Newbie: Database technique

Mel Wilson mwilson at the-wire.com
Tue Dec 31 11:47:06 EST 2002


In article <mailman.1040436689.26831.python-list at python.org>,
"Joe 'shmengie' Brown" <joe at overdrivepc.com> wrote:
[ ... ]
>    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?  Moslty I've used
>the tuple 'fields' to count the number of columns selected for insert
>statements...

   There seems to be some line-wrapping trouble, and some
confusion about `+` and `%` as string operators.  I think
this might do what you want (danger -- untested code):


user_fields=("User_Role", "PRN_User", "Name", "Home_Phone", "Office_phone"
        , "Mobile_phone", "Pager", "Fax_No", "Active_user","User_ID")
oc.execute("SELECT %s FROM my_users" % ", ".join(user_fields))

org_fields = ("Organization_ID", "UserID", "Name", "Home_Phone"
            , "Office_Phone", "Mobile_Phone", "Pager", "Fax_No", "Active_User"
            , "User_Role")
def quote (s):
    return '"' + s + '"'
i = 1
while 1:
    rec = oc.fetchone()
    if rec is None:
        break  #my_users
    mc.execute ('INSERT INTO my_users (%s) values (%s,%s)'
                 % (','.join (org_fields)
                   , ','.join ([quote(x) for x in rec[1:9]])
                   , quote(role_id) ))
    print "%5d %-20s %-20s transferred" % (i, rec[1], rec[3])
    id = mc.insert_id()


   You could get rid of the quote function by putting first
and last `"` into the string, and using `'","'.join`.. some
might think that's obscure.


        Regards.        Mel.



More information about the Python-list mailing list