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?

Solution 1

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

Solution 2

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!!