Importing Data from MySQL to Pandas

The core idea is simple: you take a table that lives inside a MySQL database and pull it into your Python environment as a pandas DataFrame. Once the data is in a DataFrame, you can analyse, clean, and visualise it using all the pandas tools you have already learned.

The first step is always to establish a connection to the MySQL database. This is done using create_engine(), which returns a connection identifier (usually stored in a variable like sql_conn or engine). Once you have that connection, pandas gives you three different functions to bring the data across. Each serves a slightly different purpose.

The Three Functions for Importing Data

1. pandas.read_sql_query(query, sql_conn)

   This function takes a raw SQL query (written as a string) and executes it against the database. The result is returned directly as a DataFrame. Use this when you need to filter, join, or aggregate data on the database side before bringing it into Python. For example, you could write "SELECT * FROM students WHERE city = 'Delhi'" as your query.

2. pandas.read_sql_table(table_name, sql_conn)

   This function is simpler: you just give it the name of an existing table in the database, and it loads the entire table into a DataFrame. There is no SQL query involved — it is a direct table-to-DataFrame transfer. Use this when you want the whole table, without any filtering.

3. pandas.read_sql(sql, sql_conn)

   This is a flexible, combined function. The first argument (sql) can be either a table name (like read_sql_table) or a SQL query string (like read_sql_query). The function automatically detects which one you have provided and acts accordingly. This is often the most convenient choice when you are not sure in advance whether you will need a query or a full table.

Summary Table