[Tutor] Problem with mxODBC insert statement

[Danny Yoo]

> Khawaja-Shahzad Butt wrote:
>
> > I don't know how to use SQL which way for mxODBC and python. I used
> > this statement and got this error.
> >
> >q= "INSERT INTO
> >rss_feed_items(item_date,item_title,item_author,item_permalink,
> >               item_description)
> >   VALUES(%s)"%(item_dmodified);


Hello,


What's the value of item_dmodified here?  The SQL query that you're
generating will probably need correction, as there needs to be as many
values as there are columns in the SQL insertion statement.  I expected
to see something like:


###
q = """INSERT INTO rss_feed_items(
    item_date,
    item_title,
    item_author,
    item_permalink,
    item_description)
VALUES(
    ?, ?, ?, ?, ?)"""
###



> >or can i use the ? instead of %s can you give me example since there is
> >none in egenix manual for mxODBC. What am i doing wrong. Also should
> >always use auto_commit while using mxODBC


Let me check the mxODBC manual... ok, here's what they say about
parameters:

"""
  execute(operation[,parameters])
    Prepare and execute a database operation (query or command).

    Parameters must be provided as sequence and will be bound to variables
in the operation. Variables are specified using the ODBC variable
placeholder '?', e.g. 'SELECT name,id FROM table WHERE amount > ? AND
amount < ?' (also see the module attribute paramstyle) and get bound in
the order they appear in the SQL statement from left to right.

    A reference to the operation will be retained by the cursor. If the
same operation object is passed in again, then the cursor will optimize
its behavior by reusing the previously prepared statement. This is most
effective for algorithms where the same operation is used, but different
parameters are bound to it (many times). """

    (http://www.egenix.com/files/python/mxODBC.html)


So yes, you can use "?" parameter syntax.  For example:


###
query = "select * from person where age = ? or grade > ?"
cursor = conn.cursor()
cursor.execute(query, 25, 60)
###

And in fact, you should always use parameter "prepared statements" in SQL
unless you have an overwhelming reason not too.  There are a lot of
potential problems that you can run into if you try to do the string
interpolation yourself, so I'd recommend letting the mxODBC driver handle
interpolation for you.


Good luck!