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

Editor to share with you how to use SQLAlchemy in the Python ORM framework. I hope you will get something after reading this article. Let's discuss it together.

1. SQLAlchemy introduces the concept of 1.1 ORM

The full name of ORM is Object Relational Mapping (object-relational mapping). Through ORM, you can use the objects in python to operate the database (converted to sql statements at the bottom), eliminating the writing of sql statements.

However, because of the high degree of abstraction, the execution efficiency of sql statements is relatively low, so in some cases, we still need to write sql statements ourselves.

ORM converts python code into sql statements through the following correspondence:

Python object relational database class table class attribute field class instance object record instance object property value record field value 1.2 SQLAlchemy introduction

When you access the database in django, you usually use the ORM (Object Relational Mapping) object-relational mapping that comes with django to access the database. You only need to use the syntax of python to manipulate the object, and it can be automatically mapped to sql statements.

SQLAlchemy is a specialized object-relational mapper and Python SQL toolkit designed to achieve efficient and high-performance database access.

1.3 Architecture

Schema / Types: mapping rules between classes to tables.

SQL Expression Language: SQL statement.

Engine: engine.

Connection Pooling: connection pool.

Dialect: dialect, call different database API (Oracle, postgresql, Mysql) and execute the corresponding SQL statement.

1.4 Asynchronous

Before version 1.4, SQLAlchemy implemented support for asynchrony through greenlet, while in version 1.4 and later, support for the python asyncio standard library was added. Therefore, this requires the python interpreter version at 3.6 +.

1.5 installation

Install SQLAlchemy (version 1.4):

Pip install SQLAlchemy

If you need greenlet asynchronous support:

Pip install sqlalchemy [asyncio] II. SQLAlchemy Quick start 2.1.Create configuration (optional)

This step is not necessary, but putting the configuration in a separate file makes it easy for us to manage and modify it.

Create a configuration file, such as settings.py:

DBMS = 'mysql' # database management system name: such as sqlite, mysql, oracle, etc. DBAPI =' pymysql' # DBAPI (third-party driver) Such as pysqlite, pymysql, etc. # here are the contents of the database management system: host IP, port, user name, password, database HOST = 'localhost' PORT = 3306 USERNAME =' root'PASSWORD = '123456'DB =' myclass'DB_URI = f' {DBMS} + {DBAPI}: / / {USERNAME}: {PASSWORD} @ {HOST}: {PORT} / {DB} '2.2 create the engine and obtain and close the connection

Any SQLAlchemy application starts with an object called Engine, which is a global object created once for a specific database server, which can be understood as manipulating the connection center of the database through SQLAlchemy, holding the connection pool.

From sqlalchemy import create_enginefrom settings import DB_URIengine = create_engine (DB_URI) # creation engine # can also pass in a string directly Do not use the configuration file # engine = create_engine ("database management system name + driver: / / username: password @ host IP: port number / database name", echo=True, future=True) conn = engine.connect () # get the connection result = conn.execute ('SQL statement') # execute SQLprint (result.fetchone ()) # print one line conn.close () # in the SQL execution result close the connection

Other common parameters of create_engine ():

Echo: set to True, which records SQL to a logger, which writes SQL to standard output.

Future: use a 2.0-style engine and connect to API to use the new features in version 2.0.

Encoding: the default is utf-8.

Pool_size: the number of connections that remain open in the connection pool.

2.3.Create ORM model from sqlalchemy.orm import declarative_basefrom sqlalchemy import Column, Integer, String# create base class Base = declarative_base () # each model class inherits the base class class User (Base) created by declarative_base (): # define table name _ _ tablename__ = 'users' # definition field Parameters create a session for field types and constraints id = Column (Integer, primary_key=True, autoincrement=True) name = Column (String) age = Column (Integer) sex = Column (String (10)) 2.4

After each operation in python, you need to submit it to the database through session:

From sqlalchemy.orm import sessionmaker# creates a session, which is equivalent to Django ORM's objectsSession = sessionmaker (bind=engine) session = Session () # to add, delete, modify and query data. # for example, add data # session.add (model class (class attribute = value, …)) # submit session.commit () # close sessionsession.close ()

Before commit (), you can cancel the changes made to the instance object, that is, roll back:

Session.rollback () 2.5 create and delete tables

Create all tables:

Base.metadata.create_all (engin)

Delete all tables:

Base.metadata.drop_all (engin) 2.6 New data

New data:

Variable name = model class (class attribute = value, …)

Session.add (variable name)

Add in batch:

Session.add_all ([

Model class (class attribute = value, …...)

Model class (class attribute = value, …...)

……

])

2.7 query data

Query all:

Variable name = session.query (model class). All ()

Returns a list of model class instance objects.

Query the specified field:

Variable name = session.query (model class. Field) .all ()

Get only the first one that returns the result:

Variable name = session.query (model class). First ()

Query with tables:

Variable name = session.query (model class 1, model class 2). Filter (condition). All ()

# for example, query user ID and his favorite IDres = session.query (User, Hobby) .filter (User.hobby_id = = Hobby.id) .all ()

Returns a list of tuples containing two instance objects.

Filter:

Variable name = session.query (model class). Filter (condition). All ()

# for example, query the names of students over the age of 18 res = session.query (Student.name) .filter (Student.age > = 18) .all ()

Operators commonly used in conditions:

Multi-conditional filtering:

The default between conditions is and relation variable name = session.query (model class). Filter (condition 1, condition 2...) .all () # uses the or from sqlalchemy import or_ variable name = session.query (model class) .filter (or_ (condition 1, condition 2...)) .all ()

Sort:

Variable name = session.query (model class). Order_by (sort by field. Desc ()). All () # desc indicates reverse order, write asc or not is ascending order

Number of statistics:

Variable name = session.query (model class). Count ()

Slice:

Variable name = session.query (model class). All () [1:3]

2.8 modify data

Variable name = session.query (model class). Filter (condition). Update ({"field": value})

# Don't forget to submit

2.9 deleting data

Session.query (model class). Filter (condition). Delete ()

3. Multi-meter operation

In the following content, I will call the model class that defines the foreign key field the associated model, and the other is the associated model.

3.1 one-to-many

Create the model:

Foreign keys are defined on more than one side.

From sqlalchemy import ForeignKeyfrom sqlalchemy.orm import relationshipclass model class name (Base): _ _ tablename__ = 'table name' field = Column (type, constraint)... Foreign key field = Column (type, other constraints, ForeignKey (association model. (field)) # key associated model name lowercase = relationship ("name of the associated model", backref= "name for reverse query") # is not a field and will not be created in the database

New data:

# positive add

Variable name = associated model (field = value, …... , foreign key field = association model (field = value …))

Session.add (variable name)

# add in reverse

Variable name A = associated model (field = value, …...)

The value of the variable name A.backref = [instance 1 of the associated model, instance 2 of the associated model.]

Session.add (variable name A)

Forward query:

First get the instance of the associated model, and then through the instance. The foreign key gets the associated object.

Reverse query:

First get the instance of the associated model, and then get the instance object of the associated model through the value of the instance .backref.

3.2 many-to-many

Create the model:

Create a new intermediate table and save only the corresponding relationship between the two parties.

On one side, define relationship:

Associated model class name lowercase = relationship ("name of associated model class", lowercase name of secondary=' intermediate table model class, backref= "name for reverse query") # is not a field and will not be created in the database

New data:

After both parties have created the data, add the corresponding relationship directly to the intermediate table.

Forward and reverse queries:

Consistent with the one-to-many model.

After reading this article, I believe you have some understanding of "how to use SQLAlchemy in Python ORM framework". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for your reading!

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.