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?

wind · Accepted Answer · 2020-10-28 03:17:15Z

6

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

edited Oct 28, 2020 at 3:17

answered Oct 28, 2020 at 3:09

wind

2,4592 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Saeed Over a year ago

I get this error: AttributeError: 'CMySQLCursor' object has no attribute 'cursor'. It does not work.

chandni goyal · Accepted Answer · 2020-10-28 03:14:55Z

4

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)

answered Oct 28, 2020 at 3:14

chandni goyal

1574 bronze badges

2 Comments

Raksha Over a year ago

i get UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy. df = pd.read_sql_query(q, mydb)

InnocentBystander Over a year ago

@Raksha This is just a warning and probably works. SQLAlchemy has problems with Python 3

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

TRY THIS CODE

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

TRY THIS CODE

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related