Intro
Well, I was working on some small application, which was using sqlite databases. And whole problem was I had to create definitions for objects representing the tables. I was looking for some ORM framework for this application. Django gives such framework, and it can be injected in not-django application, but I found sqlalchemy after a little research in the net. And it really rocks. It is pretty easy to configure python script to create appropriate python files with appropriate models from the tables. So, let’s have a closer look.
Initial setup and assumptions
Let’s say we are using sqlite database for our small aplication.
We have to import appropriate modules :
from sqlalchemy import *
from datetime import *
Let’s say our future model objects will reside in directory /home/max/pythonprojects/slowniki/domainobjects and our databases reside in /home/max/pythonprojects/slowniki/dbs. So define this as TARGET_DIRECTORY and SOURCE_DB_DIRECTORY like this :
TARGET_DIRECTORY="/home/max/pythonprojects/slowniki/domainobjects"
SOURCE_DB_DIRECTORY="/home/max/pythonprojects/slowniki/dbs"
Now, let’s define that our databases are : ustawienia.db and edictgerman.db and engine is sqlite. Define this like this :
databasetuples = ('ustawienia.db', 'edictgerman.db')
enginename = "sqlite:///"
Generating "header" for the new files
Well, let’s define now a method for generating "header" of the files, that will be autmatically generated :
def generujKomentarze(nazwapliku, dbname, file) :
d = datetime.now()
commentstuples = (
'# -*- coding: utf-8 -*-',
'#',
'# %s' % nazwapliku,
'#',
'# Plik wygenerowany przy pomocy narzędzia tablereflector.py z modułu utils ',
'#',
'#',
'# Data wygenerowania : %s ' %d ,
'#',
'# Należy sprawdzić, czy któraś z kolumn nie jest Booleanem, w sqlite',
'# Boolean jest zapisywany jako Integer.',
'#',
'# Baza danych z której pochodzi tablica : %s ' % dbname,
'\n'
)
for string in commentstuples :
file.write(string + "\n")
Here we define the comments for the new file, with date of the generation – Data wygenerowania – which we take from datetime.now().
Generating imports for the new files
def generujImporty(file) :
importsTuples = (
'from sqlalchemy.ext.declarative import declarative_base',
'from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey',
'from sqlalchemy.dialects.sqlite import BOOLEAN',
''
)
for string in importsTuples :
file.write(string + "\n")
This are simply needed imports for the objects, which will be instantiated later on.
Generating base class and constructor
def generujKod(tname, columnsArray, file) :
basedeclarative = "Base = declarative_base()"
arrayOfCode = [
'def %s(Base) :' % changeCaseInWord(tname, 0),
' __tablename__ = "%s"' %tname
]
if columnsArray is not None and len(columnsArray) > 0 :
for stringline in columnsArray:
arrayOfCode.append(stringline)
for string in arrayOfCode:
file.write(string + "\n")
Here will be constructor generated. columnsArray are simply strings, which are generated by method generujPojedynczaLinie – generateSimpleLine, this method (generujPojedynczaLinie) is called every time, when we are receiving metadata for the columns from table, this we will se a little bit later.
Now let’s see how the simple line of constructor may be generated :
Generating simple lines
def generujPojedynczaLinie(nazwa, typ, primaryKey = False) :
line = " %s = Column(%s" % (nazwa, typ)
line = line.replace("TEXT", "String")
line = line.replace("INTEGER", "Integer")
if primaryKey is True :
line = line + ", primary_key=True)"
else :
line = line +")"
return line
Now, let’s take a while in here. Sqlite has a few data types. We use simply use TEXT and INTEGER, but, for the python we have to change TEXT into String and INTEGER into Integer. That’s why there is a replace call. Additionally, there is also a check, if the column is primary key. By default it is False, but if this gonna be primary key, it is added to appropriate property definition.
Generating init method
def generujMetodeInit(columnsNames, file) :
initDefinitionArray=[]
line00 = " "
initDefinitionArray.append(line00)
lineInit = " def __init__(self"
if columnsNames is not None and len(columnsNames) > 0 :
for string in columnsNames :
lineInit = lineInit + ", %s" % string
lineInit = lineInit + "):"
initDefinitionArray.append(lineInit)
if columnsNames is not None and len(columnsNames) > 0 :
for string in columnsNames :
initDefinitionArray.append(" self.%s = %s" % (string, string))
for string in initDefinitionArray:
file.write(string + "\n")
This method goes through columnsNames and generates init method. Here are appropriate lines added (generated earlier with generujPojedynczaLinie method (see above, previous section).
Generate "__repr__" method
def generujMetodeRepr(tname, columnsNames, file):
reprDefinitionArray = []
line00 = " "
reprDefinitionArray.append(line00)
lineInit = " def __repr__(self):"
reprDefinitionArray.append(lineInit)
lineEnd = " return \"<%s(" % changeCaseInWord(tname, 0)
if columnsNames is not None and len(columnsNames) > 0:
for string in columnsNames :
if columnsNames.index(string) < len(columnsNames) - 1:
lineEnd = lineEnd + "'%s',"
else :
lineEnd = lineEnd + "'%s'"
lineEnd = lineEnd + ")>\" % ("
if columnsNames is not None and len(columnsNames) > 0:
for string in columnsNames :
if columnsNames.index(string) < len(columnsNames) - 1 :
lineEnd = lineEnd + "self.%s, " %string
else :
lineEnd = lineEnd + "self.%s" %string
lineEnd += ")"
reprDefinitionArray.append(lineEnd)
for string in reprDefinitionArray :
file.write(string + "\n")
This method gives us the repr method which is called to represent our new object. It can be used in place of str method. But if it is your wish, you can add str method too. It simply returns a string, with the fields of the object.
Defining helper method for changing one letter in the word to upper or lower case
'''
Zmienia literkę pod podanym numerem na dużą, lub małą
w zależności od flagi toUpper.
Reszta literek w słowie zostaje bez zmiany,
domyślnie toUpper ustawiony jest na True
'''
def changeCaseInWord(s, numerliterki, toUpper=True):
if numerliterki == 0:
if toUpper == True:
return s.upper()[0] + s[1:]
else :
return s.lower()[0] + s[1:]
elif numerliterki <= len(s) :
if toUpper is True :
return s[:numerliterki] + s.upper()[numerliterki] + s[numerliterki+1:]
else :
return s[:numerliterki] + s.lower()[numerliterki] + s[numerliterki+1:]
else :
return "Numer literki jest poza słowem..."
This method is used to change first letter of the table (during creation of the base class for the table) to upper case, but it can be used to change any selected letter in a word to upper or lower case.
Finally the main method of the generation script
if __name__ == "__main__" :
for db in databasetuples :
nazwa_enginea = enginename + SOURCE_DB_DIRECTORY + "/" + db
engine = create_engine(nazwa_enginea)
meta = MetaData()
meta.bind = engine
meta.reflect()
for tname in meta.tables:
file = open(TARGET_DIRECTORY + "/%s.py" % tname, "w")
T = meta.tables[tname]
generujKomentarze(tname + ".py", db, file)
generujImporty(file)
columnsArray = []
columnsNames = []
for c in T.columns :
columnsArray.append(generujPojedynczaLinie(c.name, c.type, c.primary_key))
columnsNames.append(c.name)
generujKod(tname, columnsArray, file)
generujMetodeInit(columnsNames, file)
generujMetodeRepr(tname, columnsNames, file)
file.close()
Here we initialise everything and run the script, which creates our files. Isn’t it easy? The result of running so constructed script is a folder with generated models (every model has it’s own file).
