I want to add CSV data to MySQL using python, but i want automatically create columns in the mysql table using columns of csv
Is there any way to create column of MySQL table with CSV using python automatically more than 100 columns in CSV
1 Answers
One of the ways to create a table in MySQL (or any other database: PostgreSQL, SQLite,.. ) based on a .csv header (columns names) is by using pandas and sqlalchemy combined.
First of all, you need to install those two libraries :
pip install pandas
pip install sqlalchemy
Considering the .csv below (4 columns) :
Secondly, by running this code, you will be able to create a table named test_table with the types defined in the variable/list type_cols.
from sqlalchemy import create_engine
import pandas as pd
engine = create_engine('mysql://user:password@server/database') #Put here your credentials
df = pd.read_csv('csv_sql.csv') #Change the parameters to match your csv properties
type_cols = ['DATE', 'INTEGER', 'TEXT', 'REAL'] #Put here the columns types
name_cols = df.columns.tolist()
table_config= ', '.join([' '.join(map(str, i)) for i in zip(name_cols, type_cols)])
engine.execute(f'''CREATE TABLE IF NOT EXISTS test_table ({table_config})''')
If necessary, you can retrieve a dataframe by running this :
df = pd.read_sql("SELECT * FROM test_table", engine) #Change the query to match your needs
print(df)
col1 col2 col3 col4
Related