On Oct 15, 2010, at 10:20 AM, William Furnass wrote:
> Trying to follow the SQLA tutorial at
> http://mapfish.org/doc/tutorials/sqlalchemy.html
> but find that I get stuck at the first hurdle (the Engine API bit) due
> to a string formatting problem:
>
> sqlalchemy.exc.ProgrammingError: (ProgrammingError) syntax error at or
> near ","
> LINE 1: INSERT INTO users(username, password) VALUES ( ?, ? )
>
> ^
> 'INSERT INTO users(username, password) VALUES ( ?, ? )' (u'bob',
> u'bobpwd')
>
>
> Getting rid of the question marks and doing string interpolation in
> the typical way works just fine:
>
> connection.execute("INSERT INTO users(username, password) VALUES
> ( '%s', '%s' )'" % (u'bob', u'bobpwd'))
>
> but I'd rather make use of SQLAs interpolation mechanism so as to
> guard against SQL injection.
>
> Any ideas as to where I could be going wrong?
>
> SQLA 0.6.3
> Python 2.5
> Connection to a UTF8-encoded Postgres 8.4.4 database.
that tutorial is hardwiring a particular bindparam style into a statement,
known as "qmark", or question marks for binds. It is not supported by
Psycopg2.
The psycopg2 format would be :
connection.execute(
"""
INSERT INTO users (username, password) VALUES (%(uname)s, %(passwd)s);
""",
uname="foo", passwd="bar"
)
if you want to use a straight string and not worry about DBAPIs strange
decision to allow any random subset of six types of bindparam formats, use
text():
connection.execute(
text("""
INSERT INTO users (username, password) VALUES (:uname, :passwd);
"""),
uname="foo", passwd="bar"
)
as soon as you get into normal expression language usage with table metadata
you don't really deal with these distinctions anymore.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
