第一步： python 与mysql接口

Windows 下安装：

http://www.codegood.com/downloads

Linux:

sudo aptitude install python-mysqldb -y

第二步：

安装 mysql

Linux :

    sudo aptitude install mysql-client -y
    sudo aptitude install mysql-server -y

Windows:

https://dev.mysql.com/downloads/installer/5.6.html

第三步：

安装 SQLAlchemy

Linux

sudo easy_install SQLAlchemy

Windows:

easy_install SQLAlchemy

第四步：

easy_install uliweb

Linux下在 easy_install 前加 sudo

sqlalchemy + mysql

from sqlalchemy import create_engine
engine = create_engine('mysql://username:password@127.0.0.1/foo')

sqlite

#Unix/Mac - 4 initial slashes in total
engine = create_engine('sqlite:////absolute/path/to/foo.db')
#Windows
engine = create_engine('sqlite:///C:\\path\\to\\foo.db')
#Windows alternative using raw string
engine = create_engine(r'sqlite:///C:\path\to\foo.db')

表和字段

metadata = MetaData()

user = Table('user', metadata,
    Column('user_id', Integer, primary_key = True),
    Column('user_name', String(16), nullable = False),
    Column('email_address', String(60), key='email'),
    Column('password', String(20), nullable = False)
)

user_prefs = Table('user_prefs', metadata,
    Column('pref_id', Integer, primary_key=True),
    Column('user_id', Integer, ForeignKey("user.user_id"), nullable=False),
    Column('pref_name', String(40), nullable=False),
    Column('pref_value', String(100))
)

metadata.create_all(engine)

connection = engine.connect()

插入,更新

stmt = user.insert().values(name="some name")

stmt = user.update().where(user.c.id==5).values(name="some name")

stmt = user.delete(user.c.userid==1)

connection.execute(stmt)

stmt = user.select()
all = connection.execute(stmt)
for a in all:
	print a.user_name

orm

建立 engine

from sqlalchemy import create_engine
engine = create_engine('mysql://username:password@127.0.0.1/foo')

创建 Base

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

创建表

from sqlalchemy import Column, Integer, String
class User(Base):
     __tablename__ = 'users'

     id = Column(Integer, primary_key=True)
     name = Column(String(64))
     fullname = Column(String(64))
     password = Column(String(64))

Base.metadata.create_all(engine) 
#User.create(engine) #建立一个表
#User.drop(engine)   #删除一个表

session

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()

新纪录

ed_user = User(name='ed', fullname='Ed Jones', password='edspassword')

session.add(ed_user)
session.commit()

多条记录

session.add_all([item1, item2, item3])

查询

our_user = session.query(User)

删除

session.query(User).filter(User.id==3).delete()

更新

someobject = session.query(User).get(5)

# set 'value' attribute to a SQL expression adding one
someobject.name = "hello"

# issues "UPDATE some_table SET value=value+1"
session.commit()