Python MySQL
Talk to MySQL from Python with mysql-connector-python (Oracle's official driver) or PyMySQL (pure Python). Both speak the same DB-API 2.0 interface.
Install
SHELL
pip install mysql-connector-python # or pip install pymysql
Connect & query
PYTHON
import mysql.connector
cnx = mysql.connector.connect(
host='localhost',
user='shop_app',
password=os.environ['DB_PASS'],
database='shop',
)
cur = cnx.cursor(dictionary=True)
cur.execute('SELECT id, name FROM customers WHERE active = %s', (1,))
for row in cur:
print(row)
cur.close()
cnx.close()
Always parameterise
PYTHON
# ✗ SQL injection waiting to happen
cur.execute(f"SELECT * FROM users WHERE email = '{email}'")
# ✓ Safe — driver binds the parameter
cur.execute("SELECT * FROM users WHERE email = %s", (email,))
Insert + commit
PYTHON
cur.execute(
'INSERT INTO customers (name, email) VALUES (%s, %s)',
('Ada', 'ada@example.com'),
)
cnx.commit()
print(cur.lastrowid)
Use context managers
PYTHON
with mysql.connector.connect(**cfg) as cnx:
with cnx.cursor(dictionary=True) as cur:
cur.execute('SELECT 1')
print(cur.fetchall())
For real apps — use an ORM or pool
- SQLAlchemy — connection pooling, query builder, ORM.
- Django ORM — if you're using Django.
- Tortoise / Peewee / Pony — lighter ORMs.
Tip: Never put DB credentials in source. Read them from env vars or a secrets manager. Bonus: easier to swap per environment.
Example
Example
# import mysql.connector
# cnx = mysql.connector.connect(host='localhost', user='u', password='p', database='shop')
# cur = cnx.cursor()
# cur.execute('SELECT * FROM customers')
# for row in cur: print(row)
print('Connect with mysql-connector-python or PyMySQL.')
Try it Yourself »
Exercise
Persist changes to the database.
cnx.
()
Six letters.
Discussion
Loading…