Python Database
Databases are essential for storing, retrieving, and managing persistent data in applications. Python has excellent support for working with a wide variety...
Connecting Python to Databases: An Overview
Databases are essential for storing, retrieving, and managing persistent data in applications. Python has excellent support for working with a wide variety of databases, from lightweight local file-based systems to large-scale enterprise servers. Understanding the landscape of Python database options helps you choose the right tool for each project.
Python's database ecosystem divides into three main categories. Relational databases (SQL databases) store data in tables with rows and columns. You query them using SQL. SQLite, MySQL, and PostgreSQL are the most popular relational databases used with Python. Non-relational databases (NoSQL) store data in flexible formats like documents, key-value pairs, or graphs. MongoDB is the most widely used document database in this category.
Python communicates with databases through database adapters, which are libraries that implement the DB-API 2.0 standard defined in PEP 249. This standard defines a consistent interface, so code that connects to MySQL looks structurally similar to code that connects to PostgreSQL.
The general pattern for working with a relational database in Python is: 1. Import the adapter library. 2. Create a connection to the database. 3. Create a cursor from the connection. 4. Execute SQL queries with the cursor. 5. Fetch results or commit changes. 6. Close the cursor and connection.
import sqlite3
conn = sqlite3.connect("example.db") cursor = conn.cursor() cursor.execute("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)") cursor.execute("INSERT INTO users (name) VALUES (?)", ("Alice",)) conn.commit() cursor.execute("SELECT * FROM users") rows = cursor.fetchall() print(rows) conn.close()
Object-Relational Mappers, or ORMs, sit on top of raw SQL and let you work with database tables as Python classes. SQLAlchemy is the dominant ORM in the Python world. ORMs reduce the amount of SQL you write and make your code more portable across databases.
Interview tip: Be ready to explain what the DB-API 2.0 standard is and why it matters. The standard means that switching from one database to another often requires only changing the import and the connection string, not the overall code structure. Mention parameterized queries (using ? or %s placeholders) as the standard way to prevent SQL injection attacks.