curious paramstyle qmark behavior

B

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
 
J

Jon Clements

BartlebyScrivener said:
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
<fields> from <table> where 'somestring' like '%<sys.argv[1]>%'".

I would try re-writing it like:
c.execute("select <fcolumns> from <table> where %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.
 
D

Diez B. Roggisch

BartlebyScrivener said:
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
 
B

BartlebyScrivener

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
 
D

Dennis Lee Bieber

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.
You've fallen a foul of M$ hype <G>

Access is not, per se, an RDBM. The "native" database engine (the
one used by .mdb files) is called JET. Access, itself, is just a GUI
form and report designer that contains widgets capable of direct
interfacing with a backend database engine, and has scripting language
(VBA).

The later versions of Access even support "Access Data Projects" in
which only the form/report definitions are in the .mdb file -- the data
itself is handled via SQL Server (possible seen as MSDE -> M$ Data
Engine or M$ SQL-Server Desktop Edition depending on what document one
reads). It is even possible to link in, using ODBC, MySQL or other
database engines.

You will not find a drag&drop form designer with automatic database
linkage in Python. I believe there is a drag&drop form designer for
wxPython, but you will still have to code the linkage between the fields
and the database.
--
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/
 
J

Jon Clements

BartlebyScrivener said:
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.
 
B

BartlebyScrivener

Jon said:
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
 
J

Jon Clements

BartlebyScrivener said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,901
Latest member
Noble71S45

Latest Threads

Top