Summary of frequently used operations with psycopg2 --Qiita is becoming a popular article that gradually increases LGTM, but recently asyncpg is recommended in combination with aiohttp Server. I've come to use it a lot, so I'll summarize this as well.
However, the official documentation is usually easy to understand and the volume is not large, so it may be quick to read all of them. asyncpg — asyncpg Documentation
--Module for accessing PostgreSQL from Python --Since asynchronous processing using asyncio is performed, it will not work unless Python 3.5 or higher. --It does not comply with DB-API Specifications (The specifications specified in DB-API are synchronous APIs in the first place. for)
$ pip install asyncpg
import asyncpg
dsn = "postgresql://username:password@hostname:5432/database"
conn = await asyncpg.connect(dsn)
# ...
await conn.close()
conn is obtained as a ʻasyncpg.connection.Connection` object.
import asyncpg
dsn = "postgresql://username:password@hostname:5432/database"
async with asyncpg.create_pool(dsn) as pool:
await pool.execute("...")
pool is obtained as a ʻasyncpg.pool.Pool` object.
You can retrieve a connection from the pool and use it, but you can also execute methods such as ʻexecute` directly on the pool.
With asyncpg you can query directly with conn.execute without creating a cursor.
await conn.execute("INSERT INTO users(name) VALUES($1)", "foo")
If you write $ number in the query and specify the value after the second argument when executing the query, you can embed the value in the query. Objects of some types, such as the datetime type, can be specified as they are, but they are internally converted and embedded in the query. Which type is converted and how is described in the official documentation, and you can customize it yourself.
You can get the result by querying with a method such as fetch instead of ʻexecute`.
await conn.fetch("SELECT * FROM users") #=> [<Record id='1' name='foo'>, <Record id='2' name='bar'>]
The result is obtained as a list of ʻasyncpg.Record` objects.
It is also convenient to use fetchrow to get only the first row and fetchval to get only the value of the first row and column.
await conn.fetchrow("SELECT * FROM users WHERE id = $1", "1") #=> <Record id='1' name='foo'>
await conn.fetchval("SELECT COUNT(1) FROM users") #=> 2
Record objects behave like tuples and dicts, so you don't need to bother converting them to other data types.
record = await conn.fetchrow("SELECT * FROM users WHERE id = $1", "1")
record[1] #=>Access by index
record["name"] #=>Access with key
When using a transaction, write the process in the asynchronous context manager (async with) block.
async with connection.transaction():
await conn.execute("...")
If you use async with to start a transaction, it will be automatically committed when the block ends, so you don't have to explicitly commit.
You can set the default timeout for queries by specifying the number of seconds for command_timeout when creating a connection.
conn = await asyncpg.connect(dsn, command_timeout=60)
To set the timeout individually for each query execution, specify timeout in the argument of ʻexecute or fetch`.
await conn.execute("...", timeout=60)
The result of fetch is a list of Record objects, so you can just put it in pd.DataFrame ().
import pandas as pd
records = await conn.fetch("SELECT * FROM users")
if len(records) > 0:
df = pd.DataFrame(records, columns=list(records[0].keys()))
else:
#Processing when the result is 0
asyncpg has a lot of COPY methods, and you can easily INSERT Pandas DataFrame by using copy_records_to_table which bulk inserts a list of tuples with the COPY command.
await conn.copy_records_to_table("users", records=df.itertuples(), columns=df.columns)
Similarly, you can easily INSERT from a file such as CSV format using copy_to_table.
await conn.copy_to_table("users", source="users.csv", format="csv")
Many of the specified options flow directly to the COPY query, so if you understand the specifications of the COPY query, you can use them. (format, null, header, etc.) PostgreSQL: Documentation: COPY
Recommended Posts