CODEWITHSUNDEEP
pythonmysql
Huzefa Sadikot
Huzefa Sadikot

Reputation: 581

Automating import of pandas dataframe into MySQL

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:

Database schema

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

DB Schema after using select in sql workbench

Code runs without error

Here is the dataframe which needs to be imported

Dataframe

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

Upvotes: 0

Views: 300

Answers (1)

Carlos Almeida
Carlos Almeida

Reputation: 51

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

Upvotes: 1

Related Questions