Creating local variables from a dictionary on the fly to do an insert into database

Hi,

I am trying to simplify some of my code, for every html form
that adds
data to the database I was writing a seperate function. The
only
difference between these functions is that the fields are
different. I
am trying to write a function that parses the dictionary of
fields that
are passed as paramenters to the insert function and create
an sql
statement on the fly. The idea is to use this meta function
to process
all inserts. Thus far it looks like this:

# This function typecasts the data.
def makeString (a):
    b=""
    d=0

    if a<>[]:
        for i in a:
            d=d+1
            if i[0:3]=="txt" or
i[0:3]=="rad":
                b=b+i
            elif i[0:3]=="int":
                b=b+"int("+i+")"

            if d<>len(a):
                b=b+","

        return b
    else:
        print "empty list"

# The Web Site
class SampleSite:

    cherrypy.expose
    def addPerson (self,**kwargs):
        L1=[]
        L1=list(kwargs.keys())
        L1.sort()
        strL1=makeString(L1)

        sql="INSERT INTO tblPersons
(dobDay,dobMonth,dobYear,sex,address,city,country,email,land
line,mobile,name,state,web,zip)
VALUES
('%d','%d','%d','%s','%s','%s','%s','%s','%
s','%s','%s','%s','%s','%s');"
+ " %("+ strL1 +")"
        # Upon evaluation the string looks as follows
        #sql="INSERT INTO tblPersons
(dobDay,dobMonth,dobYear,sex,address,city,country,email,land
line,mobile,name,state,web,zip)
VALUES
('%d','%d','%d','%s','%s','%s','%s','%s','%
s','%s','%s','%s','%s','%s');"
%(int(intDobDay),int(intDobMonth),int(intDobYear),radioSex,t
xtAddress,txtCity,txtCountry,txtEmail,txtLandline,txtMobile,
txtName,txtState,txtWeb,txtZip)
        print sql
        db,a=getDb()

        try:

            a.execute(sql)
            print "A new person has been added
successfully!"

        except MySQLdb.Error, e:
            print "Error %d: %s" % (e.args[0],
e.args[1])
            sys.exit (1)

        a.close()
        db.close()


The issue is that when i use **kwargs and all the fields are
stored in
a dictionary they do not exist as local variables. How do I
convert the
items in the dictionary or in the list ( I convert the
dictionary to a
list) to variables?

Does this seem like a valid approach to solving this
problem. Is there
any other approach that anyone can recommend to process
forms at a meta
level rather than hard coding an insert function for each
web form?

Thanks

Arjuna


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "cherrypy-users" group.
To post to this group, send email to cherrypy-usersgooglegroups.com
To unsubscribe from this group, send email to
cherrypy-users-unsubscribegooglegroups.com
For more options, visit this group at http://
groups.google.com/group/cherrypy-users
-~----------~----~----~----~------~----~------~--~---

On Wed, 2006-08-23 at 11:58 +0000, brahmaforces wrote:
> Hi,
> 
> I am trying to simplify some of my code, for every html
form that adds
> data to the database I was writing a seperate function.
The only
> difference between these functions is that the fields
are different. I
> am trying to write a function that parses the
dictionary of fields
> that
> are passed as paramenters to the insert function and
create an sql
> statement on the fly. The idea is to use this meta
function to process
> all inserts.

The cursor execute method will take your database values as
a second
argument.  the first argument is the sql command (a string).
 The db
module will handle the type conversions.  The example code
below is
adapted from my real code.  However, I could have introduce
bugs in
adapting it.  The difference is that my production code gets
the field
list from the database and explicitly sets missing fields to
None.  With
MySQL this offers some minor advantages with regard to
timestamp and
other auto-processed fields.

def insert(curs,tablename,**record):
    dbfields = [key for key in record.keys()]
    values = [record.get(field,None) for field in dbfields]
    places = ','.join(["%s"] * len(dbfields))
    fields = ','.join(dbfields)
    sql_cmd = 'INSERT INTO %s (%s) VALUES (%s)' %
(tablename,fields,places)
    curs.execute(sql_cmd, values)

The place marker of %s will vary between the different DB
modules.  Some
DB modules take a dictionary for the values and use
dictionary keys in
the place markers.


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "cherrypy-users" group.
To post to this group, send email to cherrypy-usersgooglegroups.com
To unsubscribe from this group, send email to
cherrypy-users-unsubscribegooglegroups.com
For more options, visit this group at http://
groups.google.com/group/cherrypy-users
-~----------~----~----~----~------~----~------~--~---

This looks very elegant. I have not seen the for loop used
this
way...nice. Looking at this makes me remember why I chose to
switch
over to python and cherrypy and go through the learning
curve again.
This is amazing.

This looks great, but I am not sure what you mean when you
say that the
the db module will handle the type conversions? I am using
mysqldb, and
have read its documentation and the general python db api
documentation. I have not figured out how/where the type
conversion is
handled by the db module. The %s marker makes everything a
string.
Which is what we need here because we are constructing 2
strings ( 1)
sql_comd, 2) Values) However nowhere are we specifiying the
type of the
values or the type of the fields. How does one invoke/use
the DB module
for mysql to specify the data types of the incoming fields
so that they
are compatible with the corressponding field types in the
mysql
database? 

Thanks... 

Arjuna


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "cherrypy-users" group.
To post to this group, send email to cherrypy-usersgooglegroups.com
To unsubscribe from this group, send email to
cherrypy-users-unsubscribegooglegroups.com
For more options, visit this group at http://
groups.google.com/group/cherrypy-users
-~----------~----~----~----~------~----~------~--~---

I also decided to get the field list from the database. The
code now
looks like this:

def getFields(tablename):
    db,a=getDb()
    sql='Show columns from %s;' % (tablename)
    a.execute(sql)
    b=a.fetchall()
    d=[]
    for c in b:
        d.append(c[0])
    return d

def insert (tablename,record):
    db,cur=getDb()

    dbfields=getFields(tablename)
    values = [record.get(field,None) for field in dbfields]
    places = ','.join(["%s"] * len(dbfields))
    fields = ','.join(dbfields)
    sql_cmd = 'INSERT INTO %s (%s) VALUES (%s)' %
(tablename,fields,places)

    try:

        cur.execute(sql_cmd, values)
        print "A new person has been added
successfully!"

    except MySQLdb.Error, e:

        print "Error %d: %s" % (e.args[0],
e.args[1])
        sys.exit (1)

    cur.close()
    db.close()

class SampleSite:

    cherrypy.expose
    def addPerson (self,**record):
        tablename='tblPersons'
        insert(tablename,record)

There are issues: 1) In retrieving the field list what do we
do with
the ID field which is retrieved. It is set to NONE. Shouldnt
it be
eliminated from the insert statement 2) This code does not
do the
typecasting, everything goes in as a string. How do we
handle other
data types?

Currently this code does  NOT raise an exception and it gets
to the
point where it says that the record has been inserted
successfully!
However when there is no new record in the database. Howcome
there is
no error and no insert?

Arjuna


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "cherrypy-users" group.
To post to this group, send email to cherrypy-usersgooglegroups.com
To unsubscribe from this group, send email to
cherrypy-users-unsubscribegooglegroups.com
For more options, visit this group at http://
groups.google.com/group/cherrypy-users
-~----------~----~----~----~------~----~------~--~---

I have removed the id field from the insert statement

 if c[0][-2:]<>'Id':
            d.append(c[0])

My code evaluates to the following:

INSERT INTO tblPersons
(name,address,city,state,zip,country,mobile,landline,email,w
eb,sex,dobDay,dobMonth,dobYear)
VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
['1', '2', '3', '4', '5', '6', '7', '8',
'9', '10', 'm', '11', '12',
'13']
A new person has been added successfully!

As in the previous post i am using MysqlDB's

cursor.execute(sqlcommand,values)

I get no error yet nothing is inserted into the mysql
database.

Also when you say, "use the DB module to do the
validation", does this
mean that we pass strings to the mysqldatabase using %s as
above and
based on the field type in the table mysqldb automatically
typecasts
the string to the correct datatype when doing the insert?


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "cherrypy-users" group.
To post to this group, send email to cherrypy-usersgooglegroups.com
To unsubscribe from this group, send email to
cherrypy-users-unsubscribegooglegroups.com
For more options, visit this group at http://
groups.google.com/group/cherrypy-users
-~----------~----~----~----~------~----~------~--~---

On Thu, 2006-08-24 at 06:26 +0000, brahmaforces wrote:
> This looks very elegant. I have not seen the for loop
used this
> way...nice. Looking at this makes me remember why I
chose to switch
> over to python and cherrypy and go through the learning
curve again.
> This is amazing.
> 
> This looks great, but I am not sure what you mean when
you say that the
> the db module will handle the type conversions? I am
using mysqldb, and
> have read its documentation and the general python db
api
> documentation. I have not figured out how/where the
type conversion is
> handled by the db module. The %s marker makes
everything a string.
NO!

http://www.p
ython.org/dev/peps/pep-0249/
The DBI 2 spec (look for paramstyle)

Each DB module as a paramstyle.  MySQLdb uses the 'format'
paramstyle
which is
	%s

While it looks like the Python string format specifier, it
is simply a
notation to mark where parameters get slotted in your SQL
statement.
The DB module will do the right thing (barring bugs) in
terms of your
values.  Strings will be quoted.  Special characters will be
escaped.
Dates will be formatted in the way your database expects.

> Which is what we need here because we are constructing
2 strings ( 1)
> sql_comd, 2) Values) However nowhere are we specifiying
the type of the
> values or the type of the fields. How does one
invoke/use the DB module
> for mysql to specify the data types of the incoming
fields so that they
> are compatible with the corressponding field types in
the mysql
> database? 

The DB module knows how to determine the schema types for
the fields in
the table.  In MySQL it presumably uses DESCRIBE TABLE.  It
knows the
type of data you are supplying in the values.  So long as
you are
providing reasonable values, the module will make it work. 
Certainly
the conversions provided in the original code examples are
within the
capabilities of the module.

Unless you have unusual data handling needs, the data
conversions built
into the db modules will reliably put your values into the
database
fields.


> 
> Thanks... 
> 
> Arjuna
> 
> 
> 


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "cherrypy-users" group.
To post to this group, send email to cherrypy-usersgooglegroups.com
To unsubscribe from this group, send email to
cherrypy-users-unsubscribegooglegroups.com
For more options, visit this group at http://
groups.google.com/group/cherrypy-users
-~----------~----~----~----~------~----~------~--~---

On Thu, 2006-08-24 at 09:40 +0000, brahmaforces wrote:
> I also decided to get the field list from the database.
The code now
> looks like this:
> 
> def getFields(tablename):
>     db,a=getDb()
>     sql='Show columns from %s;' % (tablename)
>     a.execute(sql)
>     b=a.fetchall()
>     d=[]
>     for c in b:
>         d.append(c[0])
>     return d
> 
> def insert (tablename,record):
>     db,cur=getDb()
> 
>     dbfields=getFields(tablename)
>     values = [record.get(field,None) for field in
dbfields]
>     places = ','.join(["%s"] *
len(dbfields))
>     fields = ','.join(dbfields)
>     sql_cmd = 'INSERT INTO %s (%s) VALUES (%s)' %
> (tablename,fields,places)
> 
>     try:
> 
>         cur.execute(sql_cmd, values)
>         print "A new person has been added
successfully!"
> 
>     except MySQLdb.Error, e:
> 
>         print "Error %d: %s" % (e.args[0],
e.args[1])
>         sys.exit (1)
> 
>     cur.close()
>     db.close()
> 
> class SampleSite:
> 
>     cherrypy.expose
>     def addPerson (self,**record):
>         tablename='tblPersons'
>         insert(tablename,record)
> 
> There are issues: 1) In retrieving the field list what
do we do with
> the ID field which is retrieved. It is set to NONE.
Shouldnt it be
> eliminated from the insert statement 

For a MySQL auto-increment field either approach works OK. 
Omitting the
field name is an implicit None (NULL).

> 2) This code does not do the
> typecasting, everything goes in as a string. How do we
handle other
> data types?
Covered in earlier reply.

> 
> Currently this code does  NOT raise an exception and it
gets to the
> point where it says that the record has been inserted
successfully!
> However when there is no new record in the database.
Howcome there is
> no error and no insert?

You must be using a newer MySQL that requires COMMIT. 
Essentially, we
have a pending transaction that should be committed or
rolledback
(rollbacked??).

> 
> Arjuna
> 
> 
> 


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "cherrypy-users" group.
To post to this group, send email to cherrypy-usersgooglegroups.com
To unsubscribe from this group, send email to
cherrypy-users-unsubscribegooglegroups.com
For more options, visit this group at http://
groups.google.com/group/cherrypy-users
-~----------~----~----~----~------~----~------~--~---

On Thu, 24 Aug 2006, brahmaforces wrote:

> My code evaluates to the following:
>
> INSERT INTO tblPersons
>
(name,address,city,state,zip,country,mobile,landline,email,w
eb,sex,dobDay,dobMonth,dobYear)
> VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
> ['1', '2', '3', '4', '5', '6', '7',
'8', '9', '10', 'm', '11', '12',
> '13']
> A new person has been added successfully!
>
> As in the previous post i am using MysqlDB's
>
> cursor.execute(sqlcommand,values)
>
> I get no error yet nothing is inserted into the mysql
database.

I ran into the same issue the other day.  In my case I found
that
you can't share a MySQLdb database handle amongst different
threads.
Databaase reads appear to work ok, but writes don't.

This may or may not be your problem.  If you're using
transaction-aware
storage for the table (e.g. InnoDB), then check your
autocommit setting
as it affects whether you need to an explicit db.commit() -
see:
   http://dev.mysql.com/doc/refman/5.0/en/innodb-a
nd-autocommit.html



Cheers,

Chris

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "cherrypy-users" group.
To post to this group, send email to cherrypy-usersgooglegroups.com
To unsubscribe from this group, send email to
cherrypy-users-unsubscribegooglegroups.com
For more options, visit this group at http://
groups.google.com/group/cherrypy-users
-~----------~----~----~----~------~----~------~--~---

Thanks so much Lloyd. Its all working now.Your explanation
of the
typecasting and the fact that %s is a placeholder and does
not specify
a string value in Mysqldb, makes everything clear. This was
a source of
conusion earlier. Also it is great to know that when using
Mysqldb one
has to do NO typecasting as the module does this
automatically.

And as you and Chris said the reason no insert is happening
is because
I am using INNODB transaction aware tables and needed to do
a simple
commit, like cursor.execute("commit")

I now have a working module that picks up the fields from
any form and
inserts them into the corressponding table. 

Thanks....


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "cherrypy-users" group.
To post to this group, send email to cherrypy-usersgooglegroups.com
To unsubscribe from this group, send email to
cherrypy-users-unsubscribegooglegroups.com
For more options, visit this group at http://
groups.google.com/group/cherrypy-users
-~----------~----~----~----~------~----~------~--~---

On Fri, 2006-08-25 at 05:11 +0000, brahmaforces wrote:
> Thanks so much Lloyd. Its all working now.Your
explanation of the
> typecasting and the fact that %s is a placeholder and
does not specify
> a string value in Mysqldb, makes everything clear. This
was a source of
> conusion earlier. Also it is great to know that when
using Mysqldb one
> has to do NO typecasting as the module does this
automatically.

It would be nice of the DB module spec included some example
usage, but
the spec is too loose to make that easy.  You might find the

	python-tutor list 
to be a better place to get help for issues that are
actually outside of
cherrypy.

I'm glad I was able to help.

> 
> And as you and Chris said the reason no insert is
happening is because
> I am using INNODB transaction aware tables and needed
to do a simple
> commit, like cursor.execute("commit")
> 
> I now have a working module that picks up the fields
from any form and
> inserts them into the corressponding table. 
> 
> Thanks....
> 
> 
> 
-- 
Lloyd Kvam
Venix Corp


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "cherrypy-users" group.
To post to this group, send email to cherrypy-usersgooglegroups.com
To unsubscribe from this group, send email to
cherrypy-users-unsubscribegooglegroups.com
For more options, visit this group at http://
groups.google.com/group/cherrypy-users
-~----------~----~----~----~------~----~------~--~---