Python 9 sqlalchemy ORM

-Python mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname> pymysql mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>] MySQL-Connector mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname> cx_Oracle oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...] 更多详见：http://docs.sqlalchemy.org/en/latest/dialects/index.html

 三、sqlalchemy基本使用

创建表：

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

engine = create_engine(‘mysql+pymysql://cheng:123456@192.168.71.3/test_cheng‘,
                       encoding = ‘utf-8‘,echo=True)

Base = declarative_base()  #生成基类

class User(Base):
    __tablename__ = ‘user‘  #表名
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    password = Column(String(64))

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

插入数据：

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

engine = create_engine(‘mysql+pymysql://cheng:123456@192.168.71.3/test_cheng‘,
                       encoding = ‘utf-8‘,echo=True)

Base = declarative_base()

class User(Base):
    __tablename__ = ‘user‘
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    password = Column(String(64))

Session_class = sessionmaker(bind=engine)
Session = Session_class()
for i in range(10):
    user_obj = User(name=‘cheng%s‘%i,password=‘%s*321‘%i)
    Session.add(user_obj)
    
Session.commit()

查询和修改数据：

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

engine = create_engine(‘mysql+pymysql://cheng:123456@192.168.71.3/test_cheng‘,
                       encoding = ‘utf-8‘)

Base = declarative_base()

class User(Base):
    __tablename__ = ‘user‘
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    password = Column(String(64))

Session_class = sessionmaker(bind=engine)
Session = Session_class()
#1.first生成一个实例，而all生成一个实例列表：
my_user = Session.query(User).filter_by(name=‘cheng77‘).first()
print(my_user.id,my_user.name,my_user.password)

my_user = Session.query(User).filter_by(name = ‘cheng78‘).all()
for i in my_user:
    print(i.id,i.name,i.password)

#2.filter_by不支持<,>,==,而filter支持<,>,==而且filter支持多条件查询：
my_user = Session.query(User).filter(User.id < 29).filter(User.id > 13).all()
for i in my_user:
    print(i.id,i.name,i.password)

#3.修改数据：
my_user = Session.query(User).filter_by(name=‘cheng77‘).first()
my_user.name = ‘cheng77777‘
Session.commit()
print(my_user.id,my_user.name,my_user.password)

#4.回滚：
<----插入数据---->
Session.rollback()
<----查询数据没有插入---->

#5.统计和分组：
conn = Session.query(User).filter(User.name.like(‘ch%‘)).count()
print(conn)

from sqlalchemy import func
list_group = Session.query(func.count(User.name),User.name).group_by(User.name).all()
for i in list_group:
    print(i)

#6.将查询的数据可读：
  def __repr__(self):
      return "<User(name=‘%s‘, password=‘%s‘)>" % (self.name, self.password)    #在User类里添加
 my_user = Session.query(User.name,User.password).all()
 print(my_user)

 外键关联：

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,String,Integer,ForeignKey,DATE
from sqlalchemy.orm import sessionmaker,relationship
from sqlalchemy import func
engine = create_engine(‘mysql+pymysql://cheng:123456@192.168.71.3/test_cheng‘,
                       encoding = ‘utf-8‘)


Base = declarative_base()
class Student(Base):
    __tablename__ = ‘student‘
    id = Column(Integer,primary_key=True)
    name = Column(String(32),nullable=False)
    data = Column(DATE,nullable=False)

    def __repr__(self):
        return ‘id:%s,name:%s‘%(self.id,self.name)

class Study_record(Base):
    __tablename__ = ‘study_record‘
    id = Column(Integer,primary_key=True)
    day = Column(Integer,nullable=False)
    status = Column(String(32),nullable=False)
    stu_id = Column(Integer,ForeignKey(‘student.id‘))    #创建外键关联

    student = relationship(‘Student‘,backref=‘my_study_record‘)  #允许你在student表里通过backref字段反向查出study_record表里的字段。
    def __repr__(self):
        #return ‘id:%s,day:%s,stu_id:%s‘%(self.id,self.day,self.stu_id)
        return ‘id:%s,day:%s,stu_id:%s‘ % (self.id, self.day, self.student.name)    #通过self.student.name调出student表里name字段的值。
Base.metadata.create_all(engine)

Session_class = sessionmaker(bind=engine)
Session = Session_class()
data = Session.query(Student).filter(Student.name==‘cheng‘).first()   #查出name等于cheng的学生，再打印出Study_record类里repr的返回值。
print(data.my_study_record)


# stu_obj = Student(name=‘cheng1‘,data = ‘2017-11-01‘)
# record_obj = Study_record(day=2,status =‘yes‘,stu_id=3)  #插入数据时先创建student表里的主键，在创建study_record的外键，因为有外键约束
# Session.add_all([record_obj])
# Session.commit()

 多外键关联：

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,String,Integer,ForeignKey,DATE
from sqlalchemy.orm import sessionmaker,relationship
engine = create_engine(‘mysql+pymysql://cheng:123456@192.168.71.3/test_cheng?charset=utf8‘,
                       encoding=‘utf-8‘)

Base = declarative_base()

class Customer(Base):
    __tablename__ = ‘customer‘
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    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(32))
    city = Column(String(32))
    state = Column(String(32))
Base.metadata.create_all(engine)

Session_class = sessionmaker(bind=engine)
Session = Session_class()
#address_obj = Address(street =‘tongzhou1‘,city =‘beijing2‘,state=‘beijing3‘)
address_obj = Address(street =‘通州‘,city =‘北京‘,state=‘北京‘)
Session.add_all([address_obj])
customer_obj = Customer(name=‘chengll‘,billing_address=address_obj,shipping_address=address_obj)   #通过以上关联，可以这样进行赋值
Session.add_all([customer_obj])
Session.commit()

 多对多：

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

engine = create_engine(‘mysql+pymysql://root:ccc949885111@192.168.71.3/cuichao?charset=utf8‘,encoding=‘utf-8‘)
Base = declarative_base()

book_m2m_author = Table(‘book_m2m_author‘, Base.metadata,
                        Column(‘book_id‘,Integer,ForeignKey(‘books.id‘)),
                        Column(‘author_id‘,Integer,ForeignKey(‘authors.id‘)),
                        )
class Book(Base):
    __tablename__ = ‘books‘
    id = Column(Integer,primary_key=True)
    name = Column(String(64))
    pub_date = Column(DATE)
    authors = relationship(‘Author‘,secondary=book_m2m_author,backref=‘books‘)
    def __repr__(self):
        return self.name

class Author(Base):
    __tablename__ = ‘authors‘
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    def __repr__(self):
        return self.name
Base.metadata.create_all(engine)

Session_class = sessionmaker(bind=engine)  # 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
s = Session_class()  # 生成session实例

b1 = Book(name="跟Alex学Python")
b2 = Book(name="跟Alex学把妹")
b3 = Book(name="跟Alex学装逼")
b4 = Book(name="跟Alex学开车")

a1 = Author(name="Alex")
a2 = Author(name="Jack")
a3 = Author(name="Rain")

b1.authors = [a1, a2]
b2.authors = [a1, a2, a3]

s.add_all([b1, b2, b3, b4, a1, a2, a3])

s.commit()
# print(‘--------通过书表查关联的作者---------‘)
#
# book_obj = s.query(Book).filter_by(name="跟Alex学Python").first()
# print(book_obj.name, book_obj.authors)
#
# print(‘--------通过作者表查关联的书---------‘)
# author_obj = s.query(Author).filter_by(name="Alex").first()
# print(author_obj.name, author_obj.books)
# s.commit()

 

Python 9 sqlalchemy ORM

标签：custom   metadata   elf   反向   use   make   多对多   lte   基类