Python Forum
Importing csv data into sql lite cant figure it out. - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Importing csv data into sql lite cant figure it out. (/thread-11562.html)

Importing csv data into sql lite cant figure it out. - jimmyvegas29 - Jul-15-2018

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)

[Image: dboutput.png]

Here is a picture of the csv data

[Image: csvdata.png]

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.

RE: Importing csv data into sql lite cant figure it out. - woooee - Jul-16-2018

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


RE: Importing csv data into sql lite cant figure it out. - Larz60+ - Jul-16-2018

Please attach sample data so can run.

RE: Importing csv data into sql lite cant figure it out. - jimmyvegas29 - Jul-16-2018

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!!!!

RE: Importing csv data into sql lite cant figure it out. - woooee - Jul-16-2018

python triple quotes https://www.google.com/search?q=python+triple+quotes&oq=python+triple+quotes&aqs=chrome..69i57.4462j0j1&sourceid=chrome&ie=UTF-8

RE: Importing csv data into sql lite cant figure it out. - jimmyvegas29 - Jul-16-2018

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

Thank you again for your help

This forum uses Lukasz Tkacz MyBB addons.
Forum use Krzysztof "Supryk" Supryczynski addons.