how to insert values from csv into sqlite3 using python

Question

i have a python code that read from csv file using pandas and then create a file sqlite3 called rduDB.db.

            date  temperaturemin  temperaturemax
0     2007-01-13            48.0            69.1
1     2007-01-19            34.0            54.0
2     2007-01-21            28.0            35.1
3     2007-01-25            30.9            46.9
4     2007-01-27            32.0            64.0
5     2007-02-05            19.9            39.9

sqlite3 table :

CREATE TABLE IF NOT EXISTS rduWeather 
                           (id INTEGER PRIMARY KEY, 
                            Date varchar(256),
                            TemperatureMin text,
                            TemperatureMax text)'''

the system is able to:

1. read from csv and get the required values
2. create the DB file
3. create the sqlite table

not able to insert the retrieved csv data into sqlite3 table

i am looping over the retrieved data using iloc() in order to insert data into sqlite3 table.

the system display error :

builtins.TypeError: Could not operate 1 with block values unsupported operand type(s) for -: 'str' and 'int'

code:

import sqlite3

import pandas as pd
import os

class readCSVintoDB():

    def __init__(self):
        '''
        self.csvobj = csvOBJ
        self.dbobj = dbOBJ
        '''

        self.importCSVintoDB()

    def importCSVintoDB(self):

        csvfile = "C:/Users/test/Documents/R_projects/homework/rdu-weather-history.csv"
        df = pd.read_csv(csvfile,sep=';')
        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()

        dbCreateTable = '''CREATE TABLE IF NOT EXISTS rduWeather 
                           (id INTEGER PRIMARY KEY, 
                            Date varchar(256),
                            TemperatureMin text,
                            TemperatureMax text)'''

        dbCursor.execute(dbCreateTable)
        myConn.commit()

        '''
        insert data into the database
        '''
        counter =0
        for i in len(dp-1):

            print(dp.iloc[len(dp)])
        #print(len(dp))

            #dbCursor.execute('''
            #INSERT INTO  rduWeather ('Date','TemperatureMin','TemperatureMax') VALUES (?,?,?)''', i)

    #myConn.commit()

        myConn.close()        



test1 = readCSVintoDB()

Answer 1

You can simply use DataFrame.to_sql() method:

import sqlite3

conn = sqlite3.connect('c:/temp/test.sqlite')

#...

df.to_sql('rduWeather', conn, if_exists='append', index_label='id')

Demo:

In [100]: df.to_sql('rduWeather', conn, if_exists='append', index_label='id')

In [101]: pd.read_sql('select * from rduWeather', conn)
Out[101]:
   id        date  temperaturemin  temperaturemax
0   0  2007-01-13            48.0            69.1
1   1  2007-01-19            34.0            54.0
2   2  2007-01-21            28.0            35.1
3   3  2007-01-25            30.9            46.9
4   4  2007-01-27            32.0            64.0
5   5  2007-02-05            19.9            39.9

In [102]: pd.read_sql('select * from rduWeather', conn, index_col='id')
Out[102]:
          date  temperaturemin  temperaturemax
id
0   2007-01-13            48.0            69.1
1   2007-01-19            34.0            54.0
2   2007-01-21            28.0            35.1
3   2007-01-25            30.9            46.9
4   2007-01-27            32.0            64.0
5   2007-02-05            19.9            39.9