A memorial note written locally will be held at Qiita. I wrote it last year, so there may be some old information. ..
psycopg2
is a library that handles PostgreSQL in Python.
Refs.: 1,2
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
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})
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.
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.
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