load csv into mysql_Load CSV data into MySQL using Python, create table and add record

I've never had good luck using the MySQL connector directly either. Now that it's installed, try a combo of sqlalchemy and pandas. Pandas can do the table creation for you, and it will trim your code a lot.

import sqlalchemy

import pandas as pd

# MySQL database connection

engine_stmt = 'mysql+mysqldb://%s:%s@%s:3306/%s' % (username, password,

server,database)

engine = sqlalchemy.create_engine(engine_stmt)

# get your data into pandas

df = pd.read_csv("file/location/name.csv")

# adjust your dataframe as you want it to look in the database

df = df.rename(columns={0: 'yearquarter', 1: 'sms_volumes')

# using your existing function to assign start/end row by row

for index, row in df.iterrows():

dt_start, dt_end = getdatesfromquarteryear(row['yearquarter'])

df.loc[index, 'sms_start_date'] = dt_start

df.loc[index, 'sms_end_date'] = dt_end

# write the entire dataframe to database

df.to_sql(name='sms_volumes', con=engine,

if_exists='append', index=False, chunksize=1000)

print('All data inserted!')

Pandas can make it easy to get the data from your table back into a dataframe, similar to the read_csv():

# create a new dataframe from your existing table

new_df = pd.read_sql("SELECT * FROM sms_volumes", engine)