sqlalchemy整理（三）

来源：互联网发布：如何提高淘宝产品权重编辑：程序博客网时间：2024/06/07 23:22

继续承接一二

之前的例子都是基于sqllite3的，下面的例子是针对mysql的

1.基于配置文件读取数据库配置，其实就是一个ConfigParser读取一个配置文件db.conf

代码：

import ConfigParser#get the connect db conf def read_conf(db_type="mysql"):    conf=ConfigParser.ConfigParser()    conf.read("./db.conf")    sec=db_type    db_user=conf.get(sec,"db_user")    db_password=conf.get(sec,"db_password")    db_host=conf.get(sec,"db_host")    db_port=conf.get(sec,"db_port")    db_name=conf.get(sec,"db_name")    return "{0}://{1}:{2}@{3}:{4}/{5}".format(db_type,db_user,db_password,db_host,db_port,db_name)

配置文件：

[mysql]db_user=hellodb_password=hellodb_host=10.210.71.145db_name=index_testdb_port=3306

2. 加入mapper映射到class ，并且加入session 控制

已经存在的table people，代码中没有创建直接使用了，

sql ，很早之前创建的了，对字段的设计太低级了，莫见笑

mysql> show create table people \G;*************************** 1. row ***************************       Table: peopleCreate Table: CREATE TABLE `people` (  `id` bigint(20) NOT NULL AUTO_INCREMENT,  `zipcode` char(32) NOT NULL DEFAULT '',  `address` varchar(128) NOT NULL DEFAULT '',  `lastname` char(64) NOT NULL DEFAULT '',  `firstname` char(64) NOT NULL DEFAULT '',  `birthdate` char(10) NOT NULL DEFAULT '',  PRIMARY KEY (`id`),  KEY `zipcode` (`zipcode`,`firstname`,`lastname`)) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin11 row in set (0.00 sec)

1.创建引擎
2.matadata和引擎关联
3.metadata和table关联，得到一个table的对象
4.类和table绑定
5.session和引擎关联
以上得到了session和table和类的关联关系建好了

接下来是对table的操作相关

import ConfigParser#get the connect db conf def read_conf(db_type="mysql"):    conf=ConfigParser.ConfigParser()    conf.read("./db.conf")    sec=db_type    db_user=conf.get(sec,"db_user")    db_password=conf.get(sec,"db_password")    db_host=conf.get(sec,"db_host")    db_port=conf.get(sec,"db_port")    db_name=conf.get(sec,"db_name")    return "{0}://{1}:{2}@{3}:{4}/{5}".format(db_type,db_user,db_password,db_host,db_port,db_name)from sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy.orm import  mapper,sessionmakerfrom sqlalchemy import schema,types,selectfrom sqlalchemy import create_engine#class  test oneclass People(object):    pass#get db urldb_url=read_conf()print db_url#create engineengine = create_engine(db_url)engine.echo = Falsemetadata=schema.MetaData(engine)# i have create a table before  so just auto load it people_table =schema.Table('people',metadata, autoload=True)#make mappermapper(People, people_table)#connectconnection = engine.connect()#create session#binf engine with sessionSession=sessionmaker(bind=engine)session=Session()#query with filterpeoples=session.query(People).filter(People.id==2)for people in peoples:    print people.id,people.zipcode,people.address,people.lastname,people.firstname,people.birthdate    people.lastname="hello world"    session.flush()    session.commit()print "############"#query without  any sqlpeoples=session.query(People).filter()for people in peoples:    print people.id,people.zipcode,people.address,people.lastname,people.firstname,people.birthdate#insert with mapper classpeople=People()people.zipcode="010"people.address="hello world"people.lastname="world hello"people.firstname="hello  xluren"people.birthdate="19900606"# make insert ok,we need add flush commit session.add(people)session.flush()session.commit()#query with filterpeoples=session.query(People).filter(People.id>2)for people in peoples:    print people.id,people.zipcode,people.address,people.lastna

0 0