How to use `pd.read_sql` using `mysql.connector`

Question

I know we can read sql using different packages than mysql.connector but since I only know mysql.connector, I want to use it to read a table from sql and save it as dataframe in pandas. I have the following as my code.

import mysql.connector
import pandas as pd

mydb = mysql.connector.connect(
    host = 'localhost',
    user = 'root',
    passwd = '*',
    database = 'mydatabase'
)

mycursor = mydb.cursor()

query = "SELECT * FROM my table"
df = pd.read_sql(query, mycursor)

I am not sure about the last line, that is df = pd.read_sql(query, mycursor). I think I am missing some arguments here. Can you please help me with that?

Answer 1

You need to replace df = pd.read_sql(query, mycursor) with df = pd.read_sql_query(query, mydb) because you want to make a DataFrame using a query (make sure to pass mydb not mycursor).