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).

Answer 2

TRY THIS CODE

import mysql.connector as sql

import pandas as pd

db_connection = sql.connect(host='hostname', database='db_name', 
user='username', password='password')

df = pd.read_sql('SELECT * FROM table_name', con=db_connection)