SQL problem in python

[aiwarrior]

class db:
def __init__(self): #constructor
conn = sqlite3.connect(":memory:")
conn.isolation_level = None
self.cursor = conn.cursor()
self.cursor.execute("CREATE TABLE database (album,filepath)")

def add_entry(self, eone , etwo): #Add entry to database
self.cursor.execute("INSERT INTO database (album,filepath)
VALUES (?,?)", ( eone , etwo ) )
return 1 #TODO: exception handler

def get_mediadb(self, print_db = False):
self.cursor.execute('SELECT * FROM database')
if (print_db == True):
print self.cursor.fetchall()

def get_value( self, column ):
self.cursor.execute( "SELECT (?) FROM database", column )
for n in self.cursor:
print n

def destructor(self):
self.cursor.close()

if __name__ == "__main__":
f = db()
f.add_entry( "Pinkk Floyd", "fdgf" )
f.add_entry( "Pink", "fdgf" )
# f.get_mediadb(print_db=True)
f.get_value(('filepath',))
f.destructor()

When i run it the get_value() returns 'filepath' instead of the
columns. But if i dont use any variable and make the expression static
all goes on as its supposed to. What am i doing wrong?

PS: Dont mind the bad code

 

[Carsten Haese]

def get_value( self, column ):
self.cursor.execute( "SELECT (?) FROM database", column )
for n in self.cursor:
print n

When i run it the get_value() returns 'filepath' instead of the
columns. But if i dont use any variable and make the expression static
all goes on as its supposed to. What am i doing wrong?

Using parameter binding wherever possible is a Good Idea, but parameter
binding can only be used to provide values. It can not be used to
provide syntax elements, table names, or column names. Basically, only
pieces that don't influence the query plan can be provided by
parameters.

To build a select query with a variable column name, you'll have to
resort to some kind of string building mechanism:

def get_value( self, column ):
self.cursor.execute( "SELECT %s FROM database" % column )
for n in self.cursor:
print n

HTH,

 

[Peter Otten]

When i run it the get_value() returns 'filepath' instead of the
columns. But if i dont use any variable and make the expression static
all goes on as its supposed to. What am i doing wrong?

self.cursor.execute( "SELECT (?) FROM database", column )

In this case you have to use Python's string interpolation, or the column
will be interpreted as a const value. The following should work:

self.cursor.execute( "SELECT %s FROM database" % column)

If you must sanitize the column name you can prepend something like

if column not in allowed_names: raise ValueError

Peter

 

[aiwarrior]

Thanks a lot.
In the Python documentation, the sqlite module documentation doesn't
mention that special rule. I really thought that every variable to be
included in a query had to use that special method.

Again thanks a lot

 

[Dennis Lee Bieber]

Thanks a lot.
In the Python documentation, the sqlite module documentation doesn't
mention that special rule. I really thought that every variable to be
included in a query had to use that special method.

It's not sqlite specific -- it is common to all of the db-api
compatible adapters.

The explanation for why you were getting 'filename' (or whatever the
field was is: parameter substitution ensures that the parameter value is
properly quoted and escaped to be safe for use as a value. So you query
was not turning into:

select filename from ...

but

select 'filename' from ...

ie; select a constant string value
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

 

[Carsten Haese]

It's not sqlite specific -- it is common to all of the db-api
compatible adapters.

The explanation for why you were getting 'filename' (or whatever the
field was is: parameter substitution ensures that the parameter value is
properly quoted and escaped to be safe for use as a value.

It's called "parameter binding", not "parameter substitution".
Parameter binding ensures that the value is passed to the database
safely. That need not necessarily be a quoting-and-escaping exercise.
Depending on the API module, it may use a mechanism that transmits the
query string and the parameters to the database engine separately.

Also note that SQL standard doesn't even allow parameters in the
projection clause of a select statement. SQLite is just more relaxed
about the standard than for example Informix:
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
_informixdb.ProgrammingError: SQLCODE -201 in PREPARE:
42000: Syntax error or access violation

 

[petr.jakes.tpc]

Maybe you should try SQLObject

from sqlobject import *
from sqlobject.sqlbuilder import Select
#from sqlobject.sqlbuilder import *
from datetime import datetime
# =========== sqlite ==============================
#connection = connectionForURI('sqlite:///dev/shm/ourdata.db')
connection = connectionForURI('sqlite:/:memory:')
sqlhub.processConnection = connection

class MyTable(SQLObject):
album = StringCol(length=20, default=None)
filepath = StringCol(length=50, default=None)

#MyTable.dropTable(ifExists=True)
#MyTable._connection.debug = True # you can switch debuging ON, so you
can see SQL commands generated by SQLObject
MyTable.createTable(ifNotExists=True)

MyTable(album ="Pinkk Floyd", filepath= "qwst" )
MyTable(album ="Pinkk", filepath= "gbfbd" )
MyTable(album ="Floyd", filepath= "fdgf" )

q = MyTable.select()
for row in q:
print row.album, row.filepath
for row in MyTable.select(MyTable.q.album == "Pinkk Floyd"):
print row.album, row.filepath

HTH

Petr Jakes