Another MySQL Problem

[Victor Subervi]

On Thu, Jun 24, 2010 at 9:34 AM, Stephen Hansen <me+list/python at ixokai.io>wrote:

> 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.
>

Obvious in retrospect. Thanks for cluing me in :))
beno
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-list/attachments/20100624/a18cbd39/attachment-0001.html>