Inserting DataFrame to MySQL database table by using to_sql () from Excel or CSV sources

https://www.plus2net.com/python/pandas-mysql-insert.php https://www.plus2net.com/python/pandas-mysql-to_sql.php 0:00:01 Introduction to process of reading & inserting data to MySQL 0:00:22 Connection to MySQL database using SQLAlchemy 0:01:20 Import Pandas library and create DataFrame 0:02:37 Insert to MySQL table using to_sql() 0:04:05 Insert data from one table to another table 0:08:25 Read then update data and insert into same table ( replace ) 0:09:39 Reading from CSV or Excel file and inserting data to MySQL table WE will first connect to MySQL database by using SQLAlchemy connection engine. This connection string we will use in our further query execution. Inside the SQLAlchemy connection string you can enter your MySQL login details like userid , password , host name , database name. We will create a sample Pandas DataFrame with two columns , class and No. After creating the DataFrame we will insert the data to MySQL table by using to_sql() While inserting data we can use the option if_exists. We can set the value of if_exists to append to add data at the end of the table or we can set it to replace to delete the table and create new table with fresh data or generate error if table is already exists. We will read data from one existing table and create a DataFrame by using read_sql(), then by using to_sql() we can create one more table by using the same data. Updating table with new data WE can read data from a table and change the mark column by adding 5 marks to each record and then update the same records in table by using replace value for the if_exists option. query="SELECT * FROM student2" df=pd.read_sql(query,my_conn) print(df) df['mark']=df['mark']+5 df.to_sql(con=my_conn,name='student2',if_exists='replace',index=False) We can read Excel of CSV file as source and create a DataFrame by using read_csv() or read_excel() methods. After creating DataFrame we can insert the data to MySQL table by using to_sql(). Here is the code. student3=pd.read_excel("D:\my_data\student.xlsx") #student3=pd.read_csv("D:\my_data\student.csv") df=pd.DataFrame(data=student3) print(df) df.to_sql(con=my_conn,name='student3', if_exists='append',index=False)

الفئة