使用 SQLAlchemy 连接数据库

Most production data lives in relational databases — PostgreSQL, MySQL, SQLite, or SQL Server — not CSV files. Connecting Pandas directly to a database lets you query data into a DataFrame without exporting to CSV first, push cleaned DataFrames back into tables, and combine Python's analytical power with the database's indexing and joining capabilities. The bridge between Pandas and databases is SQLAlchemy, Python's standard database abstraction library.

SQLAlchemy is a Python SQL toolkit and ORM. For Pandas integration you only need the Core layer — not the ORM. Install with pip install sqlalchemy. You also need the specific database driver: psycopg2 for PostgreSQL, pymysql for MySQL, or sqlite3 (built into Python) for SQLite. SQLAlchemy acts as an abstraction layer: the same Pandas code works with any supported database by changing only the connection string.

The first step is creating a SQLAlchemy engine using a connection URL that encodes the database type, credentials, host, port, and database name. The engine is a factory for database connections — it does not open a connection until you actually need one. Pass the engine to Pandas' pd.read_sql() and df.to_sql() functions. Never hardcode credentials; read them from environment variables or a secrets manager.

pd.read_sql_table('table_name', con=engine) reads an entire database table into a DataFrame. It automatically infers column dtypes from the database schema — integers stay as integers, timestamps as datetime, etc. This is more precise than CSV inference. You can also limit columns with the columns argument and filter rows with schema for non-default database schemas. Be cautious with very large tables: this loads everything into RAM.

pd.read_sql_query('SELECT ...', con=engine) executes an arbitrary SQL SELECT statement and returns the results as a DataFrame. This is the most flexible approach: you can filter, join, and aggregate in SQL before loading into Pandas, loading only the rows and columns you need. Write the query as a plain Python string. Never concatenate user input into queries — use parameterised queries to prevent SQL injection.

Never build SQL queries by string concatenation with user-provided values — this opens SQL injection vulnerabilities. Instead, use parameterised queries: pass parameters as a dictionary with named placeholders. SQLAlchemy handles escaping. The syntax for placeholders is :name in SQLAlchemy text queries or %(name)s for psycopg2-style queries. Always use parameterisation even for internal scripts to build good habits.

For large query results, use chunksize in pd.read_sql_query() to receive an iterator of DataFrames rather than loading everything at once. This mirrors the behaviour of pd.read_csv(chunksize=...) but fetches rows from the database in batches. Combine this with a running accumulator pattern to aggregate results from million-row queries without exhausting RAM.

Always open database connections inside a context manager (with engine.connect() as conn:) to ensure the connection is properly closed even if an exception occurs. Forgetting to close connections leads to connection pool exhaustion in production, causing new queries to hang waiting for a free slot. SQLAlchemy's connection pool manages a fixed number of connections and recycles them automatically when context managers are used.

Before writing queries, you need to know what tables and columns exist. SQLAlchemy's Inspector lets you reflect the database schema without writing raw SQL. inspector.get_table_names() lists all tables; inspector.get_columns('table') returns column names and types. This is useful when working with unfamiliar databases and is cleaner than running PRAGMA table_info() or \d tablename manually.

In long-running scripts or web applications, call engine.dispose() when you are done to close all connections in the pool. For short scripts, Python's garbage collector handles cleanup. Best practices for database connections in data pipelines: create the engine once at the top of the script and reuse it, use connection pooling defaults (pool_size=5), enable pool_pre_ping=True to automatically reconnect if the database server restarts between queries.

For data already in a database with proper indexes, pd.read_sql_query with a filtered query is often faster than exporting to CSV and reading that. The database server applies filters before sending data, reducing network transfer and parse overhead. For very wide tables, the database can also project only needed columns. However, reading from a remote database over a slow network may be slower than reading a local Parquet file — always profile both options for your specific setup.

Test your understanding of Data Analysis concepts from this lesson.

In this lesson you learned: sa.create_engine() creates a reusable connection factory from a URL string, pd.read_sql_query() executes arbitrary SQL and returns a DataFrame, and parameterised queries with sa.text() and params prevent SQL injection vulnerabilities. Next up we look at running more complex SQL queries from Pandas and combining SQL with Python logic.