sqlalchemy+postgreSQL（relationship）

 

1、串連資料庫

import os,sysPROJECT_ROOT = os.path.dirname(os.path.realpath(__file__))sys.path.insert(0, os.path.join(PROJECT_ROOT, 'lib'))CONFIG_PATH = os.path.join(PROJECT_ROOT,'config.cfg')from sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_baseimport ConfigParsercf = ConfigParser.ConfigParser()cf.read(CONFIG_PATH)print cf.get("db","db_host")if cf.get("db","db_host"):db_host=cf.get("db","db_host")else:db_host= "127.0.0.1"  //之前用localhost 報錯連結不上資料庫 注意if cf.get("db","db_type"):db_type= cf.get("db","db_type")else:db_type="postgresql+psycopg2" if cf.get("db","db_name"):db_name=cf.get("db","db_name")else:db_name="luoyun"if cf.get("db","db_user"):db_user = cf.get("db","db_user")else:db_user = "luoyun"if cf.get("db","db_password"):db_password = cf.get("db","db_password")else:db_password = "luoyun"str= "%s://%s:%s@%s/%s" % (db_type, db_user,db_password,db_host,db_name)#dbengine = create_engine('postgresql+psycopg2://luoyun:luoyun@127.0.0.1/luoyun',echo=True,client_encoding='utf8')dbengine = create_engine(str,echo=True,client_encoding='utf8')from sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_baseORMBase = declarative_base()from sqlalchemy.orm import sessionmakerSession = sessionmaker(bind=dbengine)dbsession = Session()db = dbsession

2、定義資料表和類之間的映射關係

import lyormfrom sqlalchemy import Column, Integer, String, ForeignKeyfrom sqlalchemy.orm import relationship, backreffrom lyorm import ORMBaseclass User(ORMBase):     __tablename__='users'     id = Column(Integer,primary_key=True)     name = Column(String)     fullname = Column(String)     password = Column(String)     def __init__(self, name, fullname, password):         self.name = name         self.fullname = fullname         self.password = password     def __repr__(self):         return "<User('%s','%s','%s')>" % (self.name,self.fullname,self.password)class Address(ORMBase):      __tablename__= 'address'      id = Column(Integer, primary_key=True)      email_address = Column(String, nullable=False)      user_id = Column(Integer, ForeignKey('users.id'))      user = relationship("User", backref = backref('addresses',order_by=id))      def __init__(self,email_address):          self.email_address = email_address      def __repr__(self):           return "<Address('%s')>" % self.email_address

　　3、設定檔  

[db]db_host = 127.0.0.1db_type = postgresql+psycopg2db_name = luoyundb_user = luoyundb_password = luoyun

　　4、初始化資料庫 python manage.py

import modelsfrom lyorm import ORMBase, dbengine,dbsessionORMBase.metadata.create_all(dbengine)from models import Userdef init():   jack = User('jack','Jack Bean', '801310')   dbsession.add(jack)   dbsession.commit()if __name__=='__main__':  init()