Stack Overflow
Stack Overflow

Questions tagged [psycopg2]

Psycopg is a PostgreSQL adapter for Python programming language. It implements PEP 249 with many extensions.

Psycopg is a PostgreSQL adapter for Python . It is a wrapper around the libpq library. Psycopg fully implements PEP 249. It is thread-safe. It supports asynchronous I/O, server-side cursors, COPY, large objects and two-phase commit. Psycopg is extensible with new adapters and typecasters.

Questions that relate directly to psycopg2 should receive this tag. If you are using a web framework, scientific library or ORM in conjunction with Psycopg2 be sure to include that information in your post, and consider adding tags for those libraries.

Psycopg can be easily installed on Linux or Mac, but lacks full support in Windows, though there is a port available. It requires some manual installation in a virtual environment.

Psycopg2 can be used to archive the state of objects, although it is often convenient to use an ORM (SQLAlchemy for example).

External Links

4351 questions
29
votes
3 answers

Psycopg2 Insert Into Table with Placeholders

This might be a rather silly question but what am I doing wrong here? It creates the table but the INSERT INTO doesn't work, I guess I'm doing something wrong with the placeholders? conn = psycopg2.connect("dbname=postgres user=postgres") cur =…
Timothy Dalton
  • 1,290
  • 2
  • 17
  • 24
28
votes
3 answers

How to use server side cursors with psycopg2

I have a table with 4million rows and I use psycopg2 to execture a: SELECT * FROM ..WHERE query I haven't heard before of the server side cursor and I am reading its a good practice when you expect lots of results. I find the documentation a bit…
user1919
  • 3,818
  • 17
  • 62
  • 97
28
votes
3 answers

Turn pandas dataframe into a file-like object in memory?

I am loading about 2 - 2.5 million records into a Postgres database every day. I then read this data with pd.read_sql to turn it into a dataframe and then I do some column manipulation and some minor merging. I am saving this modified data as a…
trench
  • 5,075
  • 12
  • 50
  • 80
28
votes
2 answers

Get warning messages through psycopg2

I want to call a plpgsql function through psycopg2 and see the warning messages. I.e, I have this function: create or replace function test_warning() returns void as $$ begin raise warning 'this is only a test'; end; $$ language plpgsql; and call…
Tom-db
  • 6,528
  • 3
  • 30
  • 44
27
votes
5 answers

Detect whether to fetch from psycopg2 cursor or not?

Let's say if I execute the following command. insert into hello (username) values ('me') and I ran like cursor.fetchall() I get the following error psycopg2.ProgrammingError: no results to fetch How can I detect whether to call fetchall() or not…
moeseth
  • 1,855
  • 5
  • 23
  • 47
27
votes
1 answer

How to save results of postgresql to csv/excel file using psycopg2?

I use driving_distance in postgresql to find distances between all nodes, and here's my python script in pyscripter, import sys #set up psycopg2 environment import psycopg2 #driving_distance module query = """ select * from…
Heinz
  • 2,415
  • 6
  • 26
  • 34
27
votes
3 answers

Get psycopg2 count(*) number of results

Whats the correct way to get the number or rows returned by this query? I'm specifically looking to see if no results are returned. sql = 'SELECT count(*) from table WHERE guid = %s;' data=[guid] cur.execute(sql,data) results = cur.fetchone() for r…
Matt
  • 7,022
  • 16
  • 53
  • 66
26
votes
4 answers

Postgres/psycopg2 - Inserting array of strings

I'm using Postgres 9 and Python 2.7.2 along with psycopg2 and am trying to insert an array of string values with properly escaped quotation marks. Sample: metadata = {"Name": "Guest", "Details": "['One', 'Two', 'Three']"} cur.execute("insert into…
Growth Mindset
  • 1,135
  • 1
  • 12
  • 28
26
votes
3 answers

Getting OperationalError: FATAL: sorry, too many clients already using psycopg2

I am getting the error OperationalError: FATAL: sorry, too many clients already when using psycopg2. I am calling the close method on my connection instance after I am done with it. I am not sure what could be causing this, it is my first experience…
Greg
  • 7,233
  • 12
  • 42
  • 53
26
votes
4 answers

Installing psycopg2 (postgresql) in virtualenv on windows

I installed psycopg2 in virtualenv using easy_install psycopg2. I did not see any errors and looks like installation went fine.. there is an egg file created in the site-packages dir for psycopg2.. but when I run import psycopg2 in the interpreter,…
StackUnderflow
  • 24,080
  • 14
  • 54
  • 77
26
votes
2 answers

How do I use Psycopg2's LoggingConnection?

I'd like to log the queries that psycopg2 is making, but the psycopg2 documentation doesn't really specify how LoggingConnection should be used. import logging from psycopg2.extras import LoggingConnection db_settings = { "user": "abcd", …
k107
  • 15,882
  • 11
  • 61
  • 59
26
votes
2 answers

Psycopg2 uses up memory on large select query

I am using psycopg2 to query a Postgresql database and trying to process all rows from a table with about 380M rows. There are only 3 columns (id1, id2, count) all of type integer. However, when I run the straightforward select query below, the…
Carl
  • 1,027
  • 1
  • 9
  • 21
26
votes
5 answers

xcrun/lipo freezes with OS X Mavericks and XCode 4.x

Been trying to install psycopg2 with either easy_install or pip, and the terminal gets stuck in a loop between xcrun and lipo. sidwyn$ sudo easy_install psycopg2 Searching for psycopg2 Reading https://pypi.python.org/simple/psycopg2/ Reading…
Sidwyn Koh
  • 1,742
  • 2
  • 21
  • 29
25
votes
3 answers

Postgres closes connection during query after a few hundred seconds when using Psycopg2

I'm running PostgreSQL 9.6 (in Docker, using the postgres:9.6.13 image) and psycopg2 2.8.2. My PostgreSQL server (local) hosts two databases. My goal is to create materialized views in one of the databases that use data from the other database using…
haroba
  • 2,120
  • 4
  • 22
  • 37
25
votes
4 answers

How Postgresql COPY TO STDIN With CSV do on conflic do update?

I want to do " on conflict (time) do update set name , description " but I have no idea when I use stdin with csv , I don't know what name equal what? and description equal what... table_a: xxx.csv: with open('xxx/xxx.csv', 'r',…
Frank Liao
  • 855
  • 1
  • 8
  • 25
1 2 3
…
99 100