第一步： python 與mysql接口 Windows 下安裝： [http://www.codegood.com/downloads](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](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() ~~~