python 操作数据库 ORM

'''

对象关系映射（英语：(Object Relational Mapping，简称ORM，或O/RM，或O/R mapping），是一种程序技术，用于实现面向对象编程语言里不同类型系统的数据之间的转换  。
从效果上说，它其实是创建了一个可在编程语言里使用的--“虚拟对象数据库”。
'''

import  sqlalchemy
from sqlalchemy import  create_engine
from  sqlalchemy.ext.declarative import  declarative_base
from  sqlalchemy import  Column,Integer,String
from  sqlalchemy.orm import  sessionmaker

from sqlalchemy import func
#指定连接信息
#可以使用的连接类型  MySQL-Python,pymysql , MySQL-Connector,cx_Oracle
engine= create_engine('mysql+pymysql://root:123@localhost/mydb',encoding='utf-8',echo=True)

#生成ORM基类
Base=declarative_base()

class User(Base):
        __tablename__='user2' #表名

        #声明列
        id = Column(Integer,primary_key=True,autoincrement=True)
        name=Column(String(32))
        password = Column(String(64))

        def __repr__(self):
                return "<%s name : %s>"%(self.id ,self.name)


class Student(Base):
        __tablename__ = 'student'  # 表名

        # 声明列
        id = Column(Integer, primary_key=True, autoincrement=True)
        name = Column(String(32))


        def __repr__(self):
                return "<%s name : %s>" % (self.id, self.name)

#创建表结构
Base.metadata.create_all(engine)



'''与数据库交互'''
#创建与数据库连接的会话 , 这里返回的是一个类
Session_class = sessionmaker(bind=engine)

#生成 session 实例
Session = Session_class()

#
# user_obj = User(name='xiaowan',password='123456')
# print(user_obj.name,user_obj.id)
#
# #将数据添加到session 中
# Session.add(user_obj)

#查询,返回一个对象
#data = Session.query(User).filter(id>2).filter(id<5).all()
data = Session.query(User).filter_by(id='1').first()
print(data)

#修改
# data.name='xiaoxiao'


#统计数量
print(Session.query(User).filter(User.name.like("x%")).count() )

#分组统计
print(Session.query(func.count(User.name),User.name).group_by(User.name).all())

#回滚
# Session.rollback()

#连接查询
print(Session.query(User,Student).filter(User.id ==Student.id).all())

#提交
Session.commit()

 

添加外键：

'''
外键
'''
import  sqlalchemy
from sqlalchemy import  create_engine
from  sqlalchemy.ext.declarative import  declarative_base
from  sqlalchemy import  Column,Integer,String,ForeignKey
from  sqlalchemy.orm import  sessionmaker,relationship

from sqlalchemy import func
#指定连接信息
#可以使用的连接类型  MySQL-Python,pymysql , MySQL-Connector,cx_Oracle
engine= create_engine('mysql+pymysql://root:123@localhost/mydb',encoding='utf-8',echo=True)

#生成ORM基类
Base=declarative_base()


class Student(Base):
        __tablename__ = 'student'  # 表名

        # 声明列
        id = Column(Integer, primary_key=True, autoincrement=True)
        name = Column(String(32))


        def __repr__(self):
                return "<%s name : %s>" % (self.id, self.name)

class StudyRecord(Base):
    __tablename__ = 'Study_record'
    id = Column(Integer, primary_key=True, autoincrement=True)
    day = Column(Integer,nullable=False)
    statrus = Column(String(32),nullable=False)
    #外键关联student id
    stu_id = Column(Integer,ForeignKey("student.id"))

    #添加一个关系 ,让StudyRecord可以调用student
    student = relationship("Student", backref="my_study_record")

    def __repr__(self):
        return "<%s day:%s  status:%s>" % (self.student.name,self.day,self.statrus)

#生成表结构
Base.metadata.create_all(engine)

#创建与数据库连接的会话 , 这里返回的是一个类
Session_class = sessionmaker(bind=engine)

#生成 session 实例
session = Session_class()

# s1 = Student(name='aaa')
# s2 = Student(name='bbb')
# s3 = Student(name='ccc')
# s4 = Student(name='ddd')
#
# obj1 = StudyRecord(day=1,statrus="Y",stu_id=1)
# obj2 = StudyRecord(day=2,statrus="Y",stu_id=1)
# obj3 = StudyRecord(day=3,statrus="Y",stu_id=1)
# obj4 = StudyRecord(day=1,statrus="Y",stu_id=2)



# 查询
stu_obj = session.query(Student).filter(Student.name=="aaa").first()

print("---------------------")
print(stu_obj.my_study_record)

# session.add_all([s1,s2,s3,s4,obj1,obj2,obj3,obj4])

# session.commit()

 

多外键：

from  sqlalchemy import Integer,ForeignKey,String,Column
from  sqlalchemy.ext.declarative import  declarative_base
from  sqlalchemy.orm import relationship,sessionmaker
from sqlalchemy import  create_engine,ForeignKey

Base = declarative_base()

class Customer(Base):
    __tablename__='customer'
    id = Column(Integer,primary_key=True)
    name= Column(String(64))

#外键
    billing_address_id = Column(Integer,ForeignKey("address.id"))
    shipping_address_id = Column(Integer,ForeignKey("address.id"))

    billing_address = relationship("Address",foreign_keys=[billing_address_id])
    shipping_address = relationship("Address",foreign_keys=[shipping_address_id])


class Address(Base):
    __tablename__='address'
    id = Column(Integer,primary_key=True)
    street = Column(String(64))
    city = Column(String(64))
    state = Column(String(64))

    def __repr__(self):
        return  self.street


engine= create_engine('mysql+pymysql://root:123@localhost/mydb',encoding='utf-8',echo=True)
Base.metadata.create_all(engine)

#创建与数据库连接的会话 , 这里返回的是一个类
Session_class = sessionmaker(bind=engine)

#生成 session 实例
session = Session_class()


# #插入数据
#
# addr1 = Address(street="FT",city="fengtai",state="beijing")
# addr2 = Address(street="CY",city="chaoyang",state="beijing")
# addr3 = Address(street="PD",city="pudong",state="shanghai")
#
# session.add_all([addr1,addr2,addr3])
#
# c1 = Customer(name="xiaoqiang",billing_address=addr1,shipping_address=addr2)
# c2 = Customer(name="jack",billing_address=addr3,shipping_address=addr3)
#
# session.add_all([c1,c2])





#查询
obj = session.query(Customer).filter(Customer.name=="xiaoqiang").first()
print(obj.name,obj.billing_address,obj.shipping_address)

session.commit()

 

 

 

多对多关系：

from  sqlalchemy import Integer,ForeignKey,String,Column,Table
from  sqlalchemy.ext.declarative import  declarative_base
from  sqlalchemy.orm import relationship,sessionmaker
from sqlalchemy import  create_engine,ForeignKey

Base = declarative_base()

book_m2m_author = Table('book_m2m_author',Base.metadata,
                        Column('book_id',Integer,ForeignKey('book.id')),
                        Column('author_id',Integer,ForeignKey('author.id'))
                        )
#book table
class Book(Base):
    __tablename__='book'
    id =  Column(Integer,primary_key=True)
    name = Column(String(64))

    authors = relationship('Author',secondary=book_m2m_author ,backref='books')

    def __repr__(self):
        return  self.name

#Author table
class Author(Base):
    __tablename__ = 'author'
    id = Column(Integer,primary_key=True)
    name= Column(String(64))

    def __repr__(self):
        return  self.name



engine= create_engine('mysql+pymysql://root:123@localhost/mydb?charset=utf8',encoding='utf-8',echo=True)
Base.metadata.create_all(engine)


#创建与数据库连接的会话 , 这里返回的是一个类
Session_class = sessionmaker(bind=engine)

#生成 session 实例
session = Session_class()
#
# #插入数据
# b1= Book(name='java')
# b2= Book(name='c#')
# b3= Book(name='python')
b3 = Book(name='语文')
#
#
# a1= Author(name='jack')
# a2= Author(name='raun')
# a3= Author(name='tran')
#
#
#
# b1.authors=[a1,a3]
# b2.authors=[a2,a3]
# b3.authors=[a1,a2,a3]
#
#
# session.add_all([b1,b2,b3,a1,a2,a3])
session.add_all([b3])


#查询jack 的书
author_obj = session.query(Author).filter(Author.name=='jack').first()
print("------------------------")
print(author_obj.books)


#java 书的作者
book_obj = session.query(Book).filter(Book.name=='java').first()
print("------------------------")
print(book_obj.authors)


session.commit()