Automating import of pandas dataframe into MySQL

Question

I am tying to export the pandas dataframe into an Excel file and then trying to import that into a MySQL database using python.

Here is my code:

import xlrd
import mysql.connector
import pandas as pd
df = pd.read_csv('C:/Users/Administrator/Desktop/holdings.csv')
database = mysql.connector.connect(host="localhost", user="root", passwd="1234")
cursor = database.cursor()
#cursor.execute("Create Database orders")
cursor.execute("use orders")
cursor.execute = ("CREATE TABLE holdings ((tradingsymbol varchar(255), average_price int NOT NULL, 
last_price int NOT null, close_price int NOT null, exit_price int not null)")
for r in df:
     tradingsymbol = df['tradingsymbol']
     average_price = df['average_price']
     last_price = df['last_price']
     close_price = df['close_price']
     exit_price = df['exit_price']
     values = (tradingsymbol, average_price, last_price, close_price, exit_price)
cursor.execute = ("Insert into holdings (tradingsymbol, average_price, last_price, close_price, exit_price) VALUES (%s, %s,%s, %s, %s")


cursor.close()
database.commit()
database.close()
print ("Data Import Successful")

The programme executes without any error but in my database no table is created nor any data is imported as shown:

However if I run the same select statement in mysql it creates the table as shown:.

cursor.execute = ("CREATE TABLE holdings (tradingsymbol varchar(255), average_price int NOT NULL, last_price int NOT null, close_price int NOT null, exit_price int not null"))

Here is the dataframe which needs to be imported

May I know where am I going wrong in the syntax?

Carlos Almeida · Accepted Answer · 2020-11-27 10:11:40Z

1

Instead of using

cursor.execute("use orders")

Try to use

database = mysql.connector.connect(host="localhost", user="root", passwd="1234", database="orders")

Also you can use the the function "to_sql" to insert whole DataFrame into MySQL Docs

answered Nov 27, 2020 at 10:11

Carlos Almeida

514 bronze badges

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

2 Comments

Huzefa Sadikot Over a year ago

But is there something wrong with the syntax of my insert statement that i wrote?

Carlos Almeida Over a year ago

Also commit first and then close the cursor.

Collectives™ on Stack Overflow

Automating import of pandas dataframe into MySQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related