Python Scaffolding SQL tables

Scaffolding support in a framework is a rapid means of implementing crud, i.e. a means of maintaining the database on which we will base our application

Crud is an acronym for Create, read, update and delete, operations that may need to be applied to rows in a table.

Scrudsie could be an acronym adding sorting, selecting, importing and exporting.

If we map out a database for an application, or we are looking to access data that already exists, and have come across dozens or hundreds of table formats, scaffolding is a means of coding crud functions for a table, or even for all tables, in just a few lines of code.

Scaffolding should occur within an Auth framework, especially a utility crud function where any table could have records added, deleted or modified.

Additionally, scaffolding could use a template that defines the entire DOM or identified DIV elements within a DOM (DOM definition wikipedia w3c)

DOM, in this context, stands for Document Object Model, and refers to all the html from the initial doctype definition tag through the final ending tag. Simply “view source” and the chaos of header, body, javascript, meta, link, css, divs, tables, lists and such things is very well known and defined

Python Crud with MySQL

Let’s do the same thing with MySql

pip install MySQL-python
import MySQLdb

class mydbconn():
    def __init__(self):
        self.con = MySQLdb.connect('x', 'x', 'x', 'x')

    def get_columns(self,table):
        cuc = self.con.cursor()
        cursData = cuc.execute("SHOW columns FROM " + table)
        HEADERS = cuc.fetchall()
        keyName = HEADERS[0][0]
        keyLen = self.lenFunction(HEADERS[0][1])
        return keyName, keyLen, HEADERS

    def lenFunction(self,varCharString):
        x = varCharString.find("(")
        varCharString = varCharString[x+1:]
        x = varCharString.find(")")
        varCharString = varCharString[:x]
        return int(varCharString)

if __name__=="__main__":
    con = mydbconn()
    keyName, keyLen, h = con.get_columns("customer")
    for i in h:
        print i

On the fifth line of the program, use your MySql credentials. Sometimes the first parameter is localhost, the second x is the username for the database, the password is third and the name of the database is fourth.

You also have to change the parameter in line 2 of the __main__ section to the name of the table you are using. This routine also has been used with databases that had a key other than named id for the first field, and it was important to have the name of the key and its length to proceed with scaffolding

In the Sqlite3 sqlalchemy example, we got a list of field names. In this example, we get a tuple of tuples. At this point, tuples might be a preferred format, they are immutable and hashable, and a utility crud function would not need to add and remove things from the tuple. However, in a more sophisticated scaffolding environment, you would add things like a field for password verification or especially remove things, like the ability to change certain fields.

Python Crud column names

Python 2.7 python27 Python Scaffolding

Create a directory and go there

virtualenv vedir
source vedir/bin/activate

Let’s get some things

pip install sqlalchemy

Let’s use reflection for models, make a models.py

from sqlalchemy import create_engine, Table
from sqlalchemy.orm import scoped_session,sessionmaker
from sqlalchemy.ext.declarative import declarative_base
import sqlite3

class DBSession1():
    def __init__(self):
        self.engine1= create_engine('sqlite:///adatabase.sqlite3')
        self.Base1 = declarative_base()
        return None


def init_sqlite3(dbname):
    conn = sqlite3.connect(dbname)
    c = conn.cursor()
    c.execute("DROP TABLE IF EXISTS customer")
    c.execute("""
                 CREATE TABLE customer (id INTEGER NOT NULL,
                 customer CHAR(6),
                 name VARCHAR(255),
                 address1 VARCHAR(255),
                 address2 VARCHAR(255),
                 city VARCHAR(255),
                 zip VARCHAR(255),
                 phone1 VARCHAR(255),
                 phone2 VARCHAR(255),
                 PRIMARY KEY(id))""")
    conn.commit()
    c.execute("""
        INSERT INTO customer
        ('customer','name','city','zip')
            VALUES
        ("111111","Alice Cooper", "Decatur", "30030")""")
    conn.commit()
    return conn



if __name__=="__main__":

    init_sqlite3("adatabase.sqlite3")
    x = DBSession1()
    customers = Table('customer',\
            x.Base1.metadata, autoload=True,\
            autoload_with=x.engine1)
    print customers.columns

You can comment and uncomment the init_sqlite3 to create a database, or better yet, execute that function from an init_db.py with import models; models.init_sqlite3(“adatabase.sqlite3”)

Decide how you want to centralize the name of the sqlite3 file for your application.