Auto-creating MySql and Postgresql Databases with Powerful Python Tools


CherryPyMySQLPostgreSQLPythonThoughts

When designing and development web framework software for clients, I first spend a bit of time focused on the client data. I try to determine the traits of the client data, asking these questions:
Is it temporary?
If so, for how long? Should it expire and disappear automatically or manually?
Should it be archived?
How many users/applications will be entering data?
Is each user’s data mutually exclusive? If not, what is the logical grouping of users and data?
What level of control do users have over their own, or other user/app data?
How is the data used by the clients? Are there “super users”?

The database is such an essential part of the design, and being able to automatically create, archive, purge and delete databases is tremendously handy in so many of these designs.

I wrote this example to generate dynamic databases for short courses, training sessions, and demos. I use the example itself to demonstrate the functionality of CherryPy and SqlAlchemy.

First of all, I start my CherryPy process on my local port 7001, allowing me to do this:

http://grrlcamp.org:7001/dbi/create_my_db?db_type=postgres

or this:

http://grrlcamp.org:7001/dbi/create_my_db?db_type=mysql

First, the small bits of quirkiness in Postgresql and MySql

Starting a Postgresql session on the command line, we see what happens before the URL is referenced:

[cwc2008@localhost user1]$ psql -d template1 -U my_super_user
Welcome to psql 8.2.6, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

template1=# SELECT datname FROM pg_database ;
  datname
-----------
 postgres
 template1
 template0
(3 rows)

template1=#

and after (Note: I had to exit the command line session, run the URL, then re-enter the command line session):

template1=# SELECT datname FROM pg_database ;
   datname
--------------
 postgres
 template1
 template0
 cwcuser_7001
(4 rows)

template1=#

The cwcuser_7001 name is auto-generated from the user’s ID and port number, making it useful for training sessions with many users.

my_super_user was created using the Postgresql createuser command, and, of course, must be a super user to do this operation. The template1 database is the Postgresql master database, from which all other databases in that instance are visible.

Caveat: In Postgresql, only one user can access template1 at any given time. Violating this rule gives you this error:

template1=# create database blahblah;
ERROR:  source database "template1" is being accessed by other users
template1=#

Be sure to close your database descriptor when finished with template1. If this is not a production app, and/or you feel comfortable bouncing the instance, this lock can also be cleared by running:

-bash-3.2$ /usr/local/postgresql/8.2.6/bin/pg_ctl restart -m immediate -D/usr/local/postgresql/8.2.6/data

waiting for server to shut down... done
server stopped
server starting
-bash-3.2$ LOG:  database system was interrupted at 2008-02-14 15:07:47 EST
LOG:  checkpoint record is at 0/4B9054
LOG:  redo record is at 0/4B9054; undo record is at 0/0; shutdown FALSE
LOG:  next transaction ID: 0/2046; next OID: 57344
LOG:  next MultiXactId: 1; next MultiXactOffset: 0
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  record with zero length at 0/4B909C
LOG:  redo is not required
LOG:  database system is ready

This is nasty. In live production environments, do this with great care.

MySql is a bit forgiving when adding, removing or accessing new databases. It does not require the isolation level to be set, as in Postgresql.

We see what databases exist before:

/usr/local/mysql6/bin/mysql --socket=/tmp/mysql10.sock  -u my_super_user -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 170
Server version: 6.0.3-alpha-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

mysql>

and after:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cwcuser_7001       |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql>

MySql does not require my hopping in and out of the command line like Postgresql. It handles user scope much better, in my opinion. It does, however require each new non-super user to be granted access to each database they need, but this is (1) trivial, and (2) outside of the scope of this article.

The SqlAlchemy code to handle the quirky bits

This is invoked by CherryPy, but can be run in any other manner of Python execution. I have a Class called DBInterface, method is called create_engine:

class DBInterface:
    #~~~~~~~~~~~~~~~~~~~~~~~
    def create_engine(self,choice,local_dbname=None):

        if choice == 'super_mysql':

            '''
            Super user MySql commands do not require connection to a database.
            '''
            db = sqlalchemy.create_engine('mysql://%s:%s@%s' % ('my_super_user','some_password','127.0.0.1:3333'),strategy='thr
eadlocal')
            # 'self' is not threadsafe! Don't use it!
            #self.show_databases = "show databases ;"
            cherrypy.session['show_databases'] = "show databases ;"

        elif choice == 'mysql':
            db = sqlalchemy.create_engine('mysql://%s:%s@%s/%s' % ('my_super_user','some_password','127.0.0.1:3333',local_dbnam
e),strategy='threadlocal')

        elif choice == 'super_postgres':
            '''
            Super user Postgresql commands require that you connect to the
            built-in template1 database, and set the isolation level to 0.
            This can't be done in SqlAlchemy, so we do it in Psycopg.
            '''
            DSN_string = "dbname=template1 port=2222 user=my_super_user  host=127.0.0.1 password=some_password"
            def connection():
                self.local_conn = psycopg.connect(DSN_string)
                self.local_conn.set_isolation_level(0)
                return self.local_conn

            db = sqlalchemy.create_engine('postgres://',creator=connection, strategy='threadlocal')
            # NO!
            #self.show_databases = "SELECT datname FROM pg_database ;"
            cherrypy.session['show_databases'] = "SELECT datname FROM pg_database ;"

        elif choice == 'postgres':
            db = sqlalchemy.create_engine('postgres://%s:%s@%s/%s' % ('my_super_user','some_password','127.0.0.1:2222',local_db
name),strategy='threadlocal')

        else:
            db = None

        return db

The exposed method in the same class which invokes the create_engine method, is called create_my_db, which we saw in the URL above (I will address the CherryPy URL functionality after this point):

    @cherrypy.expose
    def create_my_db(self,db_type):

        '''
        Creating a db is a super user act.
        '''
        cherrypy.session['db_type'] = 'super_' + db_type
        cherrypy.session['my_dbname'] = "cwcuser_%s" % (cherrypy.request.config['server.socket_port'])

        db = self.create_engine(cherrypy.session['db_type'])
        db.execute("create database %s ;" % cherrypy.session['my_dbname'])
        databases = db.execute(cherrypy.session['show_databases'])

        dblist = []
        for d in databases:
             dblist.append(d[0])

        print ', '.join(dblist)
        self.dbclose(cherrypy.session['db_type'],databases)
        return ', '.join(dblist)

Because I clump together super user and non-super user database engine connection functionality, I distinguish the difference by modifying the users db type to a “super_” setting in a CherryPy session variable. This way, only one method is called in various internal modes of operation. When in non-super user mode, the SqlAlchemy connect string is the same for both Postgresql and MySql.

No more db quirks in this app: The rest is clean SqlAlchemy

All db operations after this point use the exact same SqlAlchemy code for both MySql and Postgresql. That is a profoundly beautiful thing.

The destroy_my_db method is generic:

    @cherrypy.expose
    def destroy_my_db(self,db_type):

        '''
        Destroying a db is a super user act.
        '''
        cherrypy.session['db_type'] = 'super_' + db_type

        if not cherrypy.session.has_key('my_dbname'):
            cherrypy.session['my_dbname'] = "cwcuser_%s" % (cherrypy.request.config['server.socket_port'])

        db = self.create_engine(cherrypy.session['db_type'])
        db.execute("drop database %s ;" % cherrypy.session['my_dbname'])
        databases = db.execute(cherrypy.session['show_databases'])

        dblist = []
        for d in databases:
             dblist.append(d[0])

        print ', '.join(dblist)
        self.dbclose(cherrypy.session['db_type'],databases)
        return ', '.join(dblist)

As above, all other database operations from this point forward are generic SqlAlchemy code. Yaaay!

The CherryPy layer

My CherryPy main module, which sets up the needed objects and assoiated URLs, looks like this:

import cherrypy
import pdb
import sys,os,re,traceback
import user_data
sys.path.append('../')

#hostname = os.environ.get('HOSTNAME')
#hostname = re.sub('\..*$','',hostname)

# Add your app here.
import db_wrapper

# Only generic headers and footers go here.
# To use generic templates in your app, you may need to symbolicly link
# them into your Templates dir, or references them by absolute path.
TemplatePath = os.path.join(os.path.dirname(__file__), '/cherrypy/dev/templates')

# Add your class here. Note that the 'root' should be the authentication module.

# To test, go to http://grrlcamp.org:1112/run_test
root = db_wrapper.WrapperTest()

# http://grrlcamp.org:port/dbi/create_my_db, destroy_my_db, etc.
root.dbi = db_wrapper.DBInterface()

# http://grrlcamp.org:port/user/get_user_data
root.user = user_data.UserData()

try:
        cherrypy.tree.mount(root)
        cherrypy.config.update(os.path.join(os.path.dirname(__file__),'cp.conf'))
        cherrypy.server.quickstart()
        cherrypy.engine.start()

except SystemExit:
        #smtpInterface.sendSysAdminEmail('cherrypy@xyz.com','sysadmin@xyz.com',"From %s: CherryPy is going down due to shutdown." % hostname,"")
        sys.exit()

except:
        print "ERROR: CherryPy crashed, error:"
        raise

finally:
        #smtpInterface.sendSysAdminEmail('cherrypy@xyz.com','sysadmin@xyz.com',"From %s: CherryPy is going down (from finally clause, check for another error message after this one)." % hostname,"")
        pass

The CherryPy “root” determines what objects are accessible via URL. I mounted a simple test under the root, and nested the above database code under root.dbi, hence the /dbi/ reference in the URL.

root.dbi contains an instance of the DBInterface class we saw earlier. All exposed methods in the DBInterface class become accessible via the /dbi/method_name URL when CherryPy is started.

I’ve attached the complete code used for training, configured to run on localhost, and connect to both MySql and Postgresql daemons. It contains some explicit redundancy and commented code for learning purposes. Your configuration variables will vary slightly. Enjoy.

auto-create-any-db.tar

Reporting from the CWC in New Zealand


Thoughts

It’s an inconvenient time for me to be traveling. I have a new employer patiently waiting for me to start, but who understood the impossibility of my backing out of a trip to New Zealand to speak at a women’s tech conference. I have other contract work piling up, and several contiguous weeks of heads-down work awaiting me when I return. But it is well worth it.

The Computing Women’s Congress is held every other year, and takes place on the beautifully hilly, tropically lush campus of the University of Waikato, in Hamilton. This shoe-optional campus feels laid back and relaxed, but also hosts an outstanding graduate IT program, and Ph.D students and professors from around the world.

The women I’ve met at the CWC are just as impressive. All either have a Ph.D or are studying for one, and they’re sharp, to say the least. I was at first intimidated, being more of a “layman”, with only a BS and much field experience. But the environment is welcoming, and the women running and attending this event are simply great. The academic theory definitely compliments the practice, and I am enjoying it immensely.

In one course, I learned basic C# development in a .Net environment in one day. It’s not something I’d normally want to learn, but the instructors were great, and the presentation was outstanding.

The course I’m teaching is a three day web framework intensive, using Python, CherryPy, CherryTemplate, SqlAlchemy, connecting to both MySql and Postgresql databases. It’s an intensive without the intense part, and the attendees seem to be enjoying themselves. These brilliant women whipped through it faster than I expected, and finished day one 30 minutes early. Last night I hid for several hours in a campus office and wrote more exercises for the course, to keep up with them. They’re really making me work for this, and that is great fun.

This event was mentioned in Computerworld yesterday.

I’ll be posting the code from my course, since it does some unique things most web framework code doesn’t do, and you may find it useful.

DevChix and PHPWomen form an affiliation!


NewsPHPThoughts

A few weeks ago I was fortunate enough to meet Ligaya Turmelle one of the founders of PHPWomen and then last week we meet up for dinner. The conversation flowed so well that 4 hours passed in what seemed to be mere minutes. We had so much in common with how our organizations came about, with all of the hopes and dreams we have for women in software development, and so much more. We decided that our goals were aligned so well that we should create an affiliation between our two organizations in hopes of creating a stronger support network for all women. So I am very pleased to announce that we have formed an alliance. Hopefully this alliance will help both our organizations. We look forward to supporting, helping, and working our sisters over at PHPWomen

Women in Computer Science – An Endangered Species of a New Kind?


Thoughts

This year I have decided to finally go back and take a course at my local college. I already have a Computer Science Degree. I graduated in the spring of 2004. The course that I’m enrolled in, Operating Systems, I have already taken, but now that I’m an Oracle DBA and I am responsible for some Linux System Admin, I figured that retaking the course would be good for me and help refresh my memory on some concepts related to system processes, process scheduling, etc, general stuff really that can be applied to Oracle tuning. So here I am sitting in this 400 level senior course on the first day of class, looking around at my fellow classmates, and feeling grossly out of place. Feeling so, because I am the only woman in the class. Out of 31 students, I am the only woman, and to make it worse, I have already taken this course so I am not really a part of this graduating class! The first time I took the class, I distinctly remember at least 5 chicas out of 30(ish) students. Where are all the women? Did they just take the class a year early? The class is only offered once a year, so that’s even more disconcerting. Hopefully this graduating batch of women were just smart enough to take it a year earlier than what is recommended in the student handbooks. But now I’m starting to think about how my beloved Computer Science department was combined with the Industrial Technology department in 2005. Not that there is anything wrong at all with Industrial Technology, but the two fields are completely different! I am wondering if this departmental change may have scared off wonderfully potential female candidates.

Back in high school, my senior year, our math class was working on proofs. The teacher was assigning specific problems to students and they would work the problem out on the board. This one proof she let 2 students solve it on our dry-erase boards (which we had 2 of). Student 1, male, solves the problem taking up the entire dry-erase board. Student 2, also male, also solves the problem using the entire dry-erase board. I start to panic, because it only took me 3 lines to solve this proof! Did I do it wrong? So I go back and read over it a couple of times, and I just can’t see anything wrong with it. My hand shot up, and the teacher comes over. I showed her my answer, and pointed to the boards asking her, did I miss something? Am I looking at this wrong?? She takes a minute or two to look at it, and reassured me that I had not done anything wrong and that my answer was right. She was very impressed because even she didn’t see the route I took to solving the proof, until she saw my answer. It was elegant and clean. She asked me if I would put it on the board and show the class how I did it, which I was delighted to do (who doesn’t love a little ego rub). So after I wrote my 3 lines on the board, and took a few minutes to explain how I got to the solution, I looked back at my classmates to see if they had any questions, and found myself looking at wrinkled noses, tilted heads and perplexed faces (one guy actually slapped his forehead to acknowledge his realization). Slowly but surely they began to see this simple route to the answer. It is funny, because I remember the feeling of how impossible it seemed to me that out of my entire class, including my teacher, I was the only one that saw the answer this way. But it just was.

Having this memory, perhaps this is why I find it such a bitter pill that I am the only female in my class this semester. Not that I am trying to be an ultra feminist, but how are we supposed to reach simple and graceful solutions when our classes are only being represented by a majority of white males. And I certainly am not saying that white men don’t have simple and graceful solutions, just that diversity plays a very important role in everything. So I am thinking about pulling some resources (my boyfriend has plenty of statistics at his disposal since he is a Business Consultant) and doing some research on my college and the female representation in Computer Science and how that relates to my city and the women in the workforce here. I’d also like to know how these women compare with the male classmates, i.e. top of the class, middle or failing. Although, admittedly, this might be greatly skewed for inappropriate reasons. Which may or may not be discussed, depending on the relativity to this topic as I get further along with my research.

I’d love to hear from the readers as well, what are your thoughts/comments/suggestions? Is there an idea you have on what you would like to see discussed in my research?

HAML Tip


RubyTips and Tricks

So we were having an issue with haml and using a text-area output. It had indentation when it should not have and double indented after doing a save. A quick google search brought me to Ray Morgan’s Blog for the answer. Basically instead of using the = sign use a ~ and it will preserve whitespace. Thanks Ray. I am posting it here so I will remember where to find it if/when I forget what the answer was in the future.