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
-2 votes
0 answers
18 views

strategy for extracting data from csv files to store them in a database

I would like to have directions, I would like to store the data of thousands of csv files having different formats, how to achieve this.
user avatar
  • 1
0 votes
0 answers
36 views

Excel header columns changes when export data from sql server to Excel using pandas?

I work on sql server 2017 I run script depend on python language . I run script on sql server 2017 to export data from sql server 2017 to Excel file already exist. header of excel file before export ...
user avatar
1 vote
0 answers
14 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 ...
user avatar
  • 11
0 votes
1 answer
34 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 ...
user avatar
0 votes
0 answers
37 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 ...
user avatar
  • 101
0 votes
1 answer
24 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 ...
user avatar
-2 votes
2 answers
11 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 ...
user avatar
0 votes
0 answers
31 views

ORM vs Raw Queries

I am working on a project which I want to eventually launch to the public. In past, I have Used Django which uses ORM to use databases as python models. But I also know that they add extra overhead in ...
user avatar
1 vote
1 answer
124 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 ...
user avatar
0 votes
1 answer
166 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) ...
user avatar
0 votes
0 answers
43 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": &...
user avatar
  • 11
0 votes
0 answers
29 views

Best approach to extract 1000 Gbs of Data from Teradata

What should be the best approach to download data from Teradata using python ? I have more than 3000 GBs of data to be migrated from teradata to another code service. Any approach or advise would be ...
user avatar
0 votes
1 answer
160 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 ...
user avatar
  • 103
0 votes
1 answer
44 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 ...
user avatar
0 votes
0 answers
11 views

Download DML and DDL teradata SQL files in the local system using python

I am doing a schema migration project and what approach should I follow for downloading the DML and DDL files into the local system from teradata database using python. Unlike mssql-scripter and ...
user avatar
1 vote
1 answer
562 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 /...
user avatar
0 votes
0 answers
61 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 ...
user avatar
  • 101
0 votes
0 answers
12 views

Query trajectories that intersect two regions in pymongo

Consider a MongoDB where each document corresponds to a spatial trajectory. That is, there is a field that contains an array for each document. Each array is a sequence of latitude/longitude pairs ...
user avatar
0 votes
0 answers
4 views

Python Dataframes

I am having problem in understanding this snippet of code. import pandas as pd S1 = pd.Series([1, 2, 3, 4], index = ['a', 'b','c','d']) S2 = pd.Series([11, 22, 33, 44], index = ['a', 'bb','c','dd']) ...
user avatar
0 votes
0 answers
39 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 ...
user avatar
0 votes
2 answers
69 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 ...
user avatar
  • 111
0 votes
0 answers
160 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
79 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 ...
user avatar
0 votes
1 answer
59 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 ...
user avatar
  • 133
0 votes
1 answer
267 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 ...
user avatar
  • 103
0 votes
2 answers
189 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 ...
user avatar
0 votes
0 answers
119 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 ...
user avatar
0 votes
1 answer
473 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(""" ...
user avatar
1 vote
0 answers
1k 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). ...
user avatar
  • 11
0 votes
0 answers
69 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 ...
user avatar
0 votes
2 answers
207 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': '...
user avatar
0 votes
1 answer
343 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 ...
user avatar
  • 11
-1 votes
2 answers
113 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 ...
user avatar
0 votes
1 answer
398 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 ...
user avatar
  • 103
0 votes
1 answer
844 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 ...
user avatar
  • 103
1 vote
1 answer
210 views

Filtering Escape Characters in PostgreSQL

I am trying to filter distinct serial number from a table and join two tables. Current my code looks like SELECT DISTINCT ON (t1.serial_nbr) t1.*, q1.serl_nbr FROM ( select serial_nbr,...
user avatar
0 votes
1 answer
48 views

Check if a database table has been renamed

My app has a list of an external db's tables and it needs to be regularly updated. How do I check if a table has been renamed? i.e. how to differentiate between a new table and an old table that's ...
user avatar
0 votes
0 answers
54 views

Appropriate language to write a Script that Kills Long running query MongoDB

I want to create a script that kills queries when they reach specific time threshold, I will be using db.currentOp() method to get information about currently running queries, and use db.killOp to ...
user avatar
  • 69
2 votes
1 answer
70 views

Executing sql linked server procedures through python

I am getting an error when trying to execute SQL Server system procedures such as sp_addLinkedServer and sp_dropserver etc from python script USE [master] EXEC master.dbo.sp_dropserver @...
user avatar
  • 21
5 votes
1 answer
272 views

Why MS SQL bigint type is implicitly mapped to float64 python type, and what is the best way to handle it? [closed]

Python integer type has unlimited precision so it is more than capable to hold a bigint value of MS SQL (64 bit). Still it is implicitly mapped to float64 python type, when passed to an external ...
user avatar
  • 325
1 vote
1 answer
304 views

“REVOKE CREATE ON schema public FROM public” behave differently when executed via manually and via code in Postgres

In postgres, I created "test" database with superuser_name and then I created new user "app_user". Then I revoke app_user from create table for any public schema. And then I gave ...
user avatar
  • 111
2 votes
1 answer
174 views

Duplicate key errors in Postgres queue-like table when rows should be locked by SELECT FOR UPDATE SKIP LOCKED

I have a table called queue with items that need to be processed: CREATE TABLE public.queue ( id serial NOT NULL CONSTRAINT queue_pkey PRIMARY KEY ); Another table process ...
user avatar
3 votes
2 answers
187 views

Is using CLR for regular expressions safer than using external scripts?

Problem The main problem we need to use regular expression on MS SQL Server 2019, with the capability of at least the level on the POSIX Regular expression. Possible solutions This Q/A from ...
user avatar
  • 325
0 votes
1 answer
149 views

Variable problems in PostgreSQL with language plpython3u

I am setting up a function where recursive action is relevant. The function basically maps two floats according to a rule and then returns a string. The code below is stripped for the actual function,...
user avatar
  • 111
0 votes
0 answers
251 views

Parallel execution of a SQL Server external script

TLDR for a reader with the same problem If your external script is too slow, you may do something in your scritpt which can be implemented in TSQL, which is a big problem. If this is not the case use ...
user avatar
  • 325
1 vote
1 answer
837 views

How to enable use of SSL in MySQL server?

I have 2 devices on my local network: a remote MySQL server (using MariaDB 10.3) and a laptop (acting as a client with Python). I need to connect to a remote MySQL server via Python using SSL ...
user avatar
  • 125
-4 votes
1 answer
32 views

Doing query with website [closed]

I want to create a website and I have a data set of schools locations stored in Mongodb . There will be a search box on my website and when I search for the name of the school, I want to get the ...
user avatar
  • 1
0 votes
1 answer
428 views

MySQL optimization - more tables vs more columns/rows

Im new to SQL and looking to build a database. As an example to what I'm trying to do is have a database with about 5000 stock tickers. Each ticker will have about 35 data-points, corresponding to ...
user avatar
  • 103
0 votes
2 answers
465 views

psycopg2 connect error: cannot pass more than 100 arguments to a function?

I am trying to pass json object as a parameter to my stored procedure. I tried many things and solved couple of error atlast I am stuck with the error : Error : psycopg2 connect error: cannot pass ...
user avatar
1 vote
0 answers
51 views

Copying a dependency structure

I'm looking for performing a copy of a "dependency structure". What I mean is: there is a tree structure representing a product and its sub components. Each of this products may have one or ...
user avatar

1
2 3 4 5 6