Use this tag for questions about the psycopg3 Postgresql connector package for Python, a new implementation of the psycopg2 package.
psycopg3 is a new implementation of the psycopg2 Postgresql connector for Python.
External links
Questions tagged [psycopg3]
70 questions
1
vote
2 answers
How would I do a bulk update using Python 3 | psycopg 3.1.8?
In python I have a list of tuples
Each tuple has four fields, the first field is the row identifier that would be used in a where clause in a SQL update statement.
tuple: (id, status, department, manager_email)
update_tuple_list = [
(1, 'ACTIVE',…
user10664542
- 1,106
- 1
- 23
- 43
1
vote
1 answer
how to copy from .csv to postgres using psycopg3
I am trying (and failing) to copy data from a .csv file into a postgres table using psycopg3.
It was working perfectly using psycopg2 using the following code:
tabname= 'rd2'
fname2='new_data.csv'
new_data.to_csv(fname2, index=None)
with…
matt cooper
- 101
- 1
- 8
1
vote
3 answers
Psycopg 3 AWS Lambda layer - missing libpq/static linking
Has anyone succeeded in building a psycopg3 AWS lambda layer?
I'm using psycopg version 3.1.8 in my python code, and I get this error when testing the AWS lambda function:
{
"errorMessage": "Unable to import module 'lambda_function': no pq wrapper…
Raz T
- 11
- 2
1
vote
1 answer
Psycopg3 selecting null values
I have started migrating some Python code from using psycopg2 to pscopyg3. It appears there is a change in how null values are accepted as query parameters in the newer version. Based on a read of the documentation for this topic in psycopg3 there…
Mike
- 58,961
- 76
- 175
- 221
1
vote
1 answer
How to DRY up this psycopg connection pool boilerplate code with a reusable async function or generator?
I'm using psycopg to connect to a PostgreSQL database using a connection pool. It works great, but any function that needs to run SQL in a transaction gets three extra layers of nesting:
/app/db.py
from os import getenv
from psycopg_pool import…
Shaun Scovil
- 3,905
- 5
- 39
- 58
1
vote
1 answer
Start docker container with PostgreSQL as a fixture in conjunction with pytest-postgresql plugin
Recently I began to use pytest-postgresql plugin to test my app. I start docker container manually using docker run ... command, then I run my tests and it works like a charm.
My conftest.py looks like this:
from pytest_postgresql import…
sgtStrom
- 13
- 5
1
vote
1 answer
Why does the error "psycopg.transaction.OutOfOrderTransactionNesting" happens? and how to avoid it?
I'm running a telegram bot using python-telegram-bot==13.13 and psycopg==3.1.4 for database connections.
Yesterday this error raised suddenly:
psycopg.transaction.OutOfOrderTransactionNesting: transaction commit at the wrong nesting level.
The…
Ali_Jamous
- 11
- 3
1
vote
1 answer
Creating an async connection with psycopg3
I am trying to create an async connection using psycopg3. I was using psycopg2 without async and need to move to async database functions. The docs do not give much information.
So this is what I was using with psycopg2. It worked good.
con =…
rolandj
- 21
- 4
1
vote
2 answers
Import of CSV data into PostgreSQL using psycopg3 results in psycopg.errors.InvalidDatetimeFormat
I have a CSV file my_table.csv that looks like the following:
"dt_start","my_int_value","my_double_value","dt_version"
"2022-01-02 00:00:00",2,2.2,"2022-01-02 00:00:00"
"2022-01-03 00:00:00",3,3.3,"2022-01-03 00:00:00"
Now I simply want to import…
Cord Kaldemeyer
- 6,405
- 8
- 51
- 81
1
vote
1 answer
psycopg update with unnest for a bytea column
I'm trying to update a lot of row in my database using unnest().
Here's my db:
create table my_table
(
id serial,
feat bytea,
primary key (id)
);
create index ix_my_table_id
on my_table (id);
Then I update the table…
Yohann L.
- 1,262
- 13
- 27
1
vote
0 answers
How to deal with empty values in psycopg copy to from CSV
I'm using psycopg3 to copy rows from a csv into a SQL table.
But in the CSV are some empty values that are represented with a ' '. So when it gets copied to a int value field, I get a invalid input syntax for type integer: ""
with conn.cursor()…
Hannan Rhodes
- 79
- 8
1
vote
2 answers
How to use WHERE IN with psycopg?
I have a table like this:
id
topic_id
2
'1'
3
'1.1'
...
...
21
'4'
and I would like to get a list of ids corresponding to their topic_ids.
I can query the database directly just fine:
SELECT id FROM topics WHERE topic_id IN ('1',…
sjaustirni
- 3,056
- 7
- 32
- 50
1
vote
1 answer
Psycopg Uniqueviolation: get value that caused the error
How to get the exact value that caused the Uniqueviolation exception?
The illustrative code is:
try:
cur.execute("INSERT INTO test (num) VALUES (1), (2), (3), (3);")
conn.commit()
except psycopg.errors.UniqueViolation as err:
…
Alex Blex
- 34,704
- 7
- 48
- 75
1
vote
1 answer
Python Psycopg - copy_expert with Pandas Dataframe
I can't find a fancy way to use copy_expert, directly from a Dataframe.
This work perfectly:
Code
connection = self.engine.raw_connection()
cursor = connection.cursor()
dir_file = ''
with open(dir_archivo, 'r+') as f:
…
Brian Lauget
- 13
- 2
1
vote
1 answer
Run psycopg version 3 on Lambda
I'm using the lambda docker base image for python3.9
FROM public.ecr.aws/lambda/python:3.9
And I'm trying to use psycopg in my code. Here is a minimum reproducible example:
Dockerfile
# This works!
# FROM python:3.9
# This doesn't work
FROM…
Leslie Alldridge
- 1,427
- 1
- 10
- 26