CODEWITHSUNDEEP
pythonpython-3.xpandasmachine-learningdata-science
Osama Billah
Osama Billah

Reputation: 101

How to make Mysql database from CSV file

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.

Upvotes: 0

Views: 84

Answers (2)

Carlos Almeida
Carlos Almeida

Reputation: 51

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)"

Upvotes: 1

IoaTzimas
IoaTzimas

Reputation: 10624

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')

Upvotes: 2

Related Questions