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)
222
questions
18
votes
2answers
15k views
How to best use connection pooling in SQLAlchemy for PgBouncer transaction-level pooling?
Using SQLAlchemy to query a PostgreSQL database behind PgBouncer, using transaction-level pooling.
What is the best pattern to use for this kind of set up? Should I have one-engine-per-process, ...
12
votes
1answer
11k views
Encoding issue with SQL Server VARCHAR column retrieved in Python
We recently had an issue with encoding related to a field that's being stored as a varchar(120) in SQL Server. In SSMS, the varchar appears as:
"Who Killed JonBen‚t?"
However, when it's brought ...
12
votes
1answer
2k views
Why is PL/Python untrusted?
According to the docs:
PL/Python is only available as an "untrusted" language, meaning it does not offer any way of restricting what users can do in it and is therefore named plpythonu. A trusted ...
11
votes
1answer
8k views
pgBouncer works great but occasionally becomes unavailable
I'm running pgBouncer in front of a busy postgres 9 database.
For most of the time it works fine. But every few hours I'll get an error email from my application with an exception from psycopg2:
...
10
votes
3answers
15k views
Launching a python script from an insert trigger
We have a nice piece of python that sends some emails and interacts with a cloud system. Works fine. But we have to fire it every few minutes to poll the db. We really need, for business purposes, to ...
asked Dec 13 '13 at 19:32
Jonesome Reinstate Monica
3,0106 gold badges30 silver badges53 bronze badges
9
votes
1answer
4k views
What exactly is Psycopg2?
This question is for a report I'm writing for school. Technically speaking, what is Psycopg2? In this tutorial they refer to it both as an "adapter" and "driver"
Psycopg2 is a DB API 2.0 compliant ...
8
votes
3answers
42k views
How to work with multiple Postgres databases in psycopg2
I have two different Postgres databases on two different servers (one is actually local). Similar to this question, I would like to work with both databases at the same time. However, I can't figure ...
8
votes
1answer
26k views
Looking for simple contains method when searching text in postgresql
Im new to the PostgreSQL world so I do apologize in advance for not knowing if there is an obvious answer to these questions.
Basically I'm looking for two things. First a simple "contains" based ...
8
votes
1answer
8k views
How to get minimum value in pyMongo
I have a collection with several fields, and I'd like to get the minimum value from one. I know a command for getting the max value:
collection.find_one(sort=[("myfield", -1)])["myfield"]
but not ...
6
votes
1answer
12k views
PostgreSQL/Psycopg2 upsert syntax to update columns
I want to have Psycopg2 update col1, col2 and col3 when there is a conflict of id.
In my Python code I have the insert SQL currently as:
insert_sql = '''INSERT INTO {t} (id,col1,col2,col3)
...
6
votes
1answer
715 views
SQL Server: How to get the responsiveness of a trigger, without blocking the transaction that fires the trigger?
On a thread today I posed a desire to get code to execute immediately when a record is inserted rather than resorting to polling.
Triggers are powerful tools, yet dangerous places to put real code, ...
asked Dec 13 '13 at 22:40
Jonesome Reinstate Monica
3,0106 gold badges30 silver badges53 bronze badges
5
votes
4answers
6k views
What is the best way to store X509 certificate in PostgreSQL database?
I'm working on web authenticate system where users will digitally sign random tokens and this will be checked with X509 certificates stored on the server.
Therefore I have to store several X509 ...
5
votes
1answer
4k views
Couldn't open extension control file plpython3u.control : No such file or directory
I want to have python3 in my postgresql database for writing stored procedures.
Being in the psql client, when I enter the command create extension plpython3u I get the error:
couldn't open ...
5
votes
1answer
35 views
Lifespan of Launchpad-Launched Exterrnal Runtime Processes
When SQL Server uses SQL Launchpad to launch an external runtime (e.g. R or Python) when sp_execute_external_script is used, what's the lifespan of the external runtime process?
Does each ...
4
votes
2answers
93 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'...
4
votes
2answers
4k views
AttributeError: 'NoneType' object has no attribute 'value' - pgAdmin4
I Ctrl+C'd out of the initial pgAdmin4 setup aka. python pgAdmin4.py and now if I try to rerun the install, I get a mountain of errors. I'm using pgAdmin3 for now but I would like to know how to fix ...
4
votes
3answers
35k views
How to uninstall pgAdmin4 on Ubuntu 16.04?
I successfully installed pgAdmin4 with
pip install pgadmin4-2.0-py2.py3-none-any.whl
But afterward, pip list doesn't show anything related to pgAdmin, so I don't know what argument to pass to pip ...
4
votes
2answers
3k views
How do long string field names affect MongoDB database size?
I am using MongoDB and the names of my fields are strings with 10-20 characters. A typical document consists of 30.000 columns filled mostly with floats, like 1.2, 10.5, 2.55. It's size is 1MB.
Do ...
4
votes
1answer
122 views
Does SSMS v17 allow user input in Python scripts?
With the introduction of Python feature shipped with SQL Server 2017, I would like to know if anyone has been able to use SSMS v17.x and run a python script that requires a user input. Here is a ...
4
votes
2answers
135 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 ...
4
votes
1answer
1k views
SQL Server 2017 & External Python Modules
I'm doing some PoC work with Python integration in SQL Server 2017.
I have got the basic steps working, & have completed this example successfully:
https://docs.microsoft.com/en-us/sql/advanced-...
4
votes
1answer
788 views
Flask mongodb assertion 13 not authorized for query on flask_session.sessions
Need help resetting mongodb back to its original state.
We have a flask app running with mongodb with authentication turned off. Running on Ubuntu with std Flask. We tried ALL sorts of combinations ...
3
votes
1answer
3k views
PgBouncer Uncertainties
I'm trying PgBouncer for the first time with a simple Python web application (Flask + psycopg2), and I have a hard time interpreting the meaning of its log messages by simple googling. I'm basically ...
3
votes
2answers
70 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 ...
3
votes
2answers
3k views
Using AWS DynamoDB Geospatial index with Python
Amazon's AWS DynamoDB features Geospatial Indexing, which facilitates geo queries:
Query Support: Box queries return items that fall within a pair of geo
points that define a rectangle as ...
3
votes
1answer
735 views
When I run mssql-cli, I get “No module named mssqlcli”
I just installed mssql-cli with pip,
pip3 install mssql-cli
Then I try to run mssql-cli and I get
$ mssql-cli
/usr/bin/python: No module named mssqlcli
3
votes
1answer
108 views
Should I do the maths within PostgreSQL for ACIDity?
It is my first question on Database Administration Stack Exchange. Please be kind if something is not done the nice way..
I would like to be advised concerning the way I'm working on my database, in ...
3
votes
1answer
393 views
Have several 128 elements lists, need to obtain distance between them
Is storing them in a CUBE type field and then getting euclidean distance, (using <->) the only way?
I can not save them as a list of floats, since i get: "can't adapt type numpyndarray"
Can ...
3
votes
2answers
191 views
Python MySQL prepared statements slower than regular queries
I'm trying to use prepared statements with python to improve query performance, but my prepared statements are slower (by a factor of ~7.5 no matter how many iterations I set in my benchmark script).
...
3
votes
1answer
157 views
Removing sp_execute_external_script Start-Up Lag
On a freshly-restarted instance of SQL Server, the first time Python is executed using sp_execute_external_script, there's a lag before results are returned. After the initial Python script execution, ...
3
votes
0answers
131 views
TCP Resets when using psycopg2
I'm trying to do a simple health check for keepalived and other services via a python script and psycopg2 library. All seems to be alright, until I close the connection, at which point a packet with ...
3
votes
1answer
953 views
select two tables and print the columns side by side with matching key in hive
I have the below tables:
table_1
name_1 id_1 value_1
avi 1 21
adi 2 X
table_2
name_2 id_2 value_2
avi 1 21
adi 2 22
metadata_info: can be read from ...
3
votes
1answer
4k views
DB2 Query Error SQL0204N Even With The Schema Defined [closed]
I'm using Python to access tables in DB2 10.1.0
I have a login account named foobar and a schema with the same name. I have a table named users under the schema.
When I'm logged in as foobar, I can ...
2
votes
3answers
2k views
SQL Server backup. PowerShell vs Python [closed]
I'm about to refactor inherited scripts deployed on multiple servers that backup many other servers. They are a mixture of cmd, vbs and sql. I'm pondering what benefits would bring doing it with ...
2
votes
3answers
5k views
Connection pooling with python mysql-connector
I would like to use connection pooling with mysql-connector 2.1.5
On Ubuntu everything works fine, but I would like to switch to Alpine and have used this file to install the connector (as described ...
2
votes
2answers
1k views
Idle connection plus schema-modifying query causing locked database
As part of our automated deployment process for a web app running on a LAMP stack, we drop all our triggers and stored procedures and recreate them from source control. It turns out there was a hidden ...
2
votes
2answers
84 views
Allow only one reader to a table
I have a simple Postgres table which port numbers to listen on. If one port is used it can't be used again.
However due to multiple worker/threads they many times read simultaneously. I want that ...
2
votes
1answer
7k views
psycopg2 connection already closed
In one of our environments we execute quite heavy query from python script using psycopg2. It takes more then 10 min to execute but we get a result and can consume received data.
When we move the ...
2
votes
1answer
693 views
Fetch Only new data from auto-updating online sql database
I have an online MySQL (presto) database which keeps on auto-updating (i.e., new rows keep getting added to the tables(views, to be exact) every day). I want to fetch the data from it every day but ...
2
votes
1answer
12k views
How to set a auto-increment value in SQLAlchemy?
This is my code :
class csvimportt(Base):
__tablename__ = 'csvimportt'
#id = Column(INTEGER, primary_key=True, autoincrement=True)
aid = Column(INTEGER(unsigned=True, zerofill=...
2
votes
1answer
2k views
Unexpected behavior of foreign keys
I want to use Sqlite3 with Python and created queries as follows:
import sqlite3
from sqlite3 import Error
db = sqlite3.connect("temp.sqlite")
cursor = db.cursor()
cursor.execute("...
2
votes
1answer
4k views
Query against JSON array in PostgreSQL
The data I'm working with is in a column ('transaction') of JSONB type, containing documents similar to this {"transactions":[{"id":"ABC123", ...}, {"id":"DEF456", ...}, {"id": "GHI789", ...}]}
I'm ...
2
votes
1answer
1k views
presentation of non-ascii characters (ñ)
I'm facing the following problem: I have a MySQL database which contains non-ascii characters in some records. Characters like "ñ", for example.
What kind of setup do I need to perform at the ...
2
votes
1answer
855 views
for Insert multiple rows in one single insert MySQL
I have done bulk-insertion in MySQL using multiple row insertions method by using python script. My testing csv file consists of 100,000 lines only (real one consists of millions lines).
so I perform ...
2
votes
0answers
621 views
Redis along with Mysql
I'm currently working on a mobile app alike Google Maps or Wave, but also including parking. The application its running with MySQL primarily. With at this stage most of our data is loaded by python ...
2
votes
0answers
3k views
psycopg2 - drawbacks of reusing cursors?
I am new to Python and the psycopg2 module.
context
Working on a script that select data from MySQL, perform some operations (conversion of data-types and other "transformations"), and finally ...
2
votes
1answer
5k views
SQL Server: how to troubleshoot login timeout expired error [closed]
I have a production SQL Server 2012 (SP1) running on Windows Server 2008 R2 Enterprise edition.
A lot of time while connecting to this server from a remote host I to face connection timed out error. ...
2
votes
0answers
734 views
MongoDB communication issues on Azure
I have a Python uWSGI app running behind Nginx using pymongo to communicate with a 4 machine replica set running Mongodb 2.6.3.
The replica set is configured using domain names for each machine mdb-0[...
2
votes
2answers
1k views
Query to Access a specfic data in the row using python
Am using wordpress and phpmyadmin for handling my database
Below is the code I use to view entries on my form using python. In the form I ask users to check/uncheck specific questions. It works as ...
1
vote
2answers
2k views
What is better? Delete and insert or update? [closed]
I have a python script in which I have to update a column in 67000 rows. The value of the desired column can be unique for each row. I found that inserting 67000 rows takes about 5 seconds but ...
