Questions tagged [python]
Python is a powerful and ubiquitous language. When using a Python variant or library (e.g. Pandas, SQLAlchemy, pg8000, psycopg2), please include it in the tags. Questions using this tag should restrict themselves to interactions directly with the database tier. General python scripting questions should be directed to [StackOverflow](https://stackoverflow.com/questions/tagged/python)
261
questions
0
votes
0
answers
13
views
How to handle continuous upgradable pgsql database
I have a PGSQL database with several tables(20 million records each table and 100+ columns) which need to be updated(appending rows to the existing table) by several thousands rows almost every month. ...
- 99
0
votes
0
answers
25
views
cannot INSERT data from wsl to SQL SERVER on Window host
My problem is about cannot insert new data even I granted the insert function on SMSS. Specifically, My Python app is running in WSL environment, and the SQL Server installed on Window host. The app ...
- 101
0
votes
0
answers
20
views
Postgresql: Does execute() or fetch() download result from remote?
When getting a query result from a remote server, does the execution of the query transmit the result to the local machine, or is it buffered on remote and only downloaded when fetching the result?
...
- 101
0
votes
0
answers
52
views
PostgreSQL Server shuts down automatically after python start
I am using the following code:
def start_postgresql_server():
status = subprocess.run(["pg_ctl", "-D", fr"C:/Program Files/PostgreSQL/14/data", "status"]...
- 101
0
votes
1
answer
33
views
Proper way of constructing database of historical datasets
I've been attempting to learn how to build a database of historical sports statistics using postgresql/sqlalchemy ORM the past few days and could really use some advice on approach. My use case seems ...
- 101
0
votes
0
answers
47
views
How to retrieve millions of rows from an Oracle DB to a flat file using Python
I am planning to retrieve around 47M records from a Oracle DB to a flat file using python(Here I can not able to use other languages.). But it is taking nearly 2 hours to get it completed.
Can anyone ...
1
vote
1
answer
264
views
postgres long running copy or batched inserts
I have lots of time-series data going into a postgres DB. The time series data comes into a message queue and is read by a consumer script and inserted into the database, the data is flowing ...
- 13
2
votes
1
answer
284
views
MySQL database copy
I need to create a copy of the database in my MySQL Server using a Python program.
After a little research, I found mysqldump as the better approach:
import subprocess
command_to_copy_db = f"...
- 121
0
votes
0
answers
47
views
Keep alive sessions created with Python API
I created a session using the following script:
import dolphindb as ddb
s = ddb.session()
s.connect("localhost", 8848)
The session often got disconnected when interacting with the DolphinDB ...
- 101
0
votes
0
answers
6
views
Help with Data input project
I am relatively very new to R so apologies for the redundancy.
I have a file with trade data from Can to USA for a few years. The data shows exact amounts from two points example ( Ontario-Alabama, ...
0
votes
0
answers
410
views
Load JSON data into postgres table using airflow
I have an Airflow DAG that runs a spark file (reads two parquet files, performs transformations on them, and loads the data into a single JSON file).
Now the data from this JSON file needs to be ...
1
vote
0
answers
209
views
Pandas to_sql and JSON data
I'm in the process of creating a Python application which takes in a JSON encoded file and stores the information in an SQLite database in memory.
The JSON file in itself is essentially a Database for ...
- 11
0
votes
0
answers
22
views
MySQL trouble (wrt connector, and now password)
It all started with a project I must submit as an assignment.
This requires me to use "mysql.connector" in python.
However, it gave an attribute error, saying mysql.connector doesn't have an ...
- 101
-1
votes
1
answer
940
views
mysql.connector.errors.ProgrammingError: 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides
I am working on a python project with MySQL connectivity. I have MySQL as a shell, i.e.,I need to use mysqlsh command in the Terminal to use MySQL(I am using MacOS Mojave 10.14.6). I needed to use a ...
- 1
-1
votes
2
answers
73
views
Track row affected by a plpgSQL's DML from Python code block [closed]
I just wanted to know that how could I hold the value of a DML(affected rows) using python code.
Suppose a DML is affecting 10 rows so how will I print it using python.
db = psycopg2.connect(dbname=...
-1
votes
1
answer
50
views
SQL Server long term backup policy
I wish to implement a long term backup policy in SQL Server on VM, much like Retention policies in azure sql database.
I have full backup once a week, diff once a day and log backup every 15 minutes.
...
- 1
0
votes
1
answer
71
views
Airflow to BigQueryt data load taking forever
Im currently working as a junior data engineer. My main job right now is to move data from a DB in MYSQL (which gets updated every few minutes via webhooks) and send it to BigQuery as frequently as ...
- 1
0
votes
0
answers
188
views
bcp import from CSV producing strange results
I am trying to automate the monthly import of a CSV (created by pandas/Python) into a table in SQL Server 13.0.4001. There are 100k+ rows and 20+ rows in each import, and I do not have BULK INSERT ...
1
vote
0
answers
2k
views
Get column name and column type in the same query in the correct order using psycopg2
I have a postgresql database and I am querying it using psycopg2 and I need to get the data in the correct order as in table I have found different ways to get the column names from table and column ...
- 11
0
votes
0
answers
402
views
Remove single quote from SQL query passed as a python string
Good day. I am using flask, and I'm passing a string from app.py into a SQL database. However, Sqlite3 passed in the query along with the string, resulting in a strange result.
This is the code:
...
1
vote
1
answer
664
views
Issue with idle connections/open transactions from Python to SQL Server
I am having issue with Python code leaving idle connections to SQL Server while having open transactions in them.
Background
Recently, we had some major blockings on the SQL Server database due to ...
- 11
0
votes
1
answer
97
views
Advice for applying locks in my data processing pipeline
I currently have a Python program that enters rows into a Postgres table that essentially works as a list of data I need to process. These processes create on-disk files and trigger other behavior so ...
- 1
1
vote
0
answers
24
views
Migrations spreading to the wrong database (local environment)
I am working on one Flask project and one Ruby on Rails project, both using Postgres. When I migrate in Rails, the changes are implemented both in this database and in the database for the Flask ...
- 11
0
votes
1
answer
850
views
error [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified when import excel data to sql server
I work on SQL server 2017 I need to import data from excel 2016 to sql server 2017
I using python script to do that
I create odbc and success test with name Testserver
path G:\ImportExportExcel have ...
0
votes
1
answer
356
views
IIS web application access SQL DB as service account
I've setup a new Python site on iis using FastCGI handler. The site has windows authentication enabled in iis and the app checks that the AD user belongs to an active directory group when they access ...
- 101
0
votes
1
answer
196
views
Mysql takes longer when using php or python compared to worbench queries
Versions:
MySQL Community Server: 8.0.16 in Windows server (Configuration file: unknow)
Workbench: 8.0
Problem:
When I make a query through workbench, it takes 1 second but when I make a query through ...
- 1
-2
votes
2
answers
12
views
4 Rows in to 2 two with similar column attributes
I have this table:
Pincode1
Pincode2
City
32417
0
Hyd
67812
0
Ban
0
72407
Hyd
0
77113
Ban
I want result like this table:
Pincode1
Pincode2
City
32417
72407
Hyd
67812
77113
Ban
How can I do ...
1
vote
1
answer
998
views
UPDATE incredibly slow on small PostgreSQL query - EXPLAIN ANALYZE causes it to hang
I'm trying to debug a very slow UPDATE query in one of my tables.
The table name is beepers. It has 2486 rows and weighs 1204 MB.
It has 18 columns and two indices: one on id and the other on ...
- 11
0
votes
1
answer
1k
views
I get the error "(psycopg2.OperationalError) FATAL: role "wsb" does not exist", but the user does exits
I am trying connect to my postgress database using SQLAlchemy. I was working fine yesterday (01/27/22). Now I get the following error:
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) ...
0
votes
0
answers
283
views
MongoDB Stock Data Schema design advice
I am thinking of two structures, I don't know which is better or is there are better structure? Im seeking your advise
Normal structure with code as id and time as string
{
"_id": &...
- 11
0
votes
1
answer
2k
views
Copying a PostgreSQL schema and its data from one database to another
I am trying to copy a PostgreSQL schema and it's data from one database to another without impacting the availability of the current schema (old_schema). I would also like to do this for a specific ...
- 103
0
votes
1
answer
255
views
formating dot commands in sqlite3
My first SQL database im using sqlite3 in idle on linux. Everything is working fine but I want my table to display in an easier to read format in the terminal. been reading about dot commands eg .mode ...
- 1
1
vote
1
answer
2k
views
How to call python file in repo in databricks from data factory outside DBFS?
In Azure Databricks I have I have a repo cloned which contains python files, not notebooks.
In Azure Data Factory I want to configure a step to run a Databricks Python file. However when I enter the /...
- 281
0
votes
0
answers
84
views
Is this database correctly structured?
I am in the process of converting a bus timetable to a database structure. I am building a free mobile app for a specific itinerary in my country that will:
let users know when their next bus is due ...
- 101
0
votes
0
answers
170
views
load large data and split and store into two tables in sql server
I need to take large amount of data from a table and split that into two different tables in databse (sql server). How it is possible. i need to automate this action either in sql server side or ...
0
votes
2
answers
2k
views
What is the proper way to handle multithreading insert
I'm a newbie on Database and for now I need to work with MySQL to build a web server.
In a word, I have a web framework, developed with Python. It will read/write data from/into MySQL.
For now I don't ...
- 111
0
votes
0
answers
380
views
Possible memory leak of cx_Oracle/pyodbc/Oracle Instant Client?
Let me first list all the relevant information...
Oracle database Version 19.4.0.0.0
Oracle instantclient version: 21.3.0.0.0
OS version: RHEL 7.9 x64
cx_Oracle version: 8.2
pyodbc version: 4.0.32
...
user238532
0
votes
2
answers
689
views
UPSERT unless the date is the same
There's a table
CREATE TABLE IF NOT EXISTS table00 (
userid int8 PRIMARY KEY,
save date NOT NULL,
value int4
);
where I want to upsert values IFF the date is not the same ...
- 123
0
votes
1
answer
397
views
MySQL table with a lot of text
So I have this database (Size 3.1Gb total), but this is due to one specific table I've got, containing A LOT of console output text, from some test runs. The table itself is 2.7Gb, and I was wondering ...
- 143
0
votes
1
answer
549
views
How to add a new row to a sql server table in the first position (from python) [duplicate]
I have an sql table Tab 1 in a Database. It is ordered in descent way based on the date.
This table has to be updated with new records from python.
In python I have another table Tab 2 similar to this ...
- 103
0
votes
2
answers
491
views
Error reading data from MySQL table 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
I am connected to the MySQL database in Python using the (MySQL Connector) Python module in jupyter notebook. I can perform operations for example creating a table or reading data from a table. In the ...
0
votes
0
answers
315
views
Postgresql - Optimize query/database (Odoo/Python)
I'm trying to optimize the query below in order to go under 1 second (but i hope to do better than just that). Initially it took more than 4 seconds to execute.
The query is generated by the Odoo's ...
2
votes
1
answer
2k
views
MySQLdb._exceptions.OperationalError: (2006, '') when closing a SQL query. Is it because of the connection?
Salut les administrateurs ! I have a question (and an issue) When closing my SQL query in my ETL I get a connection error while it seemed I was able to get connected:
c.execute("""
...
1
vote
0
answers
2k
views
SQL connection error in python webapp: "pandas.io.sql.DatabaseError: Execution failed on sql"
I have built a Webapp with python flask, that reads and writes to SQL tables.
Typically in the morning, I receive an error regarding SQL query execution that looks like this (see logs snippet below). ...
- 11
0
votes
0
answers
149
views
Python / AWS RDS - 300 insert statements taking 10 minutes to complete
I'm using Python 3.7 with SQLAlchemy on Ubuntu 18.04. I have two MySQL Databases - a local instance hosted on bare metal and an Amazon RDS instance. Both are accessible from MySQL Workbench on my ...
- 101
0
votes
2
answers
777
views
Get the list of all the users of all the databases using pymongo
db.command('usersInfo')
The above command gives the list of users in the database in which the command is run.
command_result = db.command(
{
'usersInfo': {'user': 'user', 'db': '...
0
votes
1
answer
926
views
Sql: Count Null and Non-Null values for set of tables and fields dynamically
I would like to create dynamic sql query where for the set of tables and fields from information schema I will count null and non-null values of these fields.
I have idea to use it something like this ...
- 11
-1
votes
2
answers
163
views
How to make transactions atomic in Mysql 5.6
How do we make the following transactions atomic?
conn - is a connection to the old primary, which is being reset as replica now.
Python code
conn.execute('unlock tables')
conn.cursor().execute('stop ...
- 49
0
votes
1
answer
1k
views
postgres read query resample time series data for hourly averages
I am new to SQL trying to learn how to do read queries on time series data. Can someone give me a tip on how to resample interval time series data to hourly averages on the postgres read query?
My ...
- 103
0
votes
1
answer
3k
views
Troubleshooting error message Invalid Object Name accessing MS SQL with python [closed]
Using python, I can connect to a MS SQL database, but cannot run any queries at all. I always get the error - invalid object name.
I understand from other stack exchanges that I may be accessing the ...
- 103