Help: Omitting quotes from SQL Queries.

[Gerhard Häring]

* Eric Brunel <eric.brunel at pragmadev.com> [2003-01-07 17:20 +0100]:
> keithk wrote:
> > Hi All,
> > 
> > I am using an mx.ODBC connection to MSSQL and am iterating through a
> > list to get data from the DB, for eg:
> > 
> > for fileName in fileNameList:
> >         cursor.execute("SELECT fileSize FROM database where
> >         database.filename = '%s'" % fileName)
> >         fileSizes = cursor.fetchall()
> > 
> > However, when it iterates through the list and fileName equals something
> > like "Hello World's" with a single quote inside the query fails as the
> > string is ended prematurely, does anybody know how I can solve this?
> 
> fileName.replace("'", "''") ?
> 
> Doubling the quotes should do the trick.

It works, but it is not a good habit.

Use DB-API bound parameters:

    cursor.execute("""
        SELECT fileSize FROM database
        WHERE database.filename = %s
        """, (fileName,))

There's a reason cursor.execute has two parameters ;-)

Gerhard
-- 
Favourite database:             http://www.postgresql.org/
Favourite programming language: http://www.python.org/
Combine the two:                http://pypgsql.sf.net/
Embedded database for Python:   http://pysqlite.sf.net/