Another MySQL Problem

[Stephen Hansen]

On Thu, Jun 24, 2010 at 4:47 AM, Victor Subervi <victorsubervi at gmail.com>wrote:

> On Thu, Jun 24, 2010 at 1:56 AM, John Nagle <nagle at animats.com> wrote:
>
    Yes.  Please post your CREATE statements, so we can see your
>> database schema.  If you really have one table per client, you're
>> doing it wrong.
>>
>
> cursor.execute('create table if not exists clients ('client varchar(100),
> clientAppelation varchar(10), clientFirst varchar(20), clientLast
> varchar(40), clientEmail varchar(60), pw varchar(10)')
>

That's only a partial picture: your code later indicated that you have
tables which are named for client. Like, "Lincoln_Properties", or some such.
That's the problem: you have data which belongs in a field (Lincoln) in the
table name.

Basically, instead of:

Lincoln_Properties
Memphis_Properties
Angel_Properties

And such.

You should have a single "Properties" table, which is identical to the
schema of the above: but with an additional field, "client", which is either
"Lincoln" or "Memphis" or whatnot. You may then need to tweak your primary
keys a bit.

Then instead of "cur.execute('select * from %s', client)" which errored out
-- because you can't use parameterized queries for schema objects (tables,
fields, etc) -- you would simply change it to "cur.execute('select * from
properties where client = %s', (client,))" -- and it'd work just the same.

--Stephen
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-list/attachments/20100624/abc10f1e/attachment-0001.html>