SqlAlchemy crud scaffold model from MySql Record Layout
from sqlalchemy.ext.automap import automap_base Base.prepare(engine, reflect=True) Customer = Base.classes.customer session = Session(engine) print (customer_collection)
from sqlalchemy.ext.automap import automap_base Base.prepare(engine, reflect=True) Customer = Base.classes.customer session = Session(engine) print (customer_collection)
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.
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