Importing CSV into Sqlite3 DB

Python Sqlite3 and csv module help

Page 1 of 1

3 Replies - 11833 Views - Last Post: 15 September 2010 - 07:19 AM Rate Topic: -----

#1 Mow_RSA  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 8
  • Joined: 14-September 10

Importing CSV into Sqlite3 DB

Posted 14 September 2010 - 11:50 PM

Hi all .

First post.

Please have a look at this . I just cant figure out how to insert the values into the table "safely" as per code block 3 on the python docs link below. I just dont undertsand how to define the value of the symbols from a csv file.

I need to add all the values of the csv into the created table.

http://docs.python.o...ry/sqlite3.html


import csv
import sqlite3

#Create the database#
connection = sqlite3.connect('//home/mgrobler/database/working/pricelist')
cursor = connection.cursor()

cursor.execute('DROP TABLE prices')
cursor.execute('CREATE TABLE  prices ( Code text, Description text, Price integer) ')
connection.commit


#parse csv and read it into the database#
creader = csv.reader(open('//home/mgrobler/database/src/pricelist.csv', 'rb'), delimiter=',', quotechar='|')

t = (creader,)

for t in creader:
    cursor.execute('INSERT INTO  prices VALUES (?,?,?)', t )




Im no pro so ive been stuck with this for days . If someone can help me out by explaining the symbol method within the context of my code above it would be greatly appreciated.

Is This A Good Question/Topic? 0
  • +

Replies To: Importing CSV into Sqlite3 DB

#2 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5882
  • View blog
  • Posts: 12,761
  • Joined: 16-October 07

Re: Importing CSV into Sqlite3 DB

Posted 15 September 2010 - 04:12 AM

Step one would be to make sure you're getting what you think you're getting, so just do this:
creader = csv.reader(open('//home/mgrobler/database/src/pricelist.csv', 'rb'), delimiter=',', quotechar='|')

# t = (creader,) this is meaningless

for t in creader:
	print t
	break



Does t have no more or less than three values?

If it's what you expect, but too long, then maybe something like:

for t in creader:
	if len(t)>=3:
		data = [ v[i] for i in range(3) ]
		cursor.execute('INSERT INTO  prices VALUES (?,?,?)', data )


Was This Post Helpful? 0
  • +
  • -

#3 Motoma  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 452
  • View blog
  • Posts: 796
  • Joined: 08-June 10

Re: Importing CSV into Sqlite3 DB

Posted 15 September 2010 - 04:36 AM

Welcome to Dream in Code!

I'm guessing your problem is that you aren't seeing the inserted values in the database. The issue is that you haven't committed the changes. Here's a revised piece of code that should work fine:

import csv
import sqlite3

# Create the database
connection = sqlite3.connect('//home/mgrobler/database/working/pricelist')
cursor = connection.cursor()

# Create the table
cursor.execute('DROP TABLE IF EXISTS prices')
cursor.execute('CREATE TABLE  prices ( Code text, Description text, Price integer) ')
connection.commit()

# Load the CSV file into CSV reader
csvfile = open('//home/mgrobler/database/src/pricelist.csv', 'rb')
creader = csv.reader(csvfile, delimiter=',', quotechar='|')

# Iterate through the CSV reader, inserting values into the database
for t in creader:
    cursor.execute('INSERT INTO  prices VALUES (?,?,?)', t )

# Close the csv file, commit changes, and close the connection
csvfile.close()
connection.commit()
connection.close()



If you have any questions about specific changes I made, please don't hesitate to ask.

Cheers,
Motoma

This post has been edited by Motoma: 15 September 2010 - 04:38 AM

Was This Post Helpful? 0
  • +
  • -

#4 Mow_RSA  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 8
  • Joined: 14-September 10

Re: Importing CSV into Sqlite3 DB

Posted 15 September 2010 - 07:19 AM

Firstly let me say thank you for taking the time to understand my poorly phrased questions.

@baavgai

You were spot on , printed the contents and suddenly my error makes sense

"Incorrect number of bindings supplied" Statement used 3 and 4 supplied

im working with numbers like 1,234.12 it sees the "," in 1,234.12 and creates a 4th field.

So my error wasnt anywhere near what i though it was.

Need to figure out how to avoid this problem either by changing the formatting in the csv or compensating for it in python .

Thanx for your help.

@ Motoma

thanx for the end code.... I know to commit the database changes but i would not have know to close the csvfile.

Thanx all sorted now.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1