curious paramstyle qmark behavior

[BartlebyScrivener]

With

aColumn = "Topics.Topic1" '

The first statement "works" in the sense that it finds a number of
matching rows.

c.execute ("SELECT Author, Quote, ID, Topics.Topic1, Topic2 FROM
QUOTES7 WHERE " + aColumn + " LIKE ?", ("%" + sys.argv[1] + "%",))

I've tried about 20 different variations on this next one. And it finds
0 records no matter what I do. Is there some violation when I use two
qmarks?

c.execute ("SELECT Author, Quote, ID, Topics.Topic1, Topic2 FROM
QUOTES7 WHERE ? LIKE ?", (aColumn, "%" + sys.argv[1] + "%"))

I'm using mx.ODBC and Python 2.4.3 to connect to an MS Access DB.

Thank you,

rd

[Jon Clements]

Re: curious paramstyle qmark behavior


BartlebyScriven er wrote:

With
>
aColumn = "Topics.Topic1" '
>
The first statement "works" in the sense that it finds a number of
matching rows.
>
c.execute ("SELECT Author, Quote, ID, Topics.Topic1, Topic2 FROM
QUOTES7 WHERE " + aColumn + " LIKE ?", ("%" + sys.argv[1] + "%",))
>
I've tried about 20 different variations on this next one. And it finds
0 records no matter what I do. Is there some violation when I use two
qmarks?
>
c.execute ("SELECT Author, Quote, ID, Topics.Topic1, Topic2 FROM
QUOTES7 WHERE ? LIKE ?", (aColumn, "%" + sys.argv[1] + "%"))
>
I'm using mx.ODBC and Python 2.4.3 to connect to an MS Access DB.
>
Thank you,

At a guess; it's probably translating the first '?' (the one after the
WHERE) as a string literal: so your query string is effectively "select
<fieldsfrom <tablewhere 'somestring' like '%<sys.argv[1]>%'".

I would try re-writing it like:
c.execute("sele ct <fcolumnsfrom <tablewhere %s like ?" % aColumn,
"%" + sys.argv[1] + "%")

I don't use mx.ODBC, and definately don't use Access (gagging sounds...
but if you're stuck with it, so be it)...

hth,

Jon.

[Diez B. Roggisch]

Re: curious paramstyle qmark behavior

BartlebyScriven er schrieb:

With
>
aColumn = "Topics.Topic1" '
>
The first statement "works" in the sense that it finds a number of
matching rows.
>
c.execute ("SELECT Author, Quote, ID, Topics.Topic1, Topic2 FROM
QUOTES7 WHERE " + aColumn + " LIKE ?", ("%" + sys.argv[1] + "%",))
>
I've tried about 20 different variations on this next one. And it finds
0 records no matter what I do. Is there some violation when I use two
qmarks?
>
c.execute ("SELECT Author, Quote, ID, Topics.Topic1, Topic2 FROM
QUOTES7 WHERE ? LIKE ?", (aColumn, "%" + sys.argv[1] + "%"))
>
I'm using mx.ODBC and Python 2.4.3 to connect to an MS Access DB.

Parameter passing only works for literal values - not for creating sql
statements.

So, actually your first version is the correct version.

Diez

[BartlebyScrivener]

Re: curious paramstyle qmark behavior

Thanks, Jon.

I'm moving from Access to MySQL. I can query all I want using Python,
but so far haven't found a nifty set of forms (ala Access) for easying
entering of data into MySQL. My Python is still amateur level and I'm
not ready for Tkinkter or gui programming yet.

rd

----------

Jon Clements wrote:

>
I don't use mx.ODBC, and definately don't use Access (gagging sounds...
but if you're stuck with it, so be it)...
>
hth,
>
Jon.

[Jon Clements]

Re: curious paramstyle qmark behavior


BartlebyScriven er wrote:

Thanks, Jon.
>
I'm moving from Access to MySQL. I can query all I want using Python,
but so far haven't found a nifty set of forms (ala Access) for easying
entering of data into MySQL. My Python is still amateur level and I'm
not ready for Tkinkter or gui programming yet.

Not wanting to start a RDMS war, I'd personally choose PostgreSQL over
MySQL. (Quite interestingly, most Python programmers go for PostgreSQL
and most PHP programmers go for MySQL)... However, only you know what
you really want to do, so it's up to you to evaluate which RDMS to go
for!

In terms of data entry; if you're able to extend the idea of GUI a
little, why not use web forms? The django project, although I've only
played with it, was quite nice to set up and get running straight away:
if your load on the data-entry/browsing side isn't too heavy, you can
use the 'development server' instead of installing a full-blown server
such as Apache (I'm not sure if IIS is supported).

Users need not have any specific software (well, apart from a web
browser), you can change the back-end any time, have authentication,
the database and users can be remote to the actual "GUI" etc....

Just some thoughts you can do with as you wish.

Jon.

[BartlebyScrivener]

Re: curious paramstyle qmark behavior


Jon Clements wrote:

However, only you know what
you really want to do, so it's up to you to evaluate which RDMS to go
for!

That assumes a lot :) My needs are simple. I'm exploring. My only real
db is a collection of 5,000 quotations, book passages etc. Flat file
would probably even do it. But I like to learn. Converted to sqlite
with no problem. But I'll try Postgres, just for fun. I guess I was
drawn to MySQL only because it's part of a WordPress site/blog I
operate, and the conversion tools from Access to MySQL were a snap.

In terms of data entry; if you're able to extend the idea of GUI a
little, why not use web forms?

This never occurred to me. Good idea! I'll explore.

if your load on the data-entry/browsing side isn't too heavy, you can
use the 'development server' instead of installing a full-blown server
such as Apache (I'm not sure if IIS is supported).

What's IIS?

Users need not have any specific software (well, apart from a web
browser), you can change the back-end any time, have authentication,
the database and users can be remote to the actual "GUI" etc....
>
Just some thoughts you can do with as you wish.

Thank you, I shall explore.

Rick

[Jon Clements]

Re: curious paramstyle qmark behavior


BartlebyScriven er wrote:

Jon Clements wrote:
>

if your load on the data-entry/browsing side isn't too heavy, you can
use the 'development server' instead of installing a full-blown server
such as Apache (I'm not sure if IIS is supported).

>
What's IIS?

It's Internet Information Services: the MS web/ftp server, that's
standard on some window platforms (Control Panel->Add/Remove
Software->Add/Remove Windows Components - or something like that). I
assumed you were on Windows because of you mentioning Access.

Good luck with your project Rick.

All the best,

Jon.