You mean like
sql = "select distinct p.ID from ..." % (...)
# ^^^^^^^^
cursor.execute(sql)
package_ids = [row[0] for row in cursor.fetchall()]
It would also help if you didn't pass the categoryID as a
string-formatted value, but as a proper parameter, something like
sql = "... where c.categoryid=?" % (store, store)
cursor.execute(sql, (category_id,))
This helps prevent SQL-injection attacks (assuming you have full
control over the value of "store"...otherwise, as you've been
advised, if the remote user has control over the value in
"store", you're asking to be exploited). You'd have to check the
place-holder character for your particular back-end:
>>> import <your database engine> as db
>>> print db.paramstyle
should tell you whether to use "?", "%s", or some other notation.
The problem is something that was discussed in one of your other
numerous threads by John Machin and me. The issue is the
parameterization of (i.e. sticking variable bits into) SQL queries.
When you write
curs.execute("some sql query with %s and %s in it", (data1, data2))
the second argument to execute is supposed to contain data values. This
allows the SQL engine to do the preparatory work for a query once, and
then use the same "prepared query" then next time it's executed. The
preparation involves scanning the SQL query to make sure the syntax is
correct, validating the table and column names, and developing a "query
execution plan" that is a sequence of internal operations the database
performs to get you the answer you want. (SQL, unlike Python, is a
"declarative" language - rather than telling it what to do you describe
the results you want to see and the engine works out how to provide it).
Of course, if different tables are used for different queries then there
is no hope that the same execution plan can be used for them. For this
reason most database processors (and this certainly includes the one you
are using) don't allow you to parameterize anything in SQL statement
other than data values. So
curs.execute("select field1 from tableA where id=%s", (3, ))
is OK, but
curs.execute("select field1 from tableA where %s=3", ("id", ))
is definitely not. And
curs.execute("select field1 from %stable where id=3", (storename, ))
is right out.
This goes back to the issue of your database design. You have chosen to
represent each store with its own set of tables, where an experienced
database designer would instead have used just one set of tables, each
table having a "store" column that would allow you to use parameterized
queries to select the relevant data. There are many other sound reasons
for making such a design choice, which primarily boil down to
operational and management efficiency and simplicity.
But we are now in the realm of theory as far as you are concerned, since
you have already stated several times that you aren't interested in
correcting your design until after you have got the current mess into
production. So good luck with that.
regards
Steve
--
Steve Holden +1 571 484 6266 +1 800 494 3119
PyCon is coming! Atlanta, Feb 2010 http://us.pycon.org/
Holden Web LLC http://www.holdenweb.com/
UPCOMING EVENTS: http://holdenweb.eventbrite.com/
That means, MySQLdb uses %s as a placeholder for parameter substitution --
same as Python when doing string interpolation. Unfortunately this will
confuse things. In your code above, the ? near the end should become %s --
but you don't want THAT %s to be interpreted by Python at that time,
instead it must remain as a literal %s until the cursor.execute line. You
have to escape the % by doubling it: %%s
sql = 'select distinct p.ID from %sPackages p join
%sCategoriesPackages c where c.CategoryID=%%s;' % (store, store)
cursor.execute(sql, (categoryID,))
packageIDs = [itm[0] for itm in cursor]
--
Gabriel Genellina
I maintain my (MySQL) databases in an agile fashion. That means I start
small, and let my databases evolve along with my applications. So the
schema changes a lot during development. I take into account that the
databases already contain data and that it must be preserved and
migrated. I wrote a howto on my upgrading method and it can be found here:
http://www.howtoforge.org/node/4833
Hope this is of any help.
Good luck,
Dikkie