Unexpected error: <class 'NameError'>
My code to create a table and add a record from a csv, not sure how to rectify the error, should I use the same variable as in csv?
-
Add more details, for example some codeGabrieleMartini– GabrieleMartini2019-02-11 22:35:03 +00:00Commented Feb 11, 2019 at 22:35
-
1Welcome to SO! Please post your code itself, not an image containing it. This helps us to debug issues much faster :)Obsidian Age– Obsidian Age2019-02-11 22:39:07 +00:00Commented Feb 11, 2019 at 22:39
Add a comment
|
1 Answer
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)
