Re: importing csv file into sqlite

Discussion in 'Python' started by klia, Dec 18, 2008.

  1. klia

    klia Guest

    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@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!
    input = csv.reader(f, delimiter='\t')
    conn = sqlite3.connect('/home/waseem/Project2/picutres.db')
    curse = conn.cursor()

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

    for item in input:
    curse.execute('INSERT INTO photos VALUES (?,?,?,?)',item)
    curse.commit()

    --
    View this message in context: http://www.nabble.com/importing-csv-file-into-sqlite-tp21067453p21068111.html
    Sent from the Python - python-list mailing list archive at Nabble.com.
     
    klia, Dec 18, 2008
    #1
    1. Advertising

  2. klia

    John Machin Guest

    On Dec 18, 6:20 pm, klia <> 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@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
     
    John Machin, Dec 18, 2008
    #2
    1. Advertising

  3. klia

    klia Guest

    John Machin wrote:
    >
    > On Dec 18, 6:20 pm, klia <> 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@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
    > --
    > http://mail.python.org/mailman/listinfo/python-list
    >
    >


    hey guys
    i took all of your suggestion but my goal ain't yet achieved :-((
    these are the codes after changes, john i couldn't really catch what do you
    mean by renaming input, is it just normal renaming.i am testing the code on
    just simple .csv file with few data in as follows before trying on my hug
    csv file but still no joy

    "Bithday",12-05-08,"HTC","this is my birthday"
    "Sea",12-03-08,"kodak","sea"
    "girl","14-03-2009","samsung","birthday"
    "love","17-04-2009","SONY","view of island"

    import sqlite3
    import csv

    f = open('/home/waseem/Project2/photos.csv')
    input = csv.reader(f, delimiter=',')
    conn = sqlite3.connect('/home/waseem/Project2/pictures.db')
    curse = conn.cursor()

    curse.execute('CREATE TABLE photos (Name VARCHAR(100) PRIMARY KEY, Date
    INTEGER, Make VARCHAR(50), Tag VARCHAR(100))')
    for row in input:
    curse.execute('INSERT INTO photos VALUES (?,?,?,?)', '*row')
    curse.commit()

    this time i got this error

    waseem@Linux:~/Project2$ python experment.py
    Traceback (most recent call last):
    File "experment.py", line 12, in <module>
    curse.execute('INSERT INTO photos VALUES (?,?,?,?)', '*row')
    sqlite3.IntegrityError: column Name is not unique

    i removed the primary key and single quotation mark for '*row' to just *row
    but i got the old error which is

    waseem@Linux:~/Project2$ python experment.py
    Traceback (most recent call last):
    File "experment.py", line 11, in <module>
    curse.execute('INSERT INTO photos VALUES (?,?,?,?)', *row)
    TypeError: function takes at most 2 arguments (5 given)

    --
    View this message in context: http://www.nabble.com/importing-csv-file-into-sqlite-tp21067453p21090356.html
    Sent from the Python - python-list mailing list archive at Nabble.com.
     
    klia, Dec 19, 2008
    #3
  4. klia

    Peter Otten Guest

    klia wrote:

    >
    >
    > John Machin wrote:
    >>
    >> On Dec 18, 6:20 pm, klia <> 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@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
    >> --
    >> http://mail.python.org/mailman/listinfo/python-list
    >>
    >>

    >
    > hey guys
    > i took all of your suggestion but my goal ain't yet achieved :-((
    > these are the codes after changes, john i couldn't really catch what do
    > you mean by renaming input, is it just normal renaming.i am testing the
    > code on just simple .csv file with few data in as follows before trying on
    > my hug csv file but still no joy
    >
    > "Bithday",12-05-08,"HTC","this is my birthday"
    > "Sea",12-03-08,"kodak","sea"
    > "girl","14-03-2009","samsung","birthday"
    > "love","17-04-2009","SONY","view of island"
    >
    > import sqlite3
    > import csv
    >
    > f = open('/home/waseem/Project2/photos.csv')
    > input = csv.reader(f, delimiter=',')
    > conn = sqlite3.connect('/home/waseem/Project2/pictures.db')
    > curse = conn.cursor()
    >
    > curse.execute('CREATE TABLE photos (Name VARCHAR(100) PRIMARY KEY, Date
    > INTEGER, Make VARCHAR(50), Tag VARCHAR(100))')
    > for row in input:
    > curse.execute('INSERT INTO photos VALUES (?,?,?,?)', '*row')


    This will try to set

    Name="*"
    Date="r"
    Make="o"
    Tag="w"

    for all records. Can you work out why?

    > curse.commit()
    >
    > this time i got this error
    >
    > waseem@Linux:~/Project2$ python experment.py
    > Traceback (most recent call last):
    > File "experment.py", line 12, in <module>
    > curse.execute('INSERT INTO photos VALUES (?,?,?,?)', '*row')
    > sqlite3.IntegrityError: column Name is not unique
    >
    > i removed the primary key and single quotation mark for '*row' to just
    > *row but i got the old error which is
    >
    > waseem@Linux:~/Project2$ python experment.py
    > Traceback (most recent call last):
    > File "experment.py", line 11, in <module>
    > curse.execute('INSERT INTO photos VALUES (?,?,?,?)', *row)
    > TypeError: function takes at most 2 arguments (5 given)


    curse.execute('INSERT INTO photos VALUES (?,?,?,?)', row)

    (no '*') will work for the sample data you give above. If you get errors
    with your real data you have to fix that data. Follow John's advice to
    locate the problematic records.

    Peter
     
    Peter Otten, Dec 19, 2008
    #4
  5. klia

    John Machin Guest

    On Dec 19, 11:17 pm, klia <> wrote:

    [ancient screed snipped]

    >
    > hey guys
    > i took all of your suggestion but my goal ain't yet achieved :-((
    > these are the codes after changes, john i couldn't really catch what do you
    > mean by renaming input, is it just normal renaming.


    Somebody else told you not to use "input", choose another name

    > i am testing the code on
    > just simple .csv file with few data in as follows before trying on my hug
    > csv file but still no joy
    >
    > "Bithday",12-05-08,"HTC","this is my birthday"
    > "Sea",12-03-08,"kodak","sea"
    > "girl","14-03-2009","samsung","birthday"
    > "love","17-04-2009","SONY","view of island"
    >
    > import sqlite3
    > import csv
    >
    > f = open('/home/waseem/Project2/photos.csv')
    > input = csv.reader(f, delimiter=',')
    > conn = sqlite3.connect('/home/waseem/Project2/pictures.db')
    > curse = conn.cursor()
    >
    > curse.execute('CREATE TABLE photos (Name VARCHAR(100) PRIMARY KEY, Date
    > INTEGER, Make VARCHAR(50), Tag VARCHAR(100))')
    > for row in input:
    >         curse.execute('INSERT INTO photos VALUES (?,?,?,?)', '*row')
    > curse.commit()
    >
    > this time i got this error
    >
    > waseem@Linux:~/Project2$ python experment.py
    > Traceback (most recent call last):
    >   File "experment.py", line 12, in <module>
    >     curse.execute('INSERT INTO photos VALUES (?,?,?,?)', '*row')
    > sqlite3.IntegrityError: column Name is not unique
    >
    > i removed the primary key and single quotation mark for '*row' to just *row
    > but i got the old error which is
    >
    > waseem@Linux:~/Project2$ python experment.py
    > Traceback (most recent call last):
    >   File "experment.py", line 11, in <module>
    >     curse.execute('INSERT INTO photos VALUES (?,?,?,?)', *row)
    > TypeError: function takes at most 2 arguments (5 given)
    >


    No, the old error was """sqlite3.ProgrammingError: Incorrect number of
    bindings supplied. The current statement uses 4, and there are 1
    supplied.""" That's quite different.

    Secondly you have already been told about the difference between row
    and *row ... as the message says, it wants 2 arguments, (1) the SQL
    (2) the sequence of values that will be used to replace the ?s in the
    SQL. So just lose the *, OK?
     
    John Machin, Dec 19, 2008
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. klia
    Replies:
    0
    Views:
    1,027
  2. klia
    Replies:
    0
    Views:
    303
  3. James Mills

    Re: importing csv file into sqlite

    James Mills, Dec 18, 2008, in forum: Python
    Replies:
    3
    Views:
    1,632
    John Machin
    Dec 19, 2008
  4. Chris Rebert

    Re: importing csv file into sqlite

    Chris Rebert, Dec 18, 2008, in forum: Python
    Replies:
    1
    Views:
    535
    Peter Otten
    Dec 18, 2008
  5. Carl Youngblood
    Replies:
    1
    Views:
    234
    Carl Youngblood
    Apr 9, 2005
Loading...

Share This Page