Skip to content
Tutorials Center
My learning

Topics

Lesson 66 of 75 ยท Using MySQL from Code

MySQL from Python and Node.js

Connect Python and Node.js to MySQL with mysql-connector, PyMySQL and mysql2: parameterized queries, connection pools, async drivers and an ORM overview.

Most applications reach MySQL through a driver, a library that speaks the MySQL protocol, and often through an ORM on top of it. Python and Node.js have several of each, and the choices differ in small but important ways: placeholder style, how connections are pooled, and whether calls block. This lesson compares the main options and shows the habits that keep any of them safe and fast.

A browser cannot reach a MySQL server, so driver code here is read-only. The runnable examples are Python stand-ins in the in-browser Pyodide runtime using the built-in sqlite3 module, which follows the same DB-API pattern that MySQL drivers use. They show parameterized queries and a connection pool, concepts that are identical for MySQL.

Python drivers

Package Notes
mysql-connector-python Oracle's official driver. Pure Python, with an optional C extension.
PyMySQL Pure Python, simple and widely used.
mysqlclient A C extension wrapping libmysqlclient. It is the one Django uses by default. Fast, but needs build tools or system libraries to install.
aiomysql, asyncmy Async drivers for asyncio.

All of the synchronous ones follow Python's DB-API 2.0: connect, get a cursor, execute, fetch, commit. With PyMySQL:

Python
import os
import pymysql

conn = pymysql.connect(
    host="127.0.0.1",
    user="shop_app",
    password=os.environ["DB_PASSWORD"],
    database="shop",
    charset="utf8mb4",
    cursorclass=pymysql.cursors.DictCursor,
)

try:
    with conn.cursor() as cur:
        cur.execute("SELECT id, title FROM books WHERE price <= %s", (30,))
        rows = cur.fetchall()
    with conn.cursor() as cur:
        cur.execute("INSERT INTO books (title, price) VALUES (%s, %s)", ("New", 19))
    conn.commit()
finally:
    conn.close()

Notice three habits. The placeholder is %s for every type, not Python string formatting, and values go in a separate tuple. Autocommit is off by default in DB-API drivers, so you must call commit(); forgetting it is a classic "my insert vanished" bug. And charset="utf8mb4" is set explicitly, as in MySQL from PHP with PDO.

mysql-connector-python looks almost the same (mysql.connector.connect(...), cursor(dictionary=True)). One caution: since 8.0 MySQL's default password plugin is caching_sha2_password. PyMySQL needs the cryptography package for it unless you connect over TLS, so install it or you will see an error about a missing package when you connect.

Parameterized queries

The sentence to remember is: values go in the second argument, never in the SQL string. Compare the safe and the unsafe way. SQLite uses ? instead of %s, but the rule is the same:

Python Stand-in: parameters versus string building

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The string-built query returns every user, because the attacker's quote closed the string and added an always-true condition. The parameterized query returns nothing, because the whole attack string is just a name that does not exist. A common mistake is using an f-string with %s, such as cur.execute(f"... {x}"); that is string building, not parameters.

Connection pools

Opening a MySQL connection costs a TCP handshake, authentication and often TLS, so servers that handle many requests should reuse connections from a pool. A pool keeps a fixed number of open connections, lends one for the duration of a request, and takes it back. When all are in use, further callers wait. Size matters: the total of all pools across all application processes must stay below the server's max_connections.

mysql-connector-python has a built-in MySQLConnectionPool, and SQLAlchemy provides a pool for any driver. This stand-in implements the core idea with a queue so that you can see the borrow and return cycle:

Python Stand-in: a tiny connection pool

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Five requests share two connections, so only two are ever opened. A real pool also checks that a connection is still alive before lending it (servers close idle connections after wait_timeout, 8 hours by default, and proxies often close them sooner), rolls back any open transaction when the connection is returned, and may recycle connections after a set age.

Node.js with mysql2

The standard Node driver is mysql2, which supports promises, prepared statements and pools:

JavaScript
import mysql from "mysql2/promise";

const pool = mysql.createPool({
  host: "127.0.0.1",
  user: "shop_app",
  password: process.env.DB_PASSWORD,
  database: "shop",
  charset: "utf8mb4",
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
});

const [rows] = await pool.execute(
  "SELECT id, title FROM books WHERE price <= ? AND title LIKE ?",
  [30, "%query%"],
);
console.log(rows);

Points worth knowing:

  • Placeholders are ?. pool.execute() uses a server-side prepared statement, while pool.query() escapes values on the client and sends one text query. Both are safe when you pass values separately. query() is needed for IN (?) with an array, and execute() caches prepared statements per connection.
  • Results come back as [rows, fields], so you usually destructure the first element.
  • For a transaction you need one connection, not the pool: const conn = await pool.getConnection(); await conn.beginTransaction(); ... await conn.commit(); conn.release(); with a try, catch (rollback) and finally (release). Releasing is mandatory; forgetting it leaks the connection until the pool is empty and every request hangs.
  • Large integer columns (BIGINT) and DECIMAL can lose precision as JavaScript numbers. Use the options supportBigNumbers and bigNumberStrings, or decimalNumbers as needed.
  • Close the pool with await pool.end() at shutdown so that the process can exit.

Everything in Node is non-blocking: a query returns a promise, and the event loop keeps serving other requests. In Python, a synchronous driver blocks the thread. In an asyncio application, such as FastAPI, use aiomysql or asyncmy, or run blocking calls in a thread pool, otherwise a slow query freezes every request.

ORMs and query builders

An ORM maps tables to classes and generates SQL for you. They save a lot of boilerplate, but they are layered on top of the same drivers, pools and rules.

Ecosystem Common choices
Python SQLAlchemy (very powerful, Core plus ORM), Django ORM, Peewee
Node.js Prisma, Drizzle, Sequelize, TypeORM, plus the query builder Knex

With SQLAlchemy, the driver is picked in the URL, and the pool is built in:

Python
from sqlalchemy import create_engine, text

engine = create_engine(
    "mysql+pymysql://shop_app:[email protected]/shop?charset=utf8mb4",
    pool_size=10,
    pool_pre_ping=True,
    pool_recycle=1800,
)
with engine.begin() as conn:
    rows = conn.execute(text("SELECT id, title FROM books WHERE price <= :max"), {"max": 30})

pool_pre_ping tests a connection before using it, and pool_recycle replaces connections that are too old, both of which prevent "MySQL server has gone away" errors. engine.begin() commits on success and rolls back on an exception.

A few rules apply to every ORM:

  • Look at the SQL it generates, and run it through EXPLAIN and EXPLAIN ANALYZE. A convenient property access in a loop is often an N+1 query storm: one query for the list and one more per row. Use eager loading (joinedload, include, with) to fetch related rows together.
  • Keep migrations in version control and review the DDL they generate; see ALTER TABLE and Online DDL for what is safe on a big table.
  • Configure the same things as with raw drivers: utf8mb4, timeouts, a bounded pool, and least-privilege credentials.
  • Drop to raw SQL when the ORM makes a query awkward. Window functions and recursive CTEs are good examples. See Window Functions.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which is the correct way to pass a value with PyMySQL?

    Values go in the second argument as a tuple, with %s as the placeholder. The other forms build the SQL string from input.
  2. 2. You insert a row with a DB-API driver and it is gone after the script ends. What is the likely cause?

    DB-API drivers start a transaction implicitly. Without commit() (or autocommit enabled), the change is rolled back when the connection closes.
  3. 3. Why use a connection pool?

    Connecting costs a handshake, authentication and often TLS. A pool lends existing connections and limits the total number in use.
  4. 4. In mysql2, what happens if you call pool.getConnection() and never release() it?

    A borrowed connection is unavailable to others until it is released. Leaks drain the pool until every request hangs.

Summary

  • Python offers mysql-connector-python, PyMySQL (pure Python), mysqlclient (C), and aiomysql/asyncmy for async. Node's standard driver is mysql2.
  • Always pass values separately (%s in Python MySQL drivers, ? in mysql2), never build SQL strings from input.
  • DB-API drivers need commit(); set utf8mb4 explicitly.
  • Pools reuse connections; bound their size, release every borrowed connection, and use health checks and recycling.
  • ORMs (SQLAlchemy, Django ORM, Prisma, Drizzle, Sequelize) sit on the same drivers. Inspect the SQL they emit and watch for N+1 queries.

Next: Project: Blog Database.