how to read from csv file and store data in sqlite3 using python

Question

i have a python class readCSVintoDB that read from csv file and store data into sqlite 3 database.

note : the csv file includes many fields so i just need 3 of them.

until now i am able to read csv file and stored into dataframe using pandas. but how to store the dataframe into the database.

error displayed :

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

when i tried to print i in the for loop it display the header name date

readCSV_DB :

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

I don't get it. You've clearly done some research to get you this far, what part exactly are you struggling with to go forwards? There's plenty of material about INSERT into sqlite3 and parameterised queries. Are you facing a particular issue? — roganjosh
how to import the retrieved data-frame into the sqlite3 database ? — Pyt Leb
if i had get the answer from a tutorial i would not ask the question here — Pyt Leb
But you haven't tried anything in your question. Please show what you tried and what went wrong. No tutorial is going to give an exact answer to your problem but that doesn't mean you can't try and adapt it to your problem. — roganjosh

Vinicius Barcelos Vinicius Barcelos · Accepted Answer · 2018-03-22T22:45:20

If you want to write a single row (e.g: reg = (...)) try this function:

def write_datarow(conn, cols, reg):
    ''' Create a new entry (reg) into the rduWeather table

        input:  conn (class SQLite connection)
        input:  cols (list)
                Table columns names
        input:  reg (tuple)
                Data to be written as a row
    '''

        sql = 'INSERT INTO rduWeather({}) VALUES({})'.format(', '. join(cols),'?, '*(len(cols)-1)+'?') 
        cur = conn.cursor()
        # Execute the SQL query 
        cur.execute(sql, reg)
        # Confirm  
        conn.commit()
        return

But if you had multiple rows reg = [(...),...,(...)] then use:

def write_datarow(conn, cols, reg):
    ''' Create a new entry (reg) into the rduWeather table

        input:  conn (class SQLite connection)
        input:  cols (list)
                Table columns names
        input:  reg (list of tuples)
                List of rows to be written
    '''

        sql = 'INSERT INTO rduWeather({}) VALUES({})'.format(', '. join(cols),'?, '*(len(cols)-1)+'?') 
        cur = conn.cursor()
        # Execute the SQL query 
        cur.executemany(sql, reg)
        # Confirm  
        conn.commit()
        return

how to read from csv file and store data in sqlite3 using python

error displayed :

readCSV_DB :

2 Answers