Python 9 sqlalchemy ORM
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Python 9 sqlalchemy ORM，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含8333字，纯文字阅读大概需要12分钟。
内容图文
-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 基类
本文系统来源：http://www.cnblogs.com/chimeiwangliang/p/7269102.html
内容总结
以上是互联网集市为您收集整理的Python 9 sqlalchemy ORM全部内容，希望文章能够帮你解决Python 9 sqlalchemy ORM所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】