sqlalchemy+postgreSQL（relationship）

 

1、连接数据库

import os,sys
PROJECT_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_engine
from sqlalchemy.ext.declarative import declarative_base
import ConfigParser


cf = 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_engine
from sqlalchemy.ext.declarative import declarative_base

ORMBase = declarative_base()

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=dbengine)

dbsession = Session()
db = dbsession

2、定义数据表和类之间的映射关系

import lyorm
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship, backref
from lyorm import ORMBase

class 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.1
db_type = postgresql+psycopg2
db_name = luoyun
db_user = luoyun
db_password = luoyun

　　4、初始化数据库 python manage.py

import models
from lyorm import ORMBase, dbengine,dbsession
ORMBase.metadata.create_all(dbengine)
from models import User

def init():

   jack = User('jack','Jack Bean', '801310')
   dbsession.add(jack)
   dbsession.commit()
if __name__=='__main__':
  init()