[Mow_RSA]

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.

[baavgai]

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 )

[Motoma]

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

[Mow_RSA]

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.