postgres python psycopg2 - ghdrako/doc_snipets GitHub Wiki

psycopg2

Conect

import psycopg2

conn = psycopg2.connect(
                        database="postgres",
                        host="localhost",
                        port="5432",
                        user="postgres",
                        password=""
                        )

Create cursor

cursor = conn.cursor()

Executing Queries

cursor.execute("CREATE TABLE t1 (a int, b int)")
cursor.execute("INSERT INTO t1 values (1,2)")
cursor.execute("INSERT INTO t1 values (4,5)")
cursor.execute("INSERT INTO t1 values (6,7)")
cursor.execute("INSERT INTO t1 values (7,8)")

Using executemany() function

nums = [(1,2),(3,4),(5,6),(7,8)]
cursor.executemany("INSERT INTO t1 values (%s,%s)",nums)

Fetching Results

Fetch One

returns a single row from an executed query.

nums = [(1,2),(3,4),(5,6),(7,8)]
cursor.executemany("INSERT INTO t1 values (%s,%s)",nums)
cursor.execute("select * from t1")
print(cursor.fetchone())

The output of this will be

(1, 2)

If we were to run multiple fetchone() functions, each call would return the next row as the previous is consumed.

Fetch All

get all rows

nums = [(1,2),(3,4),(5,6),(7,8)]
cursor.executemany("INSERT INTO t1 values (%s,%s)",nums)
cursor.execute("select * from t1")
print(cursor.fetchall())

Output

[(1, 2), (3, 4), (5, 6), (7, 8)]

Fetch Many

get a specified amount of rows returned from a query

nums = [(1,2),(3,4),(5,6),(7,8)]
cursor.executemany("INSERT INTO t1 values (%s,%s)",nums)
cursor.execute("select * from t1")
print(cursor.fetchmany(size=2))

[(1, 2), (3, 4)]