Newbie: Database technique

[Gerhard Häring]

Joe 'shmengie' Brown wrote:
> Currently rewriting an aplication using Python and MySql.  The Previous
> incarnation was written in Oracle/Oracle Forms.  Since I'm a newbie to
> Python, I'm writing some kludgy code, and have yet to stumble across a
> cleaner way.
> 
> There is a bunch of tables to convert and am changing some field names in
> the process.  So this kludgy code is appearing in my editor. :o
> 
> fields=("User_Role", "PRN_User", "Name", "Home_Phone", "Office_phone",
> "Mobile_phone", "Pager", "Fax_No", "Active_user","User_ID")
> oc.execute("SELECT " + ( ", ".join(fields)) + " FROM my_users")
> 
> i = 1
> while 1:
>     rec = oc.fetchone()
>     if rec == None: break  #my_users
> 
>     fields = ("Organization_ID", "UserID", "Name", "Home_Phone",
>     "Office_Phone", "Mobile_Phone", "Pager", "Fax_No", "Active_User",
>     "User_Role")
> 
>     mc.execute("INSERT INTO my_users (" + ",".join(fields) + ") values
>     (" + ("""%s,""" * (len(fields)-1)) + """%s)""", [1,] + rec[1:9] +
>     list( role_id) ) print "%5d %-20s %-20s transferred" % (i, rec[1],
>     rec[3]) id=mc.insert_id()
> 
> Suggestions to make this insert operation more readable?

I'll show you how to make it more readable *and* correct :-)

Putting strings yourself in a SQL statement is WRONG because it's
insecure: a malicious user (need not even be malicious, shit happens
and I experienced this particular shit happening once) might enter
arbitrary strings, including nice ones like

    '; DELETE FROM USERTABLE; --

The DB-API allows you to bind parameters without needing to worry
about quoting strings, etc. Suppose you want to create users with name
and age fields, and you have your data in a list, like:

#v+
data = [('Joe', 26), ('Sarah', 21)]

# Then just create your SQL:

sql = "INSERT INTO PERSONS(NAME, AGE) VALUES (%s, %s)"

# and fire it:

for item in data:
    cursor.execute(sql, item[0], item[1]) 

# Ok, so far so good. Now the bright people invented executemany,
# which is easier:

cursor.executemany(sql, data)
#v-

Ok. So you actually have more than two fields. Using the string
substittution operator, this becomes a little more readable for the
SELECT:

#v+
sql = "SELECT %s FROM MYUSERS" % ",".join(fields)
#v-

and for the INSERT:

#v+
sql = "INSERT INTO MYUSERS (%s) VALUES (%s)" % \
   (",".join(fields), ",".join(["%s"] * len(fields)))
#v-

It depends on your database what the parameter placeholder is - I used
%s above, it's what least pyPgSQL and PySQLite use :) I believe
ODBC uses ? for Oracle I normally use :p1, :p2, etc.

Gerhard
-- 
Favourite database:             http://www.postgresql.org/
Favourite programming language: http://www.python.org/
Combine the two:                http://pypgsql.sf.net/
Embedded database for Python:   http://pysqlite.sf.net/