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

How to make a password-encrypted database container?

I have a database, but I need to encrypt it with a password. If anyone runs the code of selecting data from the database, the program should demand the password. If the password is correct, the ...
0
votes
0answers
17 views

How to export pandas query to PDF?

I’m using the Python client of GridDB in my CentOS computer. I have stored my database in a GridDB container but I need to generate a report for my pandas queries. The first line of the PDF report is ...
0
votes
0answers
34 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
2answers
28 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 ...
0
votes
0answers
48 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 ...
0
votes
2answers
38 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 ...
0
votes
1answer
43 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 ...
0
votes
1answer
96 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 ...
0
votes
2answers
68 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
0answers
64 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 ...
0
votes
0answers
102 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
0answers
443 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). ...
0
votes
0answers
38 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 ...
0
votes
2answers
55 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
1answer
94 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 ...
-1
votes
2answers
55 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 ...
0
votes
1answer
142 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 ...
0
votes
1answer
195 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 ...
1
vote
1answer
58 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,...
0
votes
0answers
50 views

Graphically representing SQL queries

The Problem I am trying to document a large number of SQL queries at my workplace. I'd like to programmatically parse the queries and generate graphical representations (diagrams) of the queries. For ...
0
votes
0answers
13 views

Modify owners in all tables. How to run a selected query?

I need to write a script which modifies all table owners in a Cloud SQL Postgres db. So far I have gotten this far: SELECT format( 'ALTER TABLE %I.%I.%I OWNER TO %I \gexec;', ...
0
votes
1answer
27 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 ...
0
votes
0answers
48 views

Use Python to parallelize processing of rows, or is there a native way?

As mentioned in a recent question of mine, I'm new to Oracle: Running Oracle's sqlldr on VirtualBox VM from my MacBook (client) machine I'm back, this time with a question about the best way to ...
0
votes
0answers
24 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 ...
2
votes
1answer
31 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 @...
5
votes
1answer
199 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 ...
1
vote
1answer
154 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 ...
1
vote
1answer
83 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 ...
3
votes
2answers
107 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 ...
0
votes
1answer
67 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,...
0
votes
0answers
189 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 ...
0
votes
0answers
371 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 ...
-4
votes
1answer
31 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 ...
0
votes
1answer
250 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 ...
0
votes
2answers
268 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 ...
1
vote
0answers
50 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 ...
1
vote
1answer
2k views

How to protect Sqlite3 database with password that using in desktop application

I am creating database with Qtsql.QSqlDatabase using with in python. I want to protect my Database with password. I am using this database for my desktop applications. If it is possible, How to do ...
4
votes
2answers
231 views

SQL Server hashbytes seed

I'm trying to compare hashes generated by SQL Server HASHBYTES() with hashes generated with python's hash() (or hashlib) function. For both functions, the same algorithm is used. The problem is I don'...
-1
votes
1answer
20 views

indexing impact

If I have a table with 4 columns(a,b,c,d) I create the following index for this table : CREATE INDEX index_t ON table(a,b,c); Will the PostgreSQL optimizer consider using the index in the following ...
0
votes
1answer
31 views

SQL Server 2017 MLS - cant add python package

I'm new-ish to MLS and have been trying to add new python packages like  attrs  to the python but it hasn't been working. Our set up is SQL Server 2017 with CU14 on Win 2016. Python version is 3.5.2 ...
0
votes
1answer
556 views

how do I prevent my postgres connection from timing out? (python, psycopg2)

very quick one from me: I have a python script that runs continuously, keeping an eye on a postgres database. I've set up a listening channel and everytime a relevant change is made, the script gets a ...
0
votes
0answers
17 views

How to concatenate three columns data and insert it in to another column when inserting the data into the table everytime

I have a SQlite3 table with six columns and their names are "title", "first_name", "last_name", "mobile", "designation" and "person". I want ...
0
votes
1answer
886 views

Unable to install pl/python for Postgresql12

To get the obvious out of the way, my question is posted after reading many other questions like this one https://stackoverflow.com/questions/60683776/install-pl-python-on-windows-for-postgresql-12 ...
0
votes
1answer
480 views

Adding Python package to Sql Server 2017 machine learning services breaks it

I am trying to get started running python inside of Sql Server 2017 with Python Machine Learning Services. Out of the box it works and I can run simple scripts like listing the installed packages: ...
4
votes
2answers
403 views

Python script leaves the database in restoring mode

I am attempting to use python to automate some of our database restores. When I run the SQL query the database restores and comes back up like normal, but when I run the python script that uses the ...
2
votes
1answer
429 views

How to access oracle PDB remotely?

I have create oracle 12c instance with 2 PDBs, I am able to connect to CDB from python using cx_Oracle module but not sure how to connect to PDB.
1
vote
0answers
45 views

Pyspark error inserting into mysql database table that exists

I am trying to insert into an existing mysql table using Pyspark JDBC connection however I get the following error(picture attached) Can I get assistance on this error. The table exist in the MySql ...
0
votes
0answers
27 views

Ubuntu PostgreSQL - Upgrade adding Python extension

I have an existing and populated PostgreSQL 10 instance, to which I'd like to add Python support as a Procedural language. As far as I can tell this can be done initially by installing postgresql and ...
0
votes
1answer
25 views

Need help with this sql query for Postgres DB for a Python flask application

@app.route("/home/search/details/<string:isbn_result_isbn>") def book_search_results(isbn_result_isbn): book_details = db.execute("SELECT * FROM books WHERE isbn=:g", {&...
0
votes
1answer
94 views

Change default timezone to America Pacific

SELECT @@GLOBAL.time_zone, @@SESSION.time_zone; Result: SYSTEM SYSTEM SELECT CURRENT_TIME; Result: 06:25:07 But MY System time is 11.55 AM IST and PST TIME is 11.25 PM How can I change ...

1
2 3 4 5