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)

Filter by
Sorted by
Tagged with
-1 votes
1 answer
35 views

How do I partition my database by rowid and query the partitions properly?

Obligatory I'm a beginner at database management. To be specific about my situation, I'm working in SQLite3 in Python as a means to an end for now for a finance project. The table is called ...
KillerDiek's user avatar
0 votes
0 answers
17 views

Writing large dataset from spark dataframe

We have a azure databricks job that retrieves some large dataset with pyspark. The dataframe has about 11 billion rows. We are currently writing this out to a postgresql DB (also in azure). Currently ...
Kyle Chamberlin's user avatar
0 votes
0 answers
16 views

Problems with minutes in timestamps when inserting data into InfluxDB via PowerShell

I am working with InfluxDB and trying to insert time series data via PowerShell. My goal is to insert data points with specific timestamps containing hours, minutes and seconds into InfluxDB. I am ...
michi's user avatar
  • 101
0 votes
0 answers
24 views

How to lock database for SELECT query?

I've a python script that uses SQL Database. I've to process folder sequentially so I've created a 2 column, first one will tell if particular folder is processed or not & second one if it's being ...
Thunder's user avatar
0 votes
0 answers
24 views

Environment Variables and MS Machine Learning Services

My apologies in advance if this is a naive or obvious question, but I am relatively new to this sort of thing. Here is my basic set up and dilemma: I have an ETL process that starts with an API call ...
Charlie's user avatar
3 votes
2 answers
113 views

How do I resolve this python runtime error in Azure SQL Managed Instances?

We recently setup a new managed instance through the program that allows you to try it for free. When trying to run the following code from Microsoft's documentation: EXECUTE ...
Joe Obbish's user avatar
  • 32.1k
0 votes
0 answers
86 views

Unable to resolve pyodbc dependency error while migrating SQL Server database to MYSQL

I am using MySQL workbench 8.0.35 to Migrate Database from SQL Server to MySQL. I have installed python 3.12.0 and pyodbc 5.0.1. When i select Start Migration in the MySQL workbench i am getting ...
pradeep3009's user avatar
0 votes
0 answers
36 views

Best way to set up tables for EOD stock database

Im a newbie with postgre, and the course I did focused on querying already existing databases, not creating a database from scratch. Im aware that using csv tables might be the best way to go for ...
AndysPythonStuff's user avatar
1 vote
2 answers
81 views

optimize insert operation in MySQL (InnoDB)

I have this table: create table tab3( id int not null auto_increment, phrase text, link_1 int, link_2 ...
moe_'s user avatar
  • 113
2 votes
1 answer
58 views

Is calling a database class on app creation correct for pooling?

Start doadmin.Database().__init__() Class class Database(object): connection = None cursor = None def get_c(self): return self.cursor def __init__(self): if Database.connection is None: ...
Corie LeClair's user avatar
0 votes
2 answers
39 views

Question about multi fields index in MySQL

I'm having an doubt about coumpound indexes in my application. My db engine is Mysql 8.0 with INNODB and my question is the following: My application has a model called Report with the follwing fields ...
Pedro Silva's user avatar
-1 votes
1 answer
129 views

Azure DB - Converting Base64 Encoded into Binary Found in Databricks Python

I am going nuts on this. We have a base64 encoded value: "+G1mQl3A30MKQsCBxMXwyMOc/AZEvpcRRI13mUOy3SNDEFjyQuVQ0UKHFsdCJ/HCQqEvuEE5Lb5AAg==" We needed decoded to binary. In python (databricks)...
Davik2001's user avatar
1 vote
1 answer
101 views

storing histogram in sqlite database

I am not a database administrator, but only a scientist who would appreciate your help in solving my issue with storing histogram data in an SQLite database. I have several of them to be stored and to ...
user41796's user avatar
1 vote
0 answers
93 views

Machine Learning Services fails from Secondary Node of SQL Failover Cluster Instance

There was a planned cluster failover to the secondary node of our FCI. Afterwards, sp_execute_external_script stopped working. Examples with error messages are below: EXEC sp_execute_external_script @...
Dave Mason's user avatar
0 votes
0 answers
43 views

Why MongoDB getting higher memory usage?

This is the sample Python code that I used to write data in MongoDB database. After every connection close, I restart the program and I noticed an accumulation in memory count in the Task Manager. ...
Thiwanka Gunasinghe's user avatar
0 votes
0 answers
15 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. ...
Learner's user avatar
  • 99
0 votes
0 answers
91 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 ...
ShanN's user avatar
  • 101
0 votes
0 answers
31 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? ...
Radio Controlled's user avatar
0 votes
0 answers
295 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"]...
Zanam's user avatar
  • 101
0 votes
1 answer
67 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 ...
Nick's user avatar
  • 101
0 votes
0 answers
114 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 ...
user3040157's user avatar
1 vote
1 answer
687 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 ...
Harry Lees's user avatar
3 votes
1 answer
350 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"...
DilLip_Chowdary's user avatar
0 votes
0 answers
699 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 ...
Shashank Tiwari's user avatar
1 vote
0 answers
374 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 ...
David E's user avatar
  • 11
-1 votes
1 answer
2k 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 ...
Ojasveer's user avatar
-1 votes
2 answers
157 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=...
user3040157's user avatar
-1 votes
1 answer
86 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. ...
EranH's user avatar
  • 1
0 votes
1 answer
101 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 ...
Ayrton's user avatar
  • 1
1 vote
1 answer
4k 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 ...
john wick's user avatar
0 votes
0 answers
580 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: ...
Olamide Olanrewaju's user avatar
1 vote
1 answer
1k 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 ...
Chetan's user avatar
  • 11
0 votes
1 answer
124 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 ...
Pensw's user avatar
  • 1
1 vote
0 answers
28 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 ...
пaean's user avatar
  • 11
0 votes
1 answer
1k 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 ...
user3223372's user avatar
0 votes
1 answer
529 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 ...
DeadlyDan's user avatar
  • 101
-1 votes
1 answer
383 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 ...
Ignakal's user avatar
-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 ...
Maheshwar reddy .M's user avatar
1 vote
1 answer
1k 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 ...
R. Gosman's user avatar
0 votes
1 answer
2k 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) ...
W. Hunter Giles's user avatar
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 ...
scrollout's user avatar
  • 103
0 votes
1 answer
413 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 ...
dogfood's user avatar
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 /...
Brendan Hill's user avatar
0 votes
0 answers
89 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 ...
Steffan's user avatar
  • 101
0 votes
0 answers
211 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 ...
vishnu padmanabhan's user avatar
0 votes
2 answers
3k 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 ...
Yves's user avatar
  • 111
0 votes
0 answers
533 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 ...
user avatar
0 votes
2 answers
916 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 ...
étale-cohomology's user avatar
0 votes
1 answer
612 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 ...
Mads's user avatar
  • 143
0 votes
1 answer
792 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 ...
Luigi87's user avatar
  • 103

1
2 3 4 5 6