我有一个python类readCSVintoDB,它从csv文件中读取数据并将数据存储到sqlite 3数据库中。在
注:
csv文件包括许多字段,所以我只需要其中的3个。在
到目前为止,我能够读取csv文件,并使用pandas将其存储到数据帧中。但是如何将数据帧存储到数据库中。在
显示的错误:File "C:\Users\test\Documents\Python_Projects\readCSV_DB.py", line 15,
in init self.importCSVintoDB() File
"C:\Users\test\Documents\Python_Projects\readCSV_DB.py", line 60, in
importCSVintoDB INSERT INTO rduWeather VALUES (?,?,?,?)''', i)
sqlite3.IntegrityError: datatype mismatch
当我试图在for循环中打印i时,它会显示标题名称日期
readCSV_数据库:import sqlite3
import pandas as pd
import os
class readCSVintoDB():
def __init__(self):
'''
self.csvobj = csvOBJ
self.dbobj = dbOBJ
'''
self.importCSVintoDB()
def importCSVintoDB(self):
userInput= input("enter the path of the csv file: ")
csvfile = userInput
df = pd.read_csv(csvfile,sep=';')
#print("dataFrame Headers is {0}".format(df.columns))# display the Headers
dp = (df[['date','temperaturemin','temperaturemax']])
print(dp)
'''
check if DB file exist
if no create an empty db file
'''
if not(os.path.exists('./rduDB.db')):
open('./rduDB.db','w').close()
'''
connect to the DB and get a connection cursor
'''
myConn = sqlite3.connect('./rduDB.db')
dbCursor = myConn.cursor()
'''
Assuming i need to create a table of (Name,FamilyName,age,work)
'''
dbCreateTable = '''CREATE TABLE IF NOT EXISTS rduWeather
(id INTEGER PRIMARY KEY,
Date varchar(256),
TemperatureMin FLOAT,
TemperatureMax FLOAT)'''
dbCursor.execute(dbCreateTable)
myConn.commit()
'''
insert data into the database
'''
for i in dp:
print(i)
dbCursor.execute('''
INSERT INTO rduWeather VALUES (?,?,?,?)''', i)
#myInsert=dbCursor.execute('''insert into Info ('Name','FA','age','work')
#VALUES('georges','hateh',23,'None')''')
myConn.commit()
mySelect=dbCursor.execute('''SELECT * from rduWeather WHERE (id = 10)''')
print(list(mySelect))
myConn.close()
test1 = readCSVintoDB()