psycopg2 memo

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

Recommended Posts

psycopg2 memo
gzip memo
Pandas memo
HackerRank memo
Python memo
python memo
graphene memo
Flask memo
pyenv memo
pytest memo
sed memo
Install Memo
BeautifulSoup4 memo
networkx memo
python memo
tomcat memo
command memo
Generator memo.
Python memo
SSH memo
Command memo
Memo: rtl8812
pandas memo
Shell memo
Python memo
Pycharm memo
Python memo
AtCoder devotion memo (11/12)
[Python] Memo dictionary
PyPI push memo
tensorflow-gpu introduction memo
LPIC201 learning memo
Jupyter Notebook memo
LPIC304 virtualization memo
python beginner memo (9.2-10)
youtube download memo
Linux x memo
Django Learning Memo
ARC # 016 Participation memo
Beautiful Soup memo
LPIC101 study memo
linux (ubuntu) memo
scp command memo
Flask Primer Memo
celery / kombu memo
who command memo
django tutorial memo
Flask basic memo
Linux # Command Memo 1
★ Memo ★ Python Iroha
Gender recognition memo
Image reading memo
[MEMO] [TERMINAL] Alacritty
3D rotation memo (1)
[Python] EDA memo
Python 3 operator memo
H2O.ai Introduction memo
lambda expression memo
[Memo] [terminal] xfce-terminal
RDKit usage memo
Jupyter Notebook memo