Microsoft SQL (MSSql) via pymssql¶
Description:
- Work with data in MSSQL
Resources:
- [GitHub] https://github.com/pymssql/pymssql
- [Docs] https://www.pymssql.org/
- [Examples] https://www.pymssql.org/pymssql_examples.html
Environment variables¶
This notebook reads its settings from environment variables. Set them before starting Jupyter:
MSSQL_HOST: hostname of your Microsoft SQL ServerMSSQL_USER: SQL Server loginMSSQL_PASSWORD: password for that login
Example 1¶
Connect to a remote Microsoft SQL Database, create a table, insert data, and execute a query
In [ ]:
Copied!
import os
import pymssql
with pymssql.connect(
server=os.environ["MSSQL_HOST"],
user=os.environ["MSSQL_USER"],
password=os.environ["MSSQL_PASSWORD"],
database="tempdb",
) as conn:
# as_dict=True returns each row as a dict keyed by column name
with conn.cursor(as_dict=True) as cur:
cur.execute("DROP TABLE IF EXISTS demo_books")
cur.execute("CREATE TABLE demo_books (isbn VARCHAR(13) PRIMARY KEY, title NVARCHAR(200), year INT)")
cur.executemany(
"INSERT INTO demo_books (isbn, title, year) VALUES (%s, %s, %d)",
[
("9780000000017", "A Field Guide to Clouds", 2019),
("9780000000024", "Tides and Currents", 2021),
("9780000000031", "The Quiet Compiler", 2023),
],
)
# pymssql runs statements inside a transaction; commit() makes the new rows permanent
conn.commit()
cur.execute("SELECT title, year FROM demo_books WHERE year >= %d ORDER BY year", (2020,))
for row in cur:
print(f"{row['year']}: {row['title']}")
import os
import pymssql
with pymssql.connect(
server=os.environ["MSSQL_HOST"],
user=os.environ["MSSQL_USER"],
password=os.environ["MSSQL_PASSWORD"],
database="tempdb",
) as conn:
# as_dict=True returns each row as a dict keyed by column name
with conn.cursor(as_dict=True) as cur:
cur.execute("DROP TABLE IF EXISTS demo_books")
cur.execute("CREATE TABLE demo_books (isbn VARCHAR(13) PRIMARY KEY, title NVARCHAR(200), year INT)")
cur.executemany(
"INSERT INTO demo_books (isbn, title, year) VALUES (%s, %s, %d)",
[
("9780000000017", "A Field Guide to Clouds", 2019),
("9780000000024", "Tides and Currents", 2021),
("9780000000031", "The Quiet Compiler", 2023),
],
)
# pymssql runs statements inside a transaction; commit() makes the new rows permanent
conn.commit()
cur.execute("SELECT title, year FROM demo_books WHERE year >= %d ORDER BY year", (2020,))
for row in cur:
print(f"{row['year']}: {row['title']}")