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.

Inserting DataFrame to MySQL database table by using to_sql() from Excel or CSV sources


Installing mysqlclient with connection string

Use the SQL dump to create student table with sample data.
from sqlalchemy import create_engine
my_conn = 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_conn

Sample code to create table

Using this DataFrame we will create a new table in our MySQL database.
my_data.to_sql(con=my_conn,name='student2',if_exists='append')
to_sql() is used to Create & insert Data to MySQL database table

if_exists

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.

index

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_conn,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'")

Handling Date & time column

If you have Date field then you can change the column to Y-m-d format to store in mysql table.
my_data['c_date']=pd.to_datetime(my_data['c_date'],format='%d.%m.%Y')
The format option given in above code as format='%d.%m.%Y' is your available input format of original DataFrame.
Read more on how to convert to datetime field by using to_datetime()

When to use ignore and coerce in to_datetime().

If our column has blank data ( specially when reading from Excel file ) and we want to continue with rest of the data then better to use errors='coerce'. By using ignore we may not able to convert to datatime format for all the rows.

Data input and output from Pandas DataFrame
Pandas DataFrame read_sql read_sql_table insert data

Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com



    Post your comments , suggestion , error , requirements etc here





    Python Video Tutorials
    Python SQLite Video Tutorials
    Python MySQL Video Tutorials
    Python Tkinter Video Tutorials
    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2021 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer