SQLAlchemy Operation MySQL

SQLAlchemy "uses the simple Python language for efficient and high-performance database access design, enabling a complete enterprise-class persistence model." SQLAlchemy's philosophy is that the magnitude and performance of SQL databases is important to object collections, and the abstraction of object collections is important for tables and rows. As a result, Sqlalchmey uses a data mapping model similar to that in Java hibernate, rather than the active record model used by other ORM frameworks. However, optional plugins such as elixir and declarative allow the user to use declarative syntax.

Installation

PIP3 Install SQLAlchemy

PIP3 Install Pymysql #需要pymysql或者MySQLdb的支持

Connecting to a database

Mysql+pymysql://<username>:<password>@
Let's look at an example:
#导入想关模块
From SQLAlchemy import create_engine from sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Colum N, Integer, String, ForeignKey, UniqueConstraint, indexfrom sqlalchemy.orm import sessionmaker, relationshipengine = Crea Te_engine ("Mysql+pymysql://root:[email protected]:3306/haha", encoding= ' Utf-8 ', max_overflow=5) # DBSession = Sessionmaker (bind=engine) #实例化Base = Declarative_base () # Host table Class host (base): #声明类 __tablename__ = ' Host ' #需要创建的 Table name nid = Column (Integer, Primary_key=true, Autoincrement=true) #定义主键, since 1, there can be other parameters hostname = column (String (32) ) IP = column (string (+), unique=true) port = column (string (+)) Host_user = relationship (' Hostuser ', Secondary=la mbda:hosttohostuser.__table__,backref= ' h ') #映射表的外键 # Host User table Class Hostuser (Base): __tablename__ = ' host_user ' nid = Col Umn (Integer, Primary_key=true, autoincrement=true) Username = column (string ()) passwd = Column (string (32)) # Host user vs. master   Machine Relationship Table Class Hosttohostuser (Base): __tablename__ = ' host_to_hostuser ' nid = Column (Integer, Primary_key=true, autoincrement=true) User_nid = column (in Teger, ForeignKey (' Host_user.nid ')) Host_nid = Column (Integer, ForeignKey (' Host.nid ')) host = relationship (' host ', b    ackref= ' h ') Host_user = relationship (' Hostuser ', backref= ' U ') # Audit table class Auditlog (Base): __tablename__ = ' Audit_log ' nid = Column (integer, primary_key=true) user_nid = Column (Integer, ForeignKey (' Host_user.nid ')) Host_nid = column (Integer, ForeignKey (' Host.nid ')) cmd = column (string (255)) Date = column (string (255)) def init_db (): Base.metadat A.create_all (Engine) def drop_db (): Base.metadata.drop_all (Engine) init_db () Session = Sessionmaker (bind=engine)  Session = Session () # #session. Add_all ((Host (hostname= ' Web1 ', ip= ' 192.168.11.12 ', port= '), Host (hostname= ' web2 '), ip= ' 192.168.11.23 ', port= ', ', ' Host (hostname= ' web3 ', ip= ' 192.168.11.34 ', port= ' ","), Host (hostname= ' web4 ', ip=    ' 192.168.11.45 ', port= ' 22 ',),Host (hostname= ' web5 ', ip= ' 192.168.11.228 ', port= ',),)) Session.commit () Session.add_all ([Hostuser (Username= ') Madking ', passwd= ' 123 ',), Hostuser (username= ' Oldboy ', passwd= ' 123 ',),]) Session.commit () Session.add_all ((HostToHos TUser (user_nid= ' 1 ', host_nid= ' 1 ',), Hosttohostuser (user_nid= ' 1 ', host_nid= ' 2 ',), Hosttohostuser (user_nid= ' 1 ', host _nid= ' 5 ',), Hosttohostuser (user_nid= ' 2 ', host_nid= ' 1 ',),)) Session.commit ()
SQLAlchemy Operation MySQL