How to make Mysql database from CSV file

Question

I want to make the database as my all data is in the form of CSV. having 160 column and 15 rows. as the column are more so to write the name manually is very hard I want to do it like get the column names from csv file and store it in a variable I did this. My column name are now in a variable. but I'm not able to assign these value to database column.

def parseCSV(filePath):
      # CVS Column Names
      data = pd.read_csv(filePath)
      column = data.columns
      # Use Pandas to parse the CSV file
      csvData = pd.read_csv(filePath,names=column, header=None)
      # Loop through the Rows
    #   for i,row in csvData.iterrows():
    #          sql = "INSERT INTO addresses (first_name, last_name, address, street, state, zip) VALUES (%s, %s, %s, %s, %s, %s)"
    #          value = (row['first_name'],row['last_name'],row['address'],row['street'],row['state'],str(row['zip']))
    #          mycursor.execute(sql, value, if_exists='append')
    #          mydb.commit()
    #          print(i,row['first_name'],row['last_name'],row['address'],row['street'],row['state'],row['zip'])

I comment those line which is hard coded. I want this column name come from CSV not to be added manually.

IoaTzimas · Accepted Answer · 2020-11-26 09:45:49Z

2

You can directly save your dataframe as an sql table, with df.to_sql, like below:

def parseCSV(filePath):
      data = pd.read_csv(filePath)
      data.to_sql('addresses', 'yoursqlconnection')

answered Nov 26, 2020 at 9:45

IoaTzimas

10.7k2 gold badges15 silver badges32 bronze badges

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

Comments

Carlos Almeida · Accepted Answer · 2020-11-26 09:47:06Z

1

Get the first row as fieldnames and store it in an array. Example: columns = csvData.head() After that you can do this:

sql = "INSERT INTO table_name (".join(columns) + ") VALUES (the_values_from_csv)"

edited Nov 26, 2020 at 9:47

answered Nov 26, 2020 at 9:41

Carlos Almeida

514 bronze badges

Collectives™ on Stack Overflow

How to make Mysql database from CSV file

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related