to_sql to Pass data from DataFrame to MySQL

We will use sqlalchemy and its create_engine to manage our database connection from Python to MySQL. For this we need to install mysqlclient. All details of installation are given at our MySQL installation page.

Installing mysqlclient with connection string

Use the SQL dump to create student table with sample data.
from sqlalchemy import create_engine
my_connect = create_engine("mysql+mysqldb://userid:password@localhost/my_database") #fill details
Above code we will use in our examples to manage our connection to MySQL. Note the connection variable my_connect

Collecting records

We will use read_sql to collect records from our student table. ( SQL dump of student table )
import pandas as pd
sql="SELECT * FROM student WHERE class='Four'"
my_data = pd.read_sql(sql,con=my_connect)
   id         name class  mark     sex
0   1     John Deo  Four    75  female
1   4   Krish Star  Four    60  female
2   5    John Mike  Four    60  female
3   6    Alex John  Four    55    male
4  10     Big John  Four    55  female
5  15     Tade Row  Four    88    male
6  16        Gimmy  Four    88    male
7  21   Babby John  Four    69  female
8  31  Marry Toeey  Four    88    male
Using this DataFrame we will create a new table in our MySQL database.
The new table we created is student2. Above 9 records are stored in this table.


If the table is already available then we can use if_exists to tell how to handle. It can take three values, fail( default), replace, append.

fail: (default) Generate a value error
replace: Drop the table and then insert new values
append : Add new data to the table.


You can see in above display , the first column is our DataFrame index. This also by default goes to our table. We can stop this by setting index=False

index:(optional) Boolean , if True ( default ) index will be added to the table, in case of False no index will transfer along with records.
my_data.to_sql(con=my_connect,name='student2',if_exists='append', index=False)
If the table already have index column and we set index=False then NULL data will be inserted in index column. If there is no index column and we set if_exists='append' and index=True then we will get error message.
OperationalError: (MySQLdb._exceptions.OperationalError) (1054, "Unknown column 'index' in 'field list'")

Pandas DataFrame read_sql

Post your comments , suggestion , error , requirements etc here

We use cookies to improve your browsing experience. . Learn more
HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2020 All rights reserved worldwide Privacy Policy Disclaimer