How to store mySQL query result into pandas DataFrame with pymysql?

Question

I'm trying to store a mySQL query result in a pandas DataFrame using pymysql and am running into errors building the dataframe. Found a similar question here and here, but it looks like there are pymysql-specific errors being thrown:

import pandas as pd
import datetime
import pymysql

# dummy values 
connection = pymysql.connect(user='username', password='password', databse='database_name', host='host')

start_date = datetime.datetime(2017,11,15)
end_date = datetime.datetime(2017,11,16)

try:
    with connection.cursor() as cursor:
    query = "SELECT * FROM orders WHERE date_time BETWEEN %s AND %s"

    cursor.execute(query, (start_date, end_date)) 

    df = pd.DataFrame(data=cursor.fetchall(), index = None, columns = cursor.keys())
finally:
    connection.close()

returns: AttributeError: 'Cursor' object has no attribute 'keys'

If I drop the index and columns arguments:

try:
    with connection.cursor() as cursor:
    query = "SELECT * FROM orders WHERE date_time BETWEEN %s AND %s"

    cursor.execute(query, (start_date, end_date)) 

    df = pd.DataFrame(cursor.fetchall())
finally:
    connection.close()

returns ValueError: DataFrame constructor not properly called!

Thanks in advance!

MaxU - stand with Ukraine · Accepted Answer · 2017-11-16 11:33:54Z

38

Use Pandas.read_sql() for this:

query = "SELECT * FROM orders WHERE date_time BETWEEN ? AND ?"
df = pd.read_sql(query, connection,  params=(start_date, end_date))

edited Nov 16, 2017 at 11:33

answered Nov 16, 2017 at 11:24

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

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

3 Comments

Nodame Over a year ago

pandas.read_sql() works well in general. However, what if the command is to execute a stored procedure which needs to update a table (and commit the updated rows of the table)? In that case, how to ensure the commit is done? Does pd.read_sql allow commits?

MaxU - stand with Ukraine Over a year ago

@Nodame, you can first call your stored procedure using sqlalchemy and then read results using pd.read_sql

Ludvig W Over a year ago

How does a pymysql conmection work with pandas read_sql since pymysql Connection doesnt inherit from any schlalchemy class?

Alexandre Crivellaro · Accepted Answer · 2021-07-21 19:19:31Z

2

Try This:

import pandas as pd
import pymysql

mysql_connection = pymysql.connect(host='localhost', user='root', password='', db='test', charset='utf8')
                    
sql = "SELECT * FROM `brands`"
df = pd.read_sql(sql, mysql_connection, index_col='brand_id')
print(df)

answered Jul 21, 2021 at 19:19

Alexandre Crivellaro

9139 silver badges10 bronze badges

Comments

Piotr Labunski · Accepted Answer · 2020-05-19 23:16:54Z

1

Thank you for your suggestion to use pandas.read_sql(). It works with executing a stored procedure as well! I tested it in MSSQL 2017 environment.

Below is an example (I hope it helps others):

def database_query_to_df(connection, stored_proc, start_date, end_date):
    # Define a query
    query ="SET NOCOUNT ON; EXEC " + stored_proc + " ?, ? " + "; SET NOCOUNT OFF"

    # Pass the parameters to the query, execute it, and store the results in a data frame
    df = pd.read_sql(query, connection, params=(start_date, end_date))
    return df

edited May 19, 2020 at 23:16

Piotr Labunski

1,6564 gold badges21 silver badges27 bronze badges

answered May 19, 2020 at 19:58

Negmat Mullodzhanov

211 silver badge5 bronze badges

Collectives™ on Stack Overflow

How to store mySQL query result into pandas DataFrame with pymysql?

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related