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

user_11653374 0
Viewed 3385

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?

mysql sql python
2 Answers

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)
user_107047440
user_143183650
Related