Something More Elegant

[Victor Subervi]

On Sun, Jan 17, 2010 at 5:36 PM, Dennis Lee Bieber <wlfraed at ix.netcom.com>wrote:

>        I don't think that would be efficient, considering that "the above"
> entails what would be something like three or four courses all by
> themselves (Database Analysis, Database Design, SQL [these three are
> independent of any particular implementation language, though Design and
> SQL may be impacted by the database engine one is using -- for example,
> foreign key integrity & cascading deletes are not enforced by MySQL
> MyISAM tables, but can be enforced using InnoDB tables; SQLite doesn't
> have user access levels, but all client/server engines do, etc.), and
> then a course on Python with an emphasis on Web applications [since I
> recall you are trying to generate HTML at some stage]) the only example
> I'd be able to give would require me writing a complete application --
> the last time I supplied a large snippet of pseudo-code it took over a
> month of daily questions and replies to fix your attempts to modify it;
> most problems coming down to not understanding the algorithm, how to use
> parameterized queries, etc.
>

LOL! Yeah... <:-}

>
>        The shortest hints I can demonstrate -- using a text console for
> input, AND in pseudo-code (I've not ensured this will run) would be:
>
> DON'T:
> tname = raw_input("Enter the name of the table to be searched: ")
> fname = raw_input("Enter the name of the field in %s to be searched: "
>                                        % tname)
> fvalue = raw_input("Enter the value of %s defining the desired data: "
>                                        % fname)
>
> SQL = """select * from %s
>                        where %s like "%%%s%%"""" % (tname, fname, fvalue)
> crs.execute(SQL)
>
>
> DO
> tables = [      "Table1",
>                        "Table2",
>                        "Table3"        ]
> fields = {      "Table1" :      [       "t1field1",
>                                                        "t1field2",
>                                                        "t1field3"      ],
>                        "Table2" :      [       "t2field1",
>                                                        "t2field2",
>                                                        "t2field3"      ],
>                        "Table3" :      [       "t3field1",
>                                                        "t3field2",
>                                                        "t3field3"      ]
>     }
>
> for i, t in enumerate(tables):
>        print "%s\t%s" % (i, t)
> tnum = int(raw_input(
>                "Enter the number of the table to be searched: "))
> tname = tables[tnum]
>
> for i, f in enumerate(fields[tname]):
>        print "%s\t%s" % (i, f)
> fnum = int(raw_input(
>                "Enter the name of the field in %s to be searched: "
>                % tname)
> fname = fields[tname][fnum]
>
> fvalue = raw_input("Enter the value of %s defining the desired data: "
>                                        % fname)
>
> SQL = """select * from %s
>                        where %s like %%s""" % (tname, fname)
> crs.execute(SQL, ("%"+fvalue+"%",))
>
>        In a real application, one would use the ability of the database
> engine itself to retrieve the list of table names, and the fields for
> each table -- that way the code would not need to be changed if the
> database has additions or deletions of tables/fields.
>
>        Also note that I did NOT include exception handling -- all those
> raw_input() in the "DO" version should have some exception handling (the
> first two might not be given numeric data so the int() fails, or the
> integer supplied may not be in the range of indices for the list
> look-ups; all three might get an EOF entry by the user trying to abort
> the look-up.
>
>        Fancier versions would build multi-table joins, or allow for AND (or
> OR) clauses in the WHERE, using a list of table.field/value pairs.
>

Thanks. I think I followed that :/
beno
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-list/attachments/20100120/58483f4c/attachment-0001.html>