In bash, getting a web page

curl http://web-atlanta.com/ >html.html

The bash curl command has nothing to do with Canadian Curling, which is like shuffleboard on ice amidst the tension garnered tolerating a joke about the Queen or the national health care system from a surfboard riding processed cheese eating upper Mexican from south of the barbarian line. In bash, curl is short for client url, a simple http request of a web address.

In Python

x = urllib2.urlopen("http://web-atlanta.com")
w = x.read()

And as a part of a class init

  def __init__(self,TableName,filename,url):
    self.sqhandle=sqlite3.connect(filename) # empty if noexist
    self.sq1cursor = self.sqhandle.cursor()
    self.filename = filename
    try:
      self.sq1cursor.execute('''CREATE TABLE '''
         +TableName+''' (somenum real, somestring text)''')
      self.sqhandle.commit()
    except:
      print TableName,"table already exists"
    self.samples = 0
    x = urllib2.urlopen(url)
    self.pagedata = x.read()

So if we instantiate a class we now have the html as classname.pagedata and sqlite3 handle and cursor as classname.sqhandle and sq1cursor

We need to do some things with sql like

    command = 'INSERT INTO '+table+' VALUES (' + x +', "'+ y +'")'
    try:
      classname.sq1cursor.execute(command)
      classname.sqhandle.commit()
    except:
      print command

sqlite3 insert a row

  def sqlinsert(sqhandle,sq1cursor,table,x,y):
    command = 'INSERT INTO '+table+' VALUES (' + x +', "'+ y +'")'
    try:
      sq1cursor.execute(command)
      sqhandle.commit()
    except:
      print "failed to ", command

Perl with DBI, Perl Database access

#!/usr/bin/perl -w
#
#script to add up some table data

use DBI;
use CGI::Carp qw(fatalsToBrowser);

print “Content-type: text/html\n\n”;
my $dbh = DBI->connect(’dbi:mysql:database=DATABASE_NAME;host=localhost’, ‘USERNAME’, ‘PASSWORD’)
or die “Couldn’t connect to database: ” . DBI->errstr;

my ( $k, $id, $book_date, $svc_type, $cust_id, $time_pref, $a_sched,$a_eight);
my $t_date=”20080115″;

$sqlquery = qq( SELECT id, book_date, svc_type, cust_id, time_pref, FROM `orders` WHERE  `move_date` = $t_date );
$sth = $dbh->prepare ( $sqlquery );
$sth->execute() || die “Couldn’t execute query: ” . DBI->errstr;

$k = 0;
$sth->bind_columns( \$id, \$book_date, \$svc_type, \$cust_id, \$move_date, \$time_pref, \$est_men, \$est_trks );
while ( $sth->fetch() ) {
$a_sched  += 1;
if ( $time_pref eq “8-9A.M.” ) { $a_eight  +=1; }
}

#Now $a_sched contains the number of records $a_eight contains the number of 8-9A.M. record for that date.

atlanta custom database design   vehicle dispatch ERP

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