Importing csv data into sql lite cant figure it out.

[jimmyvegas29]

So im trying to build a gui that display some unique timesheet data, i wont bore you with the end goal and why. One of the first things i need to do is be able to import the csv files that come out of the time management system into a seperate db. So after watching a ton of videos on python and sqlite i was thought i was ready to tackle this. I can get data to populate into the database, but whats happening is each list[] is going in as one big entry into each column, its not iterating through each item in the list. (see example picture)



Here is a picture of the csv data



One of the things i need to do that is making it more complicated probably, is i do not need all the data in the csv, only certain columns, you can see in the code thats what im trying to call out.


Here is the code.

import csv
import sqlite3

jnumber = []
jdescription = []
sitenumber = []
travel = []
empcode = []
firstname = []
lastname = []
name = []
cindate = []
hours = []
city = []
state = []
pm = []

with open('C:\Python 3.5\Stuff\hours.csv') as csvfile:
    readCSV = csv.reader(csvfile, delimiter=',')

    for row in readCSV:

        jnumbers = row[3]
        jdescriptions = row[4]
        sitenumbers = row[5]
        travels = row[6]
        empcodes = row[7]
        firstnames = row[8]
        lastnames = row[9]
        names = row[10]
        cindates = row[11]
        hourss = row[12]
        citys = row[13]
        states = row[14]
        pms = row[16]

        name.append(names)
        jnumber.append(jnumbers)
        sitenumber.append(sitenumbers)
        travel.append(travels)
        empcode.append(empcodes)
        firstname.append(firstnames)
        lastname.append(lastnames)
        cindate.append(cindates)
        hours.append(hourss)
        city.append(citys)
        state.append(states)
        pm.append(pms)

conn = sqlite3.connect('testdb.db')
c = conn.cursor()

def create_table():
    c.execute('CREATE TABLE IF NOT EXISTS testtable(jobnumber STRING, jdescription TEXT, sitenumber STRING, chargetype TEXT, employeecode INTEGER, firstname TEXT, lastname TEXT, fullname TEXT, clockindate DATE, hours DECIMAL(2), city TEXT, state TEXT, projectmanager TEXT)')
    conn.commit()

def insert_data():
    
    jnu = str(jnumber)
    jde = str(jdescription)
    sit = str(sitenumber)
    tra = str(travel)
    emp = str(empcode)
    fir = str(firstname)
    las = str(lastname)
    nam = str(name)
    cin = str(cindate)
    hou = str(hours)
    cit = str(city)
    sta = str(state)
    pjm = str(pm)



    c.execute("INSERT INTO testtable (jobnumber, jdescription, sitenumber, chargetype, employeecode, firstname, lastname, fullname, clockindate, hours, city, state, projectmanager) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
              (jnu, jde, sit, tra, emp, fir, las, nam, cin, hou, cit, sta, pjm))


    conn.commit()

for rows in range(1):
    insert_data()

c.close()
conn.close()

Any help is much appreciated, I am new to Python/programming in general, but im using this project to learn. Its just seems to be very hard to search google for the right answers.

[woooee]

A re-arranging of your code which should make it to flow correctly. Note that there is no reason to create a list as you already have it in 2 places, 1) result of the read and 2) in the data in the SQLite database. Obviously this code has not been tested.

import csv
import sqlite3
 
"""
jnumber = []
jdescription = []
sitenumber = []
travel = []
empcode = []
firstname = []
lastname = []
name = []
cindate = []
hours = []
city = []
state = []
pm = []
""" 

conn = sqlite3.connect('testdb.db')
##def create_table():
c.execute('CREATE TABLE IF NOT EXISTS testtable(jobnumber STRING, jdescription TEXT, sitenumber STRING, chargetype TEXT, employeecode INTEGER, firstname TEXT, lastname TEXT, fullname TEXT, clockindate DATE, hours DECIMAL(2), city TEXT, state TEXT, projectmanager TEXT)')
c = conn.cursor()
 

with open('C:\Python 3.5\Stuff\hours.csv') as csvfile:
    readCSV = csv.reader(csvfile, delimiter=',')
 
    for row in readCSV:
 
        jnumbers = row[3]
        jdescriptions = row[4]
        sitenumbers = row[5]
        travels = row[6]
        empcodes = row[7]
        firstnames = row[8]
        lastnames = row[9]
        names = row[10]
        cindates = row[11]
        hourss = row[12]
        citys = row[13]
        states = row[14]
        pms = row[16]
 
##def insert_data():
     
        jnu = str(jnumber)
        jde = str(jdescription)
        sit = str(sitenumber)
        tra = str(travel)
        emp = str(empcode)
        fir = str(firstname)
        las = str(lastname)
        nam = str(name)
        cin = str(cindate)
        hou = str(hours)
        cit = str(city)
        sta = str(state)
        pjm = str(pm)
 
        """
        name.append(names)
        jnumber.append(jnumbers)
        sitenumber.append(sitenumbers)
        travel.append(travels)
        empcode.append(empcodes)
        firstname.append(firstnames)
        lastname.append(lastnames)
        cindate.append(cindates)
        hours.append(hourss)
        city.append(citys)
        state.append(states)
        pm.append(pms)
        """ 
        c.execute("INSERT INTO testtable (jobnumber, jdescription, sitenumber, chargetype, employeecode, firstname, lastname, fullname, clockindate, hours, city, state, projectmanager) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
                  (jnu, jde, sit, tra, emp, fir, las, nam, cin, hou, cit, sta, pjm)
        conn.commit()

##for rows in range(1):
##    insert_data()
 
##c.close()
conn.close() 

[Larz60+]

Please attach sample data so can run.

[jimmyvegas29]

Thanks for the reply's

here is a link to the sample data

https://nofile.io/f/4hUahgtFk89/sampledata.csv


Woooee - Yah i figured i was doing double the work i needed to, i couldnt find anything on how to do exactly what i wanted, so i kinda took 2 different things that i was able to learn how to do, and merge them together.

Im having trouble understanding what you actually did, do you mind elebarating a little bit? i understand that you commented out some stuff with the ##, but i dont get the triple ", what does that do? Ive come across it in my learning, but i thought it was so i could type anything inside of it i wanted.

I ran the code you replied with and im getting a syntax error at conn.commit()

---

I got it work, there was a ) missing.

here is the code after i cleaned it up after your suggestions, let me know if this looks good.

import csv
import sqlite3
  
 
conn = sqlite3.connect('testdb.db')
c = conn.cursor()
c.execute('CREATE TABLE IF NOT EXISTS testtable(jobnumber STRING, jdescription TEXT, sitenumber STRING, chargetype TEXT, employeecode INTEGER, firstname TEXT, lastname TEXT, fullname TEXT, clockindate DATE, hours DECIMAL(2), city TEXT, state TEXT, projectmanager TEXT)')

  
 
with open('C:\Python 3.5\Stuff\hours.csv') as csvfile:
    readCSV = csv.reader(csvfile, delimiter=',')
  
    for row in readCSV:
  
        jnumbers = row[3]
        jdescriptions = row[4]
        sitenumbers = row[5]
        travels = row[6]
        empcodes = row[7]
        firstnames = row[8]
        lastnames = row[9]
        names = row[10]
        cindates = row[11]
        hourss = row[12]
        citys = row[13]
        states = row[14]
        pms = row[16]
  
     
  
        c.execute("INSERT INTO testtable (jobnumber, jdescription, sitenumber, chargetype, employeecode, firstname, lastname, fullname, clockindate, hours, city, state, projectmanager) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
                  (jnumbers, jdescriptions, sitenumbers, travels, empcodes, firstnames, lastnames, names, cindates, hourss, citys, states, pms))
        conn.commit()

conn.close()

---

I forgot to mention, THANK YOU SO MUCH FOR YOUR HELP!!!!

[woooee]

python triple quotes https://www.google.com/search?q=python+t...e&ie=UTF-8

[jimmyvegas29]

ahh, so its basically just using the triple quotes to say throw this stuff in the trash lol.

Thank you again for your help