Automatic database schema builder for existing databases with Peewee

peewee-orm

There are a lot of methods for connecting to your databases from your application. And each has there advantages. If you use any ORM for your projects in python you have heard of Sqlalchemy its a standard goto, but some times its too “big” or complicated. Wouldn’t it be nice to have a small lightweight ORM module that fit the bill? Enter peewee ORM.

peewee

Peewee is a light weight ORM that is very similar to sqlalchemy but with an easy learning curve, lightweight (small install) and an expressive ORM. It is also compatible with python 2.7 and 3. Database support is limited to Postgres, MySQL/MariaDB, and Sqlite3. This is usually enough to handle most users needs.

Choosing where to use it

Peewee is really well suited for small micro applications, and quick prototyping and database access needs.  But its not limited to that. Check out the github link for several examples by the author.

The syntax is very similar to sqlalchemy, but for myself, I think is very more natural to use.

One of the things I love the most about peewee is it is very easy to run a script against an existing database, and have a schema mapper built. Something that has been really difficult with sqlalchemy.

Connecting to existing databases

databases

The magic command is documented here, but often over looked. In this case I needed the database schema for FreePBX. So I installed the peewee package and ran the following command.

pwiz.py -e mysql asterisk > freepbx_schema.py

I now have a prebuilt module that I only need to import into my project to use. That simple command saved having to create over 4000 lines of code by hand! You can grab a copy of the file results here for the lazy in us all.

CRUD

The basic CRUD can not be handled in a very pythonic way with a rather expressive expression engine. Be sure to visit the docs page http://docs.peewee-orm.com/en/latest/index.html for the most current documentation.

Retrieving a list of sip users from the FreePBX database is now as simple as:

sip_users = Sip().select()

or to only show a specific user

sip_user = Sip.get(Sip.id=='1000')

Conclusion

Peewee ORM makes it quick to access existing database by generating a usable ORM model to import into your application. It works with popular databases used for web current applications, and is extendable to other databases as well. So the next time you need to prototype or build a new micro service, give peewee a try for quick database access.

Leave a Reply