sqlalchemy是python的一款orm框架
sqlalchemy可以链接mysql,sqlite,sqlserver,oracle等等数据库,基本上现在程序开发人员使用的数据库它都可以链接。
操作系统:Ubuntu 12.04 STL
python版本:2.7.5
首先,我们在Ubuntu上搭建sqlalchemy框架,步骤如下:
(一)、安装MySql
sudo apt-get install mysql-server sudo apt-get install mysql-client sudo apt-get install libmysqlclient15-dev
(二)、安装python-mysqldb
sudo apt-get install python-mysqldb
(三)、安装easy_install(如果有了就不用装了)
wget http://peak.telecommunity.com/dist/ez_setup.py python ez_setup.py
(四)、安装MySQL-Python
sudo easy_install MySQL-Python
(五)、安装sqlalchemy
sudo easy_install SQLAlchemy
好了,现在如果打开终端,输入python回车,输入import sqlalchemy如果什么都没有显示,则表示安装成功
接下来,我们看一下sqlalchemy框架中的映射,sqlalchemy一共有三种映射方式:传统映射(classic)、
现代化映射(modern)、自定义映射。
在这里,我只为大家讲一下classic映射和modern映射。
classic映射:
顾名思义,就是类似于hibernate那样,要写好多配置文件,这里放一个小例子:
#coding=utf-8 from sqlalchemy import Table, MetaData, Column, Integer, String, DateTime from datetime import datetime from sqlalchemy.engine import create_engine '''''与数据库建立链接''' engine = create_engine("mysql://username:password@databasehost/databasename",isolation_level="READ UNCOMMITTED") '''''数据库表与对象之间的映射关系,类于hibernate中的xml配置文件''' metadata = MetaData() people = Table( #people 'people', metadata, Column('id', Integer, primary_key=True), Column('name', String(16), unique=True, nullable=False), Column('gender', String(255), unique=True, nullable=True), Column('created', DateTime, default=datetime.now) ) student = Table( #student 'student', metadata, Column('id', Integer, primary_key=True), Column('name', String(16), unique=True, nullable=False), Column('gender', String(255), unique=True, nullable=True), Column('created', DateTime, default=datetime.now) ) teacher = Table( #teacher 'teacher', metadata, Column('id', Integer, primary_key=True), Column('name', String(16), unique=True, nullable=False), Column('gender', String(255), unique=True, nullable=True), Column('created', DateTime, default=datetime.now) ) '''''创建数据库的类''' class People(object):pass class Student(object):pass class Teacher(object):pass '''''传统映射方式''' mapper(People,people) mapper(Student,student) mapper(Teacher,teacher) '''''创建表''' metadata.create_all(engine)
此时,如果我们再写一个*.py文件
代码如下:
from dbproject.dbsetting import People,Teacher,engine from sqlalchemy.orm import sessionmaker people = People() teahcer = Teacher() people.name='renlei' people.id=1 teahcer.name='laoshi' teahcer.id=1 Session = sessionmaker() Session.configure(bind=engine) session = Session() session.add(people) session.add(teahcer) session.flush() session.commit()
编译运行这个python文件,就会发现数据库中的信息发生了相应变化。
modern映射:
如下,先写一个modern.py文件,代码:
#coding=utf-8 from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String from sqlalchemy.engine import create_engine '''与数据库建立链接''' engine = create_engine("mysql://username:password@databasehost/databasename",isolation_level="READ UNCOMMITTED") '''数据库表结构''' Base = declarative_base() class People():#定义一个基类当然也可以不定义,这里只是给个例子 id = Column(Integer, primary_key=True) name = Column(String(16)) gender = Column(String(16)) age = Column(String(16)) class User(Base,People): __tablename__ = 'users' fullname = Column(String(16)) password = Column(String(16)) class AdvancedUser(Base,People): __tablename__ = 'advanceduser' fullname = Column(String(16)) password = Column(String(16)) email = Column(String(16)) class Teacher(Base,People): __tablename__ = 'teacher' phone = Column(String(16)) #创建所有的表 Base.metadata.create_all(engine)
接下来再写一个control.py文件,代码:
#coding=utf-8 from sqlalchemy.orm.session import sessionmaker from modern import engine,User,AdvancedUser,Teacher user = User() user.name = 'username' user.gender = 'male' au = AdvancedUser() au.name = 'auname' au.gender = 'female' teacher = Teacher() teacher.name = 'wangdi' teacher.gender = 'female' teacher.phone = '15264397652' Session = sessionmaker() Session.configure(bind=engine) session = Session() session.add(user) session.add(au) session.add(teacher) session.flush() session.commit()
编译执行代码,会发现数据库内信息有变化,怎么样,与classic映射相比,这种写法是不是更方便啊。
这样,我们就可以省去大量的sql语句,直接使用python代码来完成,是不是很爽啊。
转载请注明:拈花古佛 » python的sqlalchemy框架