Inserting Records and DataFrames Into a SQL Database-CodePudding

import csv
#import mysql.connector
import pandas as pd
import pymysql


# Connection to the database

connection = pymysql.connect(host='localhost',
                             user='root',
                             password='password',
                             db='db')




cursor = connection.cursor()

sdata = pd.read_csv(r'/opt/data/data.csv')
sdf = pd.DataFrame(sdata, columns=['Site','t_date','t_time','V_MN'])

for index, row in sdf.iterrows():
        #print(row['V_MIN'])
        cursor.execute('INSERT INTO "Site_Voltage"(v_mn) VALUES ("%s")', row['V_MN'])


connection.commit()

I'm trying to insert the values of the column in my csv data file in to a table Site_Voltage with a record v_mn however I m getting the following error

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"Site_Voltage"(v_mn) VALUES ("3.3e0")' at line 1')

Does any one know what I m doing wrong here ?

CodePudding user response：

Try change 'INSERT INTO "Site_Voltage"(v_mn) VALUES ("%s")', row['V_MN']' to 'INSERT INTO Site_Voltage (v_mn) VALUES ("%s"), row['V_MN']'

CodePudding user response：

So the issue is the data type on my sql databse it needs to match i m using int adn the data i m writing is float or has decimal so recreating the tables could help. The syntax didnt have any problems