Database Administrators Stack Exchange
Database Administrators Stack Exchange

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)

python logo

You know it, you tolerate it. Python is that scripting language that every single developer is telling you to learn. They've even found this handy library for you to query the database with.

Common python libraries that are likely appropriate to ask about on dba.se are...

SQL Alchemy

https://www.sqlalchemy.org/

pg8000

https://pypi.org/project/pg8000/

Pandas

https://pandas.pydata.org/

psycopg2

https://pypi.org/project/psycopg2/

251 questions
19
votes
2 answers

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, using a ConnectionPool, or should I create an engine…
Juan Carlos Coto
  • 1,428
  • 4
  • 17
  • 24
13
votes
1 answer

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 variant plpython might become available in the…
foobar0100
  • 591
  • 5
  • 14
12
votes
1 answer

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 into python, it appears as: I've researched this…
Eric
  • 223
  • 1
  • 2
  • 9
12
votes
1 answer

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 search where the python equivalent would be something…
codykochmann
  • 223
  • 1
  • 2
  • 6
11
votes
1 answer

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: OperationalError('could not connect to server: Cannot…
Harel
  • 303
  • 3
  • 8
10
votes
3 answers

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 have the python script fire in real time, so…
Jonesome Reinstate Monica
  • 3,199
  • 7
  • 34
  • 54
10
votes
1 answer

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 PostgreSQL driver that is actively developed. It…
Celeritas
  • 793
  • 3
  • 10
  • 15
9
votes
2 answers

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 extension control file…
Stephane Rolland
  • 7,003
  • 8
  • 27
  • 39
8
votes
1 answer

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 one for getting the min. Is there a way to do this?
Hatshepsut
  • 185
  • 2
  • 6
8
votes
3 answers

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 out a way how to do that using psycopg2. So I was…
n1000
  • 221
  • 1
  • 2
  • 6
6
votes
1 answer

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, because if the code is slow or blocks, the…
Jonesome Reinstate Monica
  • 3,199
  • 7
  • 34
  • 54
6
votes
1 answer

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) VALUES (%s,%s,NULLIF(%s, 'nan'), NULLIF(%s, 'nan')) …
Dobob
  • 269
  • 1
  • 3
  • 8
5
votes
4 answers

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 certificates (PEM or DER format) in PostgreSQL…
Marek
  • 183
  • 1
  • 7
5
votes
1 answer

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

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 script. This can cause serious calculation errors for…
atevm
  • 325
  • 2
  • 6
5
votes
1 answer

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-analytics/tutorials/run-python-using-t-sql For the PoC…
Dan Gray
  • 91
  • 5
1
2 3
…
16 17