Environment variables¶
This notebook reads its settings from environment variables. Set them before starting Jupyter:
MSSQL_HOST: hostname of your Microsoft SQL ServerMSSQL_PORT: optional, defaults to1433MSSQL_USER: SQL Server loginMSSQL_PASSWORD: password for that loginMSSQL_TRUST_SERVER_CERTIFICATE: optional, defaults tono. Set it toyesonly for a test server with a self-signed certificate.
The examples use Microsoft's ODBC Driver 18 for SQL Server, which must be installed on the machine running Jupyter.
Jupyter already runs an event loop, so the cells await their coroutines directly instead of calling asyncio.run().
Example 1¶
Read data asynchronously from an mssql database
In [ ]:
Copied!
import os
import aioodbc
async def test_example():
dsn = (
"DRIVER={ODBC Driver 18 for SQL Server};"
f"SERVER={os.environ['MSSQL_HOST']},{os.environ.get('MSSQL_PORT', '1433')};"
f"UID={os.environ['MSSQL_USER']};PWD={os.environ['MSSQL_PASSWORD']};"
f"TrustServerCertificate={os.environ.get('MSSQL_TRUST_SERVER_CERTIFICATE', 'no')}"
)
conn = await aioodbc.connect(dsn=dsn)
print("Connected to db")
cur = await conn.cursor()
print("Got cursor")
await cur.execute("SELECT 42 AS age;")
rows = await cur.fetchall()
print(rows)
print(rows[0])
print(rows[0].age)
await cur.close()
await conn.close()
await test_example()
import os
import aioodbc
async def test_example():
dsn = (
"DRIVER={ODBC Driver 18 for SQL Server};"
f"SERVER={os.environ['MSSQL_HOST']},{os.environ.get('MSSQL_PORT', '1433')};"
f"UID={os.environ['MSSQL_USER']};PWD={os.environ['MSSQL_PASSWORD']};"
f"TrustServerCertificate={os.environ.get('MSSQL_TRUST_SERVER_CERTIFICATE', 'no')}"
)
conn = await aioodbc.connect(dsn=dsn)
print("Connected to db")
cur = await conn.cursor()
print("Got cursor")
await cur.execute("SELECT 42 AS age;")
rows = await cur.fetchall()
print(rows)
print(rows[0])
print(rows[0].age)
await cur.close()
await conn.close()
await test_example()
Example 2¶
Use a connection pool: create a table, insert rows with parameters, commit, and query them
In [ ]:
Copied!
import os
import aioodbc
dsn = (
"DRIVER={ODBC Driver 18 for SQL Server};"
f"SERVER={os.environ['MSSQL_HOST']},{os.environ.get('MSSQL_PORT', '1433')};"
f"UID={os.environ['MSSQL_USER']};PWD={os.environ['MSSQL_PASSWORD']};"
f"TrustServerCertificate={os.environ.get('MSSQL_TRUST_SERVER_CERTIFICATE', 'no')}"
)
async def pool_example():
# The pool keeps between one and two open connections and hands them out on demand.
async with aioodbc.create_pool(dsn=dsn, minsize=1, maxsize=2) as pool:
async with pool.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute("DROP TABLE IF EXISTS demo_fruit")
await cur.execute(
"CREATE TABLE demo_fruit (name NVARCHAR(50) PRIMARY KEY, color NVARCHAR(20), stock INT)"
)
await cur.executemany(
"INSERT INTO demo_fruit (name, color, stock) VALUES (?, ?, ?)",
[("apple", "red", 12), ("banana", "yellow", 30), ("lime", "green", 7)],
)
# autocommit is off by default, so the table and rows are kept only after commit()
await conn.commit()
await cur.execute("SELECT name, stock FROM demo_fruit WHERE stock > ? ORDER BY name", 10)
for row in await cur.fetchall():
print(f"{row.name}: {row.stock}")
await pool_example()
import os
import aioodbc
dsn = (
"DRIVER={ODBC Driver 18 for SQL Server};"
f"SERVER={os.environ['MSSQL_HOST']},{os.environ.get('MSSQL_PORT', '1433')};"
f"UID={os.environ['MSSQL_USER']};PWD={os.environ['MSSQL_PASSWORD']};"
f"TrustServerCertificate={os.environ.get('MSSQL_TRUST_SERVER_CERTIFICATE', 'no')}"
)
async def pool_example():
# The pool keeps between one and two open connections and hands them out on demand.
async with aioodbc.create_pool(dsn=dsn, minsize=1, maxsize=2) as pool:
async with pool.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute("DROP TABLE IF EXISTS demo_fruit")
await cur.execute(
"CREATE TABLE demo_fruit (name NVARCHAR(50) PRIMARY KEY, color NVARCHAR(20), stock INT)"
)
await cur.executemany(
"INSERT INTO demo_fruit (name, color, stock) VALUES (?, ?, ?)",
[("apple", "red", 12), ("banana", "yellow", 30), ("lime", "green", 7)],
)
# autocommit is off by default, so the table and rows are kept only after commit()
await conn.commit()
await cur.execute("SELECT name, stock FROM demo_fruit WHERE stock > ? ORDER BY name", 10)
for row in await cur.fetchall():
print(f"{row.name}: {row.stock}")
await pool_example()
Example 3¶
Transactions and errors: commit, roll back, read an identity value, and handle a failing query
In [ ]:
Copied!
import os
import aioodbc
import pyodbc
dsn = (
"DRIVER={ODBC Driver 18 for SQL Server};"
f"SERVER={os.environ['MSSQL_HOST']},{os.environ.get('MSSQL_PORT', '1433')};"
f"UID={os.environ['MSSQL_USER']};PWD={os.environ['MSSQL_PASSWORD']};"
f"TrustServerCertificate={os.environ.get('MSSQL_TRUST_SERVER_CERTIFICATE', 'no')}"
)
async def transactions_and_errors():
async with aioodbc.connect(dsn=dsn) as conn:
async with conn.cursor() as cur:
await cur.execute("DROP TABLE IF EXISTS demo_events")
await cur.execute(
"CREATE TABLE demo_events (id INT IDENTITY(1, 1) PRIMARY KEY, kind NVARCHAR(20), amount INT)"
)
await conn.commit()
# Values go in as parameters (? markers), never formatted into the SQL text.
# OUTPUT INSERTED.id returns the identity value SQL Server gave the new row.
await cur.execute(
"INSERT INTO demo_events (kind, amount) OUTPUT INSERTED.id VALUES (?, ?)", "deposit", 50
)
new_id = (await cur.fetchone())[0]
await conn.commit()
print("committed row", new_id)
# rollback() throws away everything since the last commit().
await cur.execute("INSERT INTO demo_events (kind, amount) VALUES (?, ?)", "withdrawal", 20)
await conn.rollback()
await cur.execute("SELECT kind, amount FROM demo_events ORDER BY id")
print("rows after the rollback:", await cur.fetchall())
# A failing statement raises pyodbc.Error; the async with blocks still close the cursor and the connection.
try:
async with aioodbc.connect(dsn=dsn) as conn:
async with conn.cursor() as cur:
await cur.execute("SELECT * FROM demo_table_that_does_not_exist")
except pyodbc.Error as err:
print("query failed with SQLSTATE", err.args[0])
await transactions_and_errors()
import os
import aioodbc
import pyodbc
dsn = (
"DRIVER={ODBC Driver 18 for SQL Server};"
f"SERVER={os.environ['MSSQL_HOST']},{os.environ.get('MSSQL_PORT', '1433')};"
f"UID={os.environ['MSSQL_USER']};PWD={os.environ['MSSQL_PASSWORD']};"
f"TrustServerCertificate={os.environ.get('MSSQL_TRUST_SERVER_CERTIFICATE', 'no')}"
)
async def transactions_and_errors():
async with aioodbc.connect(dsn=dsn) as conn:
async with conn.cursor() as cur:
await cur.execute("DROP TABLE IF EXISTS demo_events")
await cur.execute(
"CREATE TABLE demo_events (id INT IDENTITY(1, 1) PRIMARY KEY, kind NVARCHAR(20), amount INT)"
)
await conn.commit()
# Values go in as parameters (? markers), never formatted into the SQL text.
# OUTPUT INSERTED.id returns the identity value SQL Server gave the new row.
await cur.execute(
"INSERT INTO demo_events (kind, amount) OUTPUT INSERTED.id VALUES (?, ?)", "deposit", 50
)
new_id = (await cur.fetchone())[0]
await conn.commit()
print("committed row", new_id)
# rollback() throws away everything since the last commit().
await cur.execute("INSERT INTO demo_events (kind, amount) VALUES (?, ?)", "withdrawal", 20)
await conn.rollback()
await cur.execute("SELECT kind, amount FROM demo_events ORDER BY id")
print("rows after the rollback:", await cur.fetchall())
# A failing statement raises pyodbc.Error; the async with blocks still close the cursor and the connection.
try:
async with aioodbc.connect(dsn=dsn) as conn:
async with conn.cursor() as cur:
await cur.execute("SELECT * FROM demo_table_that_does_not_exist")
except pyodbc.Error as err:
print("query failed with SQLSTATE", err.args[0])
await transactions_and_errors()