Pandas Insert data into MySQL

Question

I am trying to insert columns of data that I extracted from .csv file into MySQL using Pandas (Python).

Here is my code that I have so far.

import pandas as pd
from pandas.io import sql
from sqlalchemy import create_engine
engine = create_engine('mysql://username:password@localhost/dbname')
with engine.connect() as conn, conn.begin():

df = pd.read_csv('File.csv', usercols=['ID', 'START_DATE'], skiprows=skip)
print(df)

df.to_sql(con=con, name='Table1', if_exists='replace', flavor='mysql')

But, it does not mention about specific column names in Table1..

How do we express that?

What does the code you have in your question do? It is syntactically incorrect. — cs95
– cs95, Commented Jul 24, 2017 at 19:31
DataFrame.to_sql... examples are in the docs. pandas.pydata.org/pandas-docs/stable/io.html#io-sql — OneCricketeer
– OneCricketeer, Commented Jul 24, 2017 at 19:39
@cricket_007 I added few lines (above). What I am trying to do is insert data from csv file ('ID' and 'START_DATE' column) into Table1. — Java
– Java, Commented Jul 24, 2017 at 21:44
sql.write_frame is not correct. Why are you trying that? Looks like you are entirely missing a SQLalchemy connection object, so get that working even before touching your csv file. You also don't need to read the table to a Dataframe before writing — OneCricketeer
– OneCricketeer, Commented Jul 24, 2017 at 23:39
@cricket_007 I modified the code as above. So far, it is working fine as there is no error with code, and it prints data from csv file as expected. Now, how do I express so that data from csv file (df object) can be inserted into specific columns in MySQL table ('Table1')? — Java
– Java, Commented Jul 25, 2017 at 21:14

OneCricketeer · Accepted Answer · 2017-07-27 00:43:11Z

12

I think your code should read like this

import pandas as pd
from pandas.io import sql
from sqlalchemy import create_engine

df = pd.read_csv('File.csv', usercols=['ID', 'START_DATE'], skiprows=skip)
print(df)

engine = create_engine('mysql://username:password@localhost/dbname')
with engine.connect() as conn, conn.begin():
    df.to_sql('Table1', conn, if_exists='replace')

But, regarding your question, unless I am mistaken in my understanding of Pandas, whatever columns df presently has, those are going to be written to the columns of the same name of the mysql table.

If you need different column names, you'll want to rename those in the DataFrame

Or use the parameters, as mentioned,

index : boolean, default True
Write DataFrame index as a column.

index_label : string or sequence, default None
Column label for index column(s). If None is given (default) and index is True, then the index names are used

edited Jul 27, 2017 at 0:43

answered Jul 25, 2017 at 22:07

OneCricketeer

193k20 gold badges146 silver badges276 bronze badges

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

5 Comments

Java Over a year ago

Thanks a lot! Let me try when my MySQL database is ready. I will get back to this if there is/are any issues.

OneCricketeer Over a year ago

Wait, why are you asking the question without a running mysql database to validate against?

Java Over a year ago

It worked. Data got inserted beautifully.The database environment was not set up yesterday. Thank so much for all your help again.

bfsio Over a year ago

Couple of syntax issues. Otherwise this is a great solution! df = pd.read_csv('file.csv', usecols=['ID', 'ColumnName'], skiprows=[1])

OneCricketeer Over a year ago

@unicatcode I copied skiprows=skip and usercols from the question

Adnan · Accepted Answer · 2018-08-17 06:59:53Z

4

This is what i did in my project

 import pandas as pd
 import sqlalchemy
 engine = sqlalchemy.create_engine('mysql+pymysql://root:@localhost/pd_test')

 ratings = pd.read_csv('ratings2.csv', sep='\t', encoding='latin-1',
                  usecols=['user_id', 'movie_id', 'user_emb_id', 
 'movie_emb_id','rating'])

 ratings.to_sql('test', con=engine, if_exists='append',index=False,chunksize=1)

Hope this help!!

answered Aug 17, 2018 at 6:59

Adnan

1,70713 silver badges19 bronze badges

1 Comment

DonkeyKong Over a year ago

Install pymysql for this answer. Worked for me on Mac & venv.

Collectives™ on Stack Overflow

Pandas Insert data into MySQL

2 Answers 2

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related