importing csv file into sqlite

[John Machin]

On Dec 18, 6:20 pm, klia <alwaseem307s... at yahoo.com> wrote:
> klia wrote:
>
> > hey guys, i have a hug .csv file which i need to insert it into sqlite
> > database using python.
> > my csv data looks like this
> > Birthday2,12/5/2008,HTC,this is my birthday
> > Sea,12/3/2008,kodak,sea
> > birthday4,14/3/2009,samsung,birthday
> > love,17/4/2009,SONY,view of island
>
> > can any one give me a head start codes.
>
> > thanks in advance
>
> guys so far i came out with this but i get this error
> waseem at Linux:~/Project2$ python experment.py
> Traceback (most recent call last):
>   File "experment.py", line 13, in <module>
>     curse.execute('INSERT INTO photos VALUES (?,?,?,?)',item)
> sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current
> statement uses 4, and there are 1 supplied.
>
> here's the codes
>
> import sqlite3
> import csv
>
> f = open('/home/waseem/Project2/photos.txt')
> csv.field_size_limit(100000) #see below!

I see nothing "below" that looks at all like an attempt to justify
setting the field size limit to 100000 -- why are you doing that?
Tends to make one suspect a problem with your delimiter and/or your
line separator.

> input = csv.reader(f, delimiter='\t')

Why \t??? Your data examples show commas -- could this be why you are
getting one field per line (as Peter has pointed out)?

> conn = sqlite3.connect('/home/waseem/Project2/picutres.db')

Is it really called "picutres" instead of "pictures", or are you
typing the code that you ran again from (your) memory?

> curse = conn.cursor()
>
> curse.execute('CREATE TABLE photos (Name VARCHAR(100) PRIMARY KEY, Date
> INTEGER, Make VARCHAR(50), Tag VARCHAR(100))')

[OT but to save the next question]
The column named "Date" is defined to be INTEGER but the data from the
CSV file will be a str object e.g. "12/5/2008" ... I know sqlite
cheerfully regards column types as vague reminders rather than
enforceable constraints on your input, but wouldn't you like to
convert your dates to e.g. "2008-05-12" before you poke them in? You
may want to use "ORDER BY Date" at some stage, and so that ORDER BY
isn't whacked and GROUP BY doesn't give ludicrous results, wouldn't it
be a good idea to crunch 12/5/2008 and 12/05/2008 into a common format
so that they compare equal?
>
> for item in input:

I strongly suggest that you try to get a clue about exactly what you
are getting from the csv reader e.g.

for line_num, item in enumerate(input_renamed_as_suggested_by_anor):
   print line_num, repr(item)

>         curse.execute('INSERT INTO photos VALUES (?,?,?,?)',item)
> curse.commit()
>

HTH,
John