Sprox form with Genshi

Make sure we have these;

pip install sprox
pip install genshi
pip install tw.forms

And we will be able to write a script to produce a form without getting too long.

from sqlalchemy import Column, Integer, String, create_engine, Date, Float, Text
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sprox.formbase import AddRecordForm, FormBase
from sprox.fillerbase import TableFiller
from sprox.tablebase import TableBase

# coding: utf-8

Base = declarative_base()
metadata = Base.metadata


class Customer(Base):
    __tablename__ = 'customer'

    id = Column(Integer, primary_key=True)
    customer = Column(String(6), nullable=False, unique=True)
    name = Column(String(40), nullable=False)
    zip = Column(String(255), nullable=False)
    phone1 = Column(String(31), nullable=False)

class newCustomerForm(FormBase):
    __model__ = Customer

class DBSession1():
    def __init__(self):
        dbhost = "x"
        dbuser = "x"
        dbpass = "x"
        dbdbas = "x"
        self.engine1= create_engine('mysql://' + dbuser + ":" + dbpass + "@" + dbhost + "/" + dbdbas)
        self.Base1 = declarative_base()
        self.Session1 = sessionmaker(bind=self.engine1)
        return None


if __name__ == "__main__":
    w = DBSession1()
    x = newCustomerForm(w.Session1)
    print x()

This should produce html to enter fields that can be the centerpiece of a template or fetched with javascript and placed in a div. We still have not populated the form with data for updating, but this will do for a create. We also have explicitly defined the model, and we really want to do that on the fly.

What is Genshi and why did it pop up?

This might actually be a weakness of the Sprox packages, but Sprox really likes Genshi to make that final push from a Python data structure to HTML code. I call it a weakness, but this final transition is the realm of a template architecture. Popular Python templating specifications include Jinja2, Chameleon(Zope), Mako, etc. The weakness here is that it really likes Genshi, and it is not obvious how to use, for example, Mako instead.

Python SQLAlchemy MySql Columns

Lets switch to SqlAlchemy to get columns

#!/bin/python27

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):
        dbhost = "x"
        dbuser = "x"
        dbpass = "x"
        dbdbas = "x"
        self.engine1= create_engine('mysql://' + dbuser + ":" + dbpass + "@" + dbhost + "/" + dbdbas)
        self.Base1 = declarative_base()
        return None

if __name__=="__main__":

    x = DBSession1()
    customers = Table('customer', x.Base1.metadata, autoload=True, autoload_with=x.engine1)
    print customers.columns

Once again, we get a list of columns.You will need to put your MySql database credential in for this to work, often times the host is localhost.

Here is a loop to look at even more information on the database columns. You can tack this onto the end of the code segment above.

    for col in customers.columns:
        print col.name,col.type,col.nullable,col.primary_key,col.foreign_keys

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.

gmail poplib python 2.7

Gmail works best in SSL mode. Find out the number of emails in your gmail inbox.

  1. Credentials are input from the keyboard, even with an extra example of hiding the password as opposed to placing the credentials in the program (which would be good for crontab scripts)
#!/usr/bin/python
import poplib,getpass

poPserver = poplib.POP3_SSL("pop.gmail.com", 995)
poPuser = raw_input('Username:')
print poPserver.user(poPuser)
print poPserver.pass_(getpass.getpass())
messageCount = len(poPserver.list()[1])
print messageCount, "messages"

shortest poplib example

shortest poplib example python 2.7

short python 2.7 poplib tutorial, python27 poplib library usage lesson, poplib example

Get it at GitHub: https://github.com/weblanta/pytbl-spe

#!/usr/bin/python

from poplib import *

poPserver = POP3('aaa.bbb.ccc.ddd') # ip works better than url
print poPserver.getwelcome() # todo: convert print to debug
print poPserver.user('email@address.com') # todo: chng print to dbg
print poPserver.pass_('EmailPassword') # todo: s/print/debug.validate(
messagesInfo = poPserver.list()[1]
numMessages = len(messagesInfo)

for i in range(numMessages):
  messageIs = poPserver.retr(i+1)[1]
  print "==========================",
  print " message number",i,
  print "=========================="
  for j in messageIs:
    print j
    continue

A longer example puts the login credentials up front and easier to get to here;

#!/usr/bin/python

from poplib import *

popIp='aaa.bbb.ccc.ddd'
popAddr='email@address.com'
popPass='EmailPassword'

popSrvr = POP3(popIp) # ip works better than url
print popSrvr.getwelcome() # todo: convert print to debug
print popSrvr.user(popAddr) # todo: chng print to dbg
print popSrvr.pass_(popPass) # todo: s/print/debug.validate(
messagesInfo = popSrvr.list()[1]
numMessages = len(messagesInfo)

for i in range(numMessages):
  messageIs = popSrvr.retr(i+1)[1]
  print "==========================",
  print " message number",i,
  print "=========================="
  for j in messageIs:
    print j
    continue

and now in this example we split it into a class;

#!/usr/bin/python

from poplib import *

popIp='aaa.bbb.ccc.ddd'
popAddr='email@address.com'
popPass='EmailPassword'

class poppins:

  def __init__(self):
    self.popSrvr = POP3(popIp) # ip works better than url
    print self.popSrvr.getwelcome() # todo: convert print to debug
    print self.popSrvr.user(popAddr) # todo: chng print to dbg
    print self.popSrvr.pass_(popPass) # todo: s/print/debug.validate(
    messagesInfo = self.popSrvr.list()[1]
    self.numMessages = len(messagesInfo)

popJones=poppins()
for i in range(popJones.numMessages):
  messageIs = popJones.popSrvr.retr(i+1)[1]
  print "==========================",
  print " message number",i,
  print "=========================="
  for j in messageIs:
    print j
    continue

finally, we move the credentials and the boiler-plate into a library

# class in the poppins thingamajig
from poplib import *

class poppinsMary:
    def __init__(self):
        popIp='aaa.bbb.ccc.ddd'
        popAddr='email@address.com'
        popPass='EmailPassword'

class poppins:

  def __init__(self):
    x=poppinsMary()
    self.popSrvr = POP3(x.popIp) # ip works better than url
    print self.popSrvr.getwelcome() # todo: convert print to debug
    print self.popSrvr.user(x.popAddr) # todo: chng print to dbg
    print self.popSrvr.pass_(x.popPass) # todo: s/print/debug.validate(
    messagesInfo = self.popSrvr.list()[1]
    self.numMessages = len(messagesInfo)

Which allows us a really short email reading python program here

#!/usr/bin/python

import popCredentials

popJones=popCredentials.poppins()
for i in range(popJones.numMessages):
  messageIs = popJones.popSrvr.retr(i+1)[1]
  print "==========================",
  print " message number",i,
  print "=========================="
  for j in messageIs:
    print j
    continue
 

Python Mysql multi handle 2.7.2

Real world use case for multiple instantiations of python classes, Use a Python Class more than once

At the beginning of our python program, we normally would import the library for MySql operations;

import MySQLdb

Using this library, we then use .connect() and .cursor but because we may be using the same database repeatedly, here is an alternate method of initializing our connection. First, we create a file called credentials.py in our working directory to initialize the database connection;

#!/usr/bin/python
import MySQLdb
def dbconn():
  server = 'localhost'
  user = 'MySql_user_name'
  password = 'MySql_password'
  dbase = 'MySql_database_name'
  return MySQLdb.connect(server,user,password,dbase)

This means that I can quickly connect to the same database, everytime I write a new Python program, substituting the new library for making the connection.

import credentials
handle = credentials.dbconn();

We now can look at some table rows with only four more statements;

cursor=handle.cursor()
sql="SELECT * FROM table_name WHERE 1=1"
cursor.execute(sql)
rowArray=cursor.fetchone()

Add on more statement, like ‘print rowArray’ or do whatever you need to do to look at the row. You probably will write a loop that encloses the last statement.

Just to take another approach, let’s look at our MySql Python connect without the separate credentials function;

#!/usr/bin/python
import MySQLdb
server = 'localhost'
user = 'MySql_user_name'
password = 'MySql_password'
dbase = 'MySql_database_name'
handle = MySQLdb.connect(server,user,password,dbase)

cursor=handle.cursor()
sql="SELECT * FROM table_name WHERE 1=1"
cursor.execute(sql)
rowArray=cursor.fetchone()

print rowArray

And with the credentials library;

#!/usr/bin/python
import credentials
handle=credentials.dbconn()
cursor=handle.cursor()
sql="SELECT * FROM table_name WHERE 1=1"
cursor.execute(sql)
rowArray=cursor.fetchone()

print rowArray

Next an example using your credentials.py to make a python program to read two tables,first we get the user record, and then their posts.

import credentials
userHandle=credentials.dbconn()
uSql="SELECT * FROM jos_users WHERE id=57"
cursor=userHandle.cursor()
cursor.execute(sql)
uArr=cursor.fetchone
contHandle=credentials.dbconn()
cSql="SELECT * FROM jos_content WHERE created_by=57"
cCursor=contHandle.cCursor()
cCursor.execute(cSql)
for i in cCursor.fetchone
  print i['title']

python string split example 2.7.2

6 lines of code, no modules to import

x = 'joe fred terry mitch jane betty mary ellen'
while (1==1):
    y = x.find(" ")
    if y==-1: break
    print 'next name ',x[:y]
    x = x[y+1:]

3 lines of code, using the string module. Elegant because the loop definition is overt and evident.

import string

x = 'joe fred terry mitch jane betty mary ellen'
for name in string.split(x):
  print 'next name ',name

Other Examples

  1. split a semicolon delimitted string, or any delimiter in a string of fields
  2. some string library in python constants
  3. search a string (you don’t need the string library), i.e. ‘pos’ or re.search
import string

x = 'joe;fred;terry;mitch;jane;betty;mary;ellen'
for name in string.split(x,';'):
  print 'next name ',name

import string

print "1..",string.printable
print "2..",string.uppercase
print "3..",string.letters
print "4..",string.digits
print "5..",string.punctuation

x='joe,john'
print x.index('john')
# this yields the integer '4'
print x[4:]
# this would print john, or the rest of the string which is here john