pysqlite - simple problem

Discussion in 'Python' started by rdrink, Sep 1, 2006.

  1. rdrink

    rdrink Guest

    I am just getting into pysqlite (with a fair amount of Python and MySQL
    experience behind me) and have coded a simple test case to try to get
    the hang of things...
    yet have run into a 'stock simple' problem...

    I can create a database 'test.db', add a table 'foo' (which BTW I
    repeatedly DROP on each run) with one INTGER column 'id', and can
    insert data into with:
    cur.execute("INSERT INTO foo (id) VALUES (200)")
    con.commit()
    (and fetch back out)
    all with no problem. But...

    If I try to expand this to:
    num = 200
    cur.execute("INSERT INTO foo (id) VALUES (?)", num)
    I get the error...
    Traceback (most recent call last):
    File "/home/rdrink/Programming/Python/Inner_Square/sqlite_test.py",
    line 46, in ?
    cur.execute("INSERT INTO foo (id) VALUES (?)", num)
    File "/usr/lib/python2.4/site-packages/sqlite/main.py", line 255, in
    execute
    self.rs = self.con.db.execute(SQL % parms)
    TypeError: not all arguments converted during string formatting
    .... which obviously points to a 'typing' problem.
    ?? but where ??
    >From all the docs I have read Python 'int' and sqlite INTEGER should

    pass back and forth seemlessly...
    And I have even tried to reduce things to simply:
    cur.execute("INSERT INTO foo (id) VALUES (?)", 200)
    but still raise the same error.

    So this has to be something stupidly simple... but for the life of me I
    can't see it.

    Advice, suggestions, pointers for the noob?

    rd
     
    rdrink, Sep 1, 2006
    #1
    1. Advertising

  2. rdrink

    Gerold Penz Guest

    rdrink schrieb:
    > num = 200
    > cur.execute("INSERT INTO foo (id) VALUES (?)", num)


    Hi!

    ``num`` must be an iterable object (tuple, list, ...).

    num = (200,)
    cur.execute("INSERT INTO foo (id) VALUES (?)", num)

    Regards,
    Gerold
    :)

    --
    ________________________________________________________________________
    Gerold Penz - bcom - Programmierung
    | http://gerold.bcom.at | http://sw3.at
    Ehrliche, herzliche Begeisterung ist einer der
    wirksamsten Erfolgsfaktoren. Dale Carnegie
     
    Gerold Penz, Sep 1, 2006
    #2
    1. Advertising

  3. "rdrink" <> wrote:

    >I am just getting into pysqlite (with a fair amount of Python and MySQL
    > experience behind me) and have coded a simple test case to try to get
    > the hang of things...
    >
    > yet have run into a 'stock simple' problem...


    what does

    import sqlite
    print sqlite.paramstyle
    print sqlite.version

    print on your machine ?

    (afaik, version 1 of the python bindings use paramstyle=pyformat, version
    2 uses qmark. maybe you have a version 1 library ?)

    </F>
     
    Fredrik Lundh, Sep 1, 2006
    #3
  4. rdrink

    John Machin Guest

    rdrink wrote:
    > I am just getting into pysqlite (with a fair amount of Python and MySQL
    > experience behind me) and have coded a simple test case to try to get
    > the hang of things...
    > yet have run into a 'stock simple' problem...
    >
    > I can create a database 'test.db', add a table 'foo' (which BTW I
    > repeatedly DROP on each run) with one INTGER column 'id', and can
    > insert data into with:
    > cur.execute("INSERT INTO foo (id) VALUES (200)")
    > con.commit()
    > (and fetch back out)
    > all with no problem. But...
    >
    > If I try to expand this to:
    > num = 200
    > cur.execute("INSERT INTO foo (id) VALUES (?)", num)
    > I get the error...
    > Traceback (most recent call last):
    > File "/home/rdrink/Programming/Python/Inner_Square/sqlite_test.py",
    > line 46, in ?
    > cur.execute("INSERT INTO foo (id) VALUES (?)", num)
    > File "/usr/lib/python2.4/site-packages/sqlite/main.py", line 255, in
    > execute
    > self.rs = self.con.db.execute(SQL % parms)
    > TypeError: not all arguments converted during string formatting
    > ... which obviously points to a 'typing' problem.
    > ?? but where ??
    > >From all the docs I have read Python 'int' and sqlite INTEGER should

    > pass back and forth seemlessly...
    > And I have even tried to reduce things to simply:
    > cur.execute("INSERT INTO foo (id) VALUES (?)", 200)
    > but still raise the same error.
    >
    > So this has to be something stupidly simple... but for the life of me I
    > can't see it.


    With the '?' paramstyle, the 2nd arg to cursor.execute() should be a
    *sequence* (typically a tuple) of the values that you are inserting.

    Tty this:
    cur.execute("INSERT INTO foo (id) VALUES (?)", (num, ))

    This is standard Python DBAPI stuff - you would probably get a similar
    response from other gadgets e.g. mySQLdb -- IOW it's not specific to
    pysqlite.

    > Advice, suggestions, pointers for the noob?


    General advice: Read the docs -- both the gadget-specific docs and the
    Python DBAPI spec (found at http://www.python.org/dev/peps/pep-0249/).

    HTH,
    John
     
    John Machin, Sep 1, 2006
    #4
  5. On Fri, 1 Sep 2006 09:38:24 +0200, "Fredrik Lundh"
    <> declaimed the following in comp.lang.python:


    > what does
    >
    > import sqlite
    > print sqlite.paramstyle
    > print sqlite.version
    >
    > print on your machine ?
    >

    Can't speak for the original poster, but for me...
    ImportError: No module named sqlite

    >>> from pysqlite2 import dbapi2 as psql
    >>> psql.paramstyle

    'qmark'

    --
    Wulfraed Dennis Lee Bieber KD6MOG

    HTTP://wlfraed.home.netcom.com/
    (Bestiaria Support Staff: )
    HTTP://www.bestiaria.com/
     
    Dennis Lee Bieber, Sep 1, 2006
    #5
  6. John Machin wrote:
    >> So this has to be something stupidly simple... but for the life of me I
    >> can't see it.

    >
    > With the '?' paramstyle, the 2nd arg to cursor.execute() should be a
    > *sequence* (typically a tuple) of the values that you are inserting.
    >
    > Tty this:
    > cur.execute("INSERT INTO foo (id) VALUES (?)", (num, ))
    >
    > This is standard Python DBAPI stuff - you would probably get a similar
    > response from other gadgets e.g. mySQLdb -- IOW it's not specific to
    > pysqlite.


    that mistake gives an entirely different error message, at least under 2.2.0
    (which is the version shipped with 2.5):

    >>> import sqlite3
    >>> db = sqlite3.connect("foo.db")
    >>> cur = db.cursor()
    >>> cur.execute("CREATE TABLE foo (id INTEGER)")

    <pysqlite2.dbapi2.Cursor object at 0x00B7CEF0>
    >>> cur.execute("INSERT INTO foo (id) VALUES (?)", 200)

    Traceback (most recent call last):
    File "<stdin>", line 1, in <module>
    sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current
    statement uses 1, and there are -1 supplied.
    >>> cur.execute("INSERT INTO foo (id) VALUES (?)", [200])

    <pysqlite2.dbapi2.Cursor object at 0x00B7CEF0>

    (not sure "-1 arguments supplied" is any less confusing, though)

    </F>
     
    Fredrik Lundh, Sep 1, 2006
    #6
  7. rdrink

    John Machin Guest

    Fredrik Lundh wrote:
    > John Machin wrote:
    > >> So this has to be something stupidly simple... but for the life of me I
    > >> can't see it.

    > >
    > > With the '?' paramstyle, the 2nd arg to cursor.execute() should be a
    > > *sequence* (typically a tuple) of the values that you are inserting.
    > >
    > > Tty this:
    > > cur.execute("INSERT INTO foo (id) VALUES (?)", (num, ))
    > >
    > > This is standard Python DBAPI stuff - you would probably get a similar
    > > response from other gadgets e.g. mySQLdb -- IOW it's not specific to
    > > pysqlite.

    >
    > that mistake gives an entirely different error message, at least under 2.2.0
    > (which is the version shipped with 2.5):
    >


    You're right. I didn't spot that the OP may be using an antique:

    File "/usr/lib/python2.4/site-packages/sqlite/main.py"

    So the advice has to be augmented:
    1. Update to latest pysqlite2 (which BTW is a later version that that
    shipped with Python 2.5, just to add some confusion)
    2. Pass values as a sequence

    Cheers,
    John
     
    John Machin, Sep 1, 2006
    #7
  8. On 1 Sep 2006 00:47:39 -0700, "John Machin" <>
    declaimed the following in comp.lang.python:

    >
    > This is standard Python DBAPI stuff - you would probably get a similar
    > response from other gadgets e.g. mySQLdb -- IOW it's not specific to
    > pysqlite.
    >

    Poor choice of example -- MySQLdb is one that explicitly handles a
    scalar "args" when doing the data conversion/escaping, and then relies
    upon normal Python string formatting to create the final SQL... As I
    recall, it even works with dictionary "args" and %(id)s substitution...


    /cursor.py/
    def execute(self, query, args=None):

    <snip doc string>

    try:
    if args is None:
    r = self._query(query)
    else:
    r = self._query(query % self.connection.literal(args))
    except TypeError, m:

    <snip>


    /connection.py/
    def literal(self, o):
    """

    If o is a single object, returns an SQL literal as a string.
    If o is a non-string sequence, the items of the sequence are
    converted and returned as a sequence.

    Non-standard. For internal use; do not use this in your
    applications.

    """
    return self.escape(o, self.converter)
    --
    Wulfraed Dennis Lee Bieber KD6MOG

    HTTP://wlfraed.home.netcom.com/
    (Bestiaria Support Staff: )
    HTTP://www.bestiaria.com/
     
    Dennis Lee Bieber, Sep 1, 2006
    #8
  9. rdrink

    rdrink Guest

    Thanks everyone!
    But... RTFM? Ouch. It's not like I don't know what I'm doing :-(

    .... rather, that I *am* using the older sqlite module
    > print sqlite.paramstyle = pyformat
    > print sqlite.version = 1.0.1

    ..... which does not support the qmark sytax. (and I fell victim of
    someone elses tutorial).

    And yes I should prolly move to pysqlite2, but for now I was able to
    fix it this way...
    num = 200
    mess = "INSERT INTO foo (id) VALUES (%s)" % num
    cur.execute(mess)

    .... don't know why I didn't think of that last (oh wait, Yes I do...
    because 'last night' was actually 2am this morning, after working all
    day!)

    But thanks again to all of you for your help.
     
    rdrink, Sep 2, 2006
    #9
  10. On 1 Sep 2006 18:08:57 -0700, "rdrink" <> declaimed the
    following in comp.lang.python:


    > And yes I should prolly move to pysqlite2, but for now I was able to
    > fix it this way...
    > num = 200
    > mess = "INSERT INTO foo (id) VALUES (%s)" % num
    > cur.execute(mess)
    >

    That is probably the worst way to "fix" the problem -- as in the
    future, you may end up trying that method for something that may need to
    be quoted or escaped.

    cur.execute(template, (arg1,) )

    allows the DB-API spec to properly convert the argument to the string
    format (quoted or escaped) as needed.
    --
    Wulfraed Dennis Lee Bieber KD6MOG

    HTTP://wlfraed.home.netcom.com/
    (Bestiaria Support Staff: )
    HTTP://www.bestiaria.com/
     
    Dennis Lee Bieber, Sep 2, 2006
    #10
  11. rdrink wrote:


    > And yes I should prolly move to pysqlite2, but for now I was able to
    > fix it this way...
    > num = 200
    > mess = "INSERT INTO foo (id) VALUES (%s)" % num
    > cur.execute(mess)
    >
    > ... don't know why I didn't think of that last (oh wait, Yes I do...
    > because 'last night' was actually 2am this morning, after working all
    > day!)


    the "pyformat" parameter style means that you're supposed to use "%s"
    instead of "?" for the placeholders:

    cur.execute("INSERT INTO foo (id) VALUES (%s)", (num,))

    while string formatting works, and is safe for simple cases like this,
    it can quickly turn into a performance and security problem. better
    avoid it for anything other than command-line tinkering and throw-away
    scripts.

    (I'm sure this is mentioned in the fine manual, btw ;-)

    </F>
     
    Fredrik Lundh, Sep 2, 2006
    #11
  12. rdrink

    rdrink Guest

    Dennis Lee Bieber wrote:

    > That is probably the worst way to "fix" the problem -- as in the
    > future, you may end up trying that method for something that may need to
    > be quoted or escaped.
    >
    > cur.execute(template, (arg1,) )
    >
    > allows the DB-API spec to properly convert the argument to the string
    > format (quoted or escaped) as needed.


    Thank you Dennis, point taken.
    I will upgrade to pysqlite2 as soon as possible.

    >the "pyformat" parameter style means that you're supposed to use "%s"
    >instead of "?" for the placeholders:
    >
    > cur.execute("INSERT INTO foo (id) VALUES (%s)", (num,))


    Thanks Fredrick, that seems so obvious now!....

    > (I'm sure this is mentioned in the fine manual, btw ;-)


    .... I guess I have must have missed it ;-)

    >while string formatting works, and is safe for simple cases like this,
    >it can quickly turn into a performance and security problem. better
    >avoid it for anything other than command-line tinkering and throw-away
    >scripts.


    You are both right about the perils of a non-standard approach, which
    could easily break. Fortunately in this case this is a private project,
    so no worry there.
    -----
    And while you are both being so helpful, May I ask anyother stupid
    question?...
    One of the columns of my table contains a rather large list of numbers
    e.g. [12345, 76543, 89786, ... ] sometimes up to 500 entries long.
    And when I defined my table I set this column to text.
    But the problem with that approach is of course then that it gets
    returned as a string (which just happens to look like a list!) and I
    can't iter over it. However I can use rsplit(','), with the exception
    of the leading and trailing '[' ']', and I could fix that too... but
    that's not the point... the real question is: Is there a way to have
    python interperate the string "[ a,b,c ]" as a list? (and yes I have be
    reading up on typing)...
    OR
    Is there a better way to store this in sqlite, ala a BLOB or encoded?

    Thanks
    Robb
     
    rdrink, Sep 3, 2006
    #12
  13. On 3 Sep 2006 11:54:10 -0700, "rdrink" <> declaimed the
    following in comp.lang.python:

    > And while you are both being so helpful, May I ask anyother stupid
    > question?...
    > One of the columns of my table contains a rather large list of numbers
    > e.g. [12345, 76543, 89786, ... ] sometimes up to 500 entries long.
    > And when I defined my table I set this column to text.
    > But the problem with that approach is of course then that it gets
    > returned as a string (which just happens to look like a list!) and I
    > can't iter over it. However I can use rsplit(','), with the exception
    > of the leading and trailing '[' ']', and I could fix that too... but
    > that's not the point... the real question is: Is there a way to have
    > python interperate the string "[ a,b,c ]" as a list? (and yes I have be
    > reading up on typing)...
    > OR
    > Is there a better way to store this in sqlite, ala a BLOB or encoded?
    >


    Well... Approach 1 is: Don't do that...

    A relational database is not supposed to have "multi-values" in a
    column.

    Normalization would be that you either 1) create many columns (which
    requires knowing how many values could be in the list) or 2) create a
    separate table of:

    ID primary key
    Link foreign key (ID of the record in the original table)
    Value whatever (ONE value from the list.

    Instead of:

    32 | data | more | [list, of, stuff]

    you create

    32 | data | more

    AND

    1 | 32 | list
    2 | 32 | of
    3 | 32 | stuff



    However, if you trust that no one has booby-trapped the database
    contents, you could try:

    #this is the item returned by the database interface (with ... replaced)
    >>> db_list = "[12345, 76543, 89786, None]"


    >>> real_list = eval(db_list)
    >>> print real_list

    [12345, 76543, 89786, None]
    >>>

    --
    Wulfraed Dennis Lee Bieber KD6MOG

    HTTP://wlfraed.home.netcom.com/
    (Bestiaria Support Staff: )
    HTTP://www.bestiaria.com/
     
    Dennis Lee Bieber, Sep 3, 2006
    #13
    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. Hemanth P.S.
    Replies:
    0
    Views:
    354
    Hemanth P.S.
    Sep 18, 2003
  2. John Abel

    Problem Building PySQLite (Win32)

    John Abel, Oct 30, 2003, in forum: Python
    Replies:
    1
    Views:
    366
  3. F. GEIGER
    Replies:
    2
    Views:
    547
    F. GEIGER
    May 21, 2005
  4. pysqlite problem

    , Mar 1, 2006, in forum: Python
    Replies:
    6
    Views:
    369
  5. Gerhard Häring
    Replies:
    3
    Views:
    387
    Gerhard Häring
    Mar 13, 2008
Loading...

Share This Page