Shulou(Shulou.com)06/01 Report--

SQLAlchemy is an ORM framework under the Python programming language, which is based on the database API. It uses relational object mapping to operate the database. In short, it converts the object into SQL, and then uses the data API to execute SQL and get the execution result.

First, install pip3 install SQLAlchemy

SQLAlchemy itself cannot operate the database. It must come from third-party plug-ins such as pymsql. Dialect is used to communicate with the data API and call different database API according to the configuration file, so as to realize the operation of the database, such as:

MySQL-Python mysql+mysqldb://:@ [:] / pymysql mysql+pymysql://:@/ [?] MySQL-Connector mysql+mysqlconnector://:@ [:] / cx_Oracle oracle+cx_oracle://user:pass@host:port/dbname [? key=value&key=value...] For more details, see http://docs.sqlalchemy.org/en/latest/dialects/index.html II. Internal processing.

Use Engine/ConnectionPooling/Dialect for database operations, Engine uses ConnectionPooling to connect to the database, and then execute the SQL statement through Dialect.

#! / usr/bin/env python#-*-coding:utf-8-*-from sqlalchemy import create_engine# processing Chinese # sqlalchemy setting Encoding character set must add charset=utf8 to the URL accessed by the database Otherwise, the connection to the database will not be the utf8 encoding format engine = create_engine ("mysql+pymysql://root:123@127.0.0.1:3306/t1?charset=utf8'", max_overflow=5, echo=True) # execute SQL# cur = engine.execute (# "INSERT INTO hosts (host, color_id) VALUES ('1.1.1.22') 3) "#) # New insert row self-increment ID# cur.lastrowid# executes SQL# cur = engine.execute (#" INSERT INTO hosts (host, color_id) VALUES (% s,% s) ", [('1.1.1.22lines, 3), (' 1.1.1.221 lines, 3),] #) # executes SQL# cur = engine.execute (#" INSERT INTO hosts (host, color_id) VALUES (% (host) s) % (color_id) s) ", # host='1.1.1.99', color_id=3#) # execute SQL# cur = engine.execute ('select * from hosts') # get the first row of data # cur.fetchone () # get the nth row of data # cur.fetchmany (3) # get all data III. Use of the ORM function

Use all components of ORM/Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect to manipulate the data. Create an object based on the class, convert the object to SQL, and execute SQL.

1. Create table #! / usr/bin/env python#-*-coding:utf-8-*-from sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Indexfrom sqlalchemy.orm import sessionmaker, relationshipfrom sqlalchemy import create_engineengine = create_engine ("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=5) Base = declarative_base () # create a single table class Users (Base): _ _ tablename__ = 'users' id = Column (Integer) Primary_key=True) name= Column (String (32)) extra = Column (String (16)) _ _ table_args__ = (UniqueConstraint ('id',' name', name='uix_id_name'), Index ('ix_id_name',' name', 'extra'),) # one to many class Favor (Base): _ _ tablename__ =' favor' nid = Column (Integer Primary_key=True) caption = Column (String (50), default='red', unique=True) class Person (Base): _ _ tablename__ = 'person' nid = Column (Integer, primary_key=True) name = Column (String (32), index=True, nullable=True) favor_id = Column (Integer, ForeignKey ("favor.nid")) # many to many class Group (Base): _ _ tablename__ =' group' id = Column (Integer, primary_key=True) name = Column (String (64), unique=True Nullable=False) port = Column (Integer, default=22) class Server (Base): _ _ tablename__ = 'server' id = Column (Integer, primary_key=True, autoincrement=True) hostname = Column (String (64), unique=True, nullable=False) class ServerToGroup (Base): _ _ tablename__ =' servertogroup' nid = Column (Integer, primary_key=True, autoincrement=True) server_id = Column (Integer, ForeignKey ('server.id') group_id = Column (Integer) ForeignKey ('group.id')) def init_db (): Base.metadata.create_all (engine) def drop_db (): Base.metadata.drop_all (engine) Note: another way to set external inspection ForeignKeyConstraint ([' other_id'], ['othertable.other_id']) 2, operation table

Table structure + database connection

#! / usr/bin/env python#-*-coding:utf-8-*-from sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Indexfrom sqlalchemy.orm import sessionmaker, relationshipfrom sqlalchemy import create_engineengine = create_engine ("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=5) Base = declarative_base () # create a single table class Users (Base): _ _ tablename__ = 'users' id = Column (Integer) Primary_key=True) name= Column (String (32)) extra = Column (String (16)) _ _ table_args__ = (UniqueConstraint ('id',' name', name='uix_id_name'), Index ('ix_id_name',' name', 'extra'),) def _ repr__ (self): return "% s% s"% (self.id Self.name) # one-to-many class Favor (Base): _ _ tablename__ = 'favor' nid = Column (Integer, primary_key=True) caption = Column (String (50), default='red', unique=True) def _ _ repr__ (self): return "% s% s" (self.nid, self.caption) class Person (Base): _ _ tablename__ =' person' nid = Column (Integer, primary_key=True) name = Column (String (32)) Index=True, nullable=True) favor_id = Column (Integer, ForeignKey ("favor.nid")) # has nothing to do with generating table structure Favor = relationship ("Favor", backref='pers') # many-to-many class ServerToGroup (Base): _ _ tablename__ = 'servertogroup' nid = Column (Integer, primary_key=True, autoincrement=True) server_id = Column (Integer, ForeignKey (' server.id') group_id = Column (Integer, ForeignKey ('group.id') group = relationship ("Group", backref='s2g') server = relationship ("Server") Backref='s2g') class Group (Base): _ _ tablename__ = 'group' id = Column (Integer, primary_key=True) name = Column (String (64), unique=True, nullable=False) port = Column (Integer, default=22) # group = relationship (' Group',secondary=ServerToGroup,backref='host_list') class Server (Base): _ _ tablename__ = 'server' id = Column (Integer, primary_key=True, autoincrement=True) hostname = Column (String (64), unique=True Nullable=False) def init_db (): Base.metadata.create_all (engine) def drop_db (): Base.metadata.drop_all (engine) Session = sessionmaker (bind=engine) session = Session () add obj = Users (name= "alex0", extra='sb') session.add (obj) session.add_all ([Users (name= "alex1", extra='sb'), Users (name= "alex2", extra='sb') ]) session.commit () delete session.query (Users) .filter (Users.id > 2). Delete () session.commit () change session.query (Users) .filter (Users.id > 2). Update ({"name": "099"}) session.query (Users) .filter (Users.id > 2). Update ({Users.name: Users.name + "099"}, synchronize_session=False) session.query (Users) .filter (Users.id > 2). Update ({"num": Users.num + 1}) Synchronize_session= "evaluate") session.commit () check ret = session.query (Users). All () ret = session.query (Users.name, Users.extra). All () ret = session.query (Users). Filter_by (name='alex'). All () ret = session.query (Users). Filter_by (name='alex'). First () ret = session.query (Users) .filter ("id)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.