Environment variables¶
This notebook reads its settings from environment variables. Set them before starting Jupyter:
MYSQL_HOST: hostname of your MySQL serverMYSQL_PORT: optional, defaults to3306MYSQL_USER: MySQL user nameMYSQL_PASSWORD: password for that user
Example 1¶
Connect to a remote mysql instance, create a database and table, insert data, and execute a query
In [ ]:
Copied!
import os
import MySQLdb
conn = MySQLdb.connect(
host=os.environ["MYSQL_HOST"],
user=os.environ["MYSQL_USER"],
password=os.environ["MYSQL_PASSWORD"],
port=int(os.environ.get("MYSQL_PORT", "3306")),
)
try:
with conn.cursor() as cur:
cur.execute("SELECT VERSION()")
print("server version:", cur.fetchone()[0])
cur.execute("CREATE DATABASE IF NOT EXISTS demo_db")
cur.execute("USE demo_db")
cur.execute("DROP TABLE IF EXISTS demo_sensors")
cur.execute(
"CREATE TABLE demo_sensors (id INT AUTO_INCREMENT PRIMARY KEY, room VARCHAR(50), celsius DECIMAL(4, 1))"
)
cur.executemany(
"INSERT INTO demo_sensors (room, celsius) VALUES (%s, %s)",
[("kitchen", 21.5), ("garage", 12.0), ("attic", 27.3)],
)
# MySQLdb leaves autocommit off, so the inserts are kept only after commit()
conn.commit()
cur.execute("SELECT room, celsius FROM demo_sensors WHERE celsius > %s ORDER BY celsius DESC", (20,))
for room, celsius in cur.fetchall():
print(room, celsius)
finally:
conn.close()
import os
import MySQLdb
conn = MySQLdb.connect(
host=os.environ["MYSQL_HOST"],
user=os.environ["MYSQL_USER"],
password=os.environ["MYSQL_PASSWORD"],
port=int(os.environ.get("MYSQL_PORT", "3306")),
)
try:
with conn.cursor() as cur:
cur.execute("SELECT VERSION()")
print("server version:", cur.fetchone()[0])
cur.execute("CREATE DATABASE IF NOT EXISTS demo_db")
cur.execute("USE demo_db")
cur.execute("DROP TABLE IF EXISTS demo_sensors")
cur.execute(
"CREATE TABLE demo_sensors (id INT AUTO_INCREMENT PRIMARY KEY, room VARCHAR(50), celsius DECIMAL(4, 1))"
)
cur.executemany(
"INSERT INTO demo_sensors (room, celsius) VALUES (%s, %s)",
[("kitchen", 21.5), ("garage", 12.0), ("attic", 27.3)],
)
# MySQLdb leaves autocommit off, so the inserts are kept only after commit()
conn.commit()
cur.execute("SELECT room, celsius FROM demo_sensors WHERE celsius > %s ORDER BY celsius DESC", (20,))
for room, celsius in cur.fetchall():
print(room, celsius)
finally:
conn.close()