A memorial note written locally will be held at Qiita. I wrote it last year, so there may be some old information. ..

About psycopg2

psycopg2 is a library that handles PostgreSQL in Python. Refs.: 1,2

Connect to PostgreSQL DB with psycopg2

You can create a DB connection with conn = psycopg2.connect ({DB_URL (str)}). {DB_URL} is specified by postgresql: // {username}: {pass} @ {IP Address or hostname}: {port} / {DB name}.

Or conn = psycopg2.connect (host = {IP Address or hostname (str)}, port = {port number (int)}, dbname = {DB name (str)}, user = {username (str)}, You can also specify it with password = {pass (str)}) .

When you no longer need a connection, you can use close (), but you can use the with syntax, so it's a good idea to use it.

import psycopg2

with psycopg2.connect({DB URL}) as conn:
    # Processes you want to do

Throw a query

Make a cursor to throw. Cursors can be created with conn.cursor (). If this is no longer needed, close () will be done, but wi (abbreviated below).

You can execute a query with cursor.execute ({query string}). If you want to give an argument, give it as a tuple (or list) to the second argument, such as cursor.execute ('SELECT * FROM OSPF_TABLE WHERE FOO =% s', (fooval,)).

You can also give arguments as a dictionary (Dictionary type). In that case, give a key to the placeholder such as % (key) s.

cur.execute("""
     INSERT INTO some_table (an_int, a_date, another_date, a_string)
     VALUES (%(int)s, %(date)s, %(date)s, %(str)s);
     """,
     {'int': 10, 'str': "O'Reilly", 'date': datetime.date(2005, 11, 18)})

import psycopg2

with psycopg2.connect({DB URL}) as conn:
    with conn.cursor() as cur:
        cur.execute({query})

Receive query results

You will receive the result of SELECT as a Python object. You can receive it with cursor.fetchone (), cursor.fetchall (), cursor.fetchmany (size).

cursor.fetchone()

Receive only one from the next line of the result (query resultset). The result will be returned as a tuple. If you run it while cursor is pointing to the end, it will return None.

cursor.fetchall()

Receive all results from the next line of the result (query resultset). The result will be returned as a list of tuples. If you run it when cursor points to the end, an empty list will be returned.

cursor.fetchmany(size)

Receives size from the next line of the result (query resultset). The result will be returned as a list of tuples. If you run it when cursor points to the end, an empty list will be returned.

commit

Transactions are enabled by default, so your changes will not take effect until you commit. Let's commit with connect.commit (). Setting connect.autocommit = True disables transactions.

Roll back

It is connect.rollback (). If you're creating a connection with with syntax, it seems to roll back automatically if an exception occurs inside the with block. It's convenient [^ 1].

[^ 1]: It seems that it satisfies PEP249