Python sqlalchemy module (required in the project) and pythonsqlalchemy

Python sqlalchemy module (required in the project) and pythonsqlalchemy
Sqlalchemy Introduction

SQLAlchemy is an open-source software in the Python programming language. Provides the SQL Toolkit and object relationship ing (ORM) tool, which can be issued using the MIT license.

SQLAlchemy "uses a simple Python language, designed for efficient and high-performance database access, and implements a complete enterprise-level persistent model ". SQLAlchemy's philosophy is that the magnitude and performance of SQL databases are important to object sets, while the abstraction of object sets is important to tables and rows. Therefore, SQLAlchmey adopts a data ing model similar to Hibernate in Java, rather than the Active Record model used by other ORM frameworks. However, optional plug-ins such as Elixir and declarative allow users to use declarative syntax.

The relationship between SQLAlchemy and the database is as follows:

Basic sqlalchemy operations

1. Install sqlalchemy

• This article uses the mysql Case, so you need a machine with the mysql database installed.
• Install pip3 install sqlalchemy using pip3 in python

View version information after installation

import sqlalchemysqlalchemy.__version__

2. Connect to the database

In sqlalchemy, sessions are used to create sessions between programs and databases. All objects must be loaded and saved through the session object.

From sqlalchemy import create_enginefrom sqlalchemy. orm import sessionmaker # link database using pymysq module for ing, the following parameter is the maximum number of connections 5 ENGINE = create_engine ("mysql + pymysql: // root@127.0.0.1: 3306/digchouti? Charset = utf8 ", max_overflow = 5) Session = sessionmaker (bind = engine) session = Session ()

3. Create a ing (create a table)

A ing corresponds to a Python class used to represent the structure of a table. Create a person table, including the id and name fields. That is to say, creating a table is implemented using a python class.

Import sqlalchemyfrom sqlalchemy import into sqlalchemy. ext. declarative import into sqlalchemy import Column, Integer, Stringfrom sqlalchemy. orm import sessionmakerENGINE = create_engine ("mysql + pymysql: // root@127.0.0.1: 3306/digchouti? Charset = utf8 ", max_overflow = 5) # generate a SQLORM Base class, which must be inherited when creating a table. What do you mean by this? Base = declarative_base () class Person (Base): _ tablename _ = 'userinfo' id = Column (Integer, primary_key = True) name = Column (String (32 )) def _ repr _ (self): return "<Person (name = '% s')>" % self. name

This Code creates a table named userinfo with two columns in the Table: id and name.

4. Add data

Of course, when we create a table, we must also add data. The Code is as follows:

# Create a person object person = Person (name = 'zhang Yanlin ') # Add the person object, but it is still not submitted to the database session. add (person) # submit the database session. commit ()

Of course, you can add multiple data entries:

Session. add_all ([Person (name = 'zhang yanlin'), Person (name = 'aylin')]) session. commit ()

5. Search for Data

In the sqlalchemy module, the query () method provided by the data query module is described below:

# Retrieve all data sessions. query (Person ). all () # obtain the row data session of name = 'zhang Yanlin. query (Person ). filter (Person. name = 'zhang yanlin '). one () # obtain the First session of the returned data. query (Person ). first () # search for all data sessions with IDs greater than 1. query (Person. name ). filter (Person. id> 1 ). all () # Use the limit index to retrieve the first two rows of data session. query (Person ). all () [] # order by, sort sessions in ascending order of IDs. query (Person ). ordre_by (Person. id) # equal/like/inquery = session. query (Person) query. filter (Person. id = 1 ). all () query. Filter (Person. id! = 1 ). all () query. filter (Person. name. like ('% ay % ')). all () query. filter (Person. id. in _ ([1, 2, 3]). all () query. filter (~ Person. id. in _ ([1, 2, 3]). all () query. filter (Person. name = None ). all () # and orfrom sqlalchemy import and_from sqlalchemy import or_query.filter (and _ (Person. id = 1, Person. name = 'zhang yanlin ')). all () query. filter (Person. id = 1, Person. name = 'zhang yanlin '). all () query. filter (Person. id = 1 ). filter (Person. name = 'zhang yanlin '). all () query. filter (or _ (Person. id = 1, Person. id = 2 )). all () # count calculates the number of sessions. query (Person ). count () # modify updatesession. query (Person ). filter (id> 2 ). update ({'name': 'zhang yanlin '})

There are a lot of queries, and I hope you will forgive me for not writing them completely. I believe everyone can expand the rest.

After the above introduction is complete, you may not be able to integrate it into one piece. Below I will write it into one piece for everyone:

From sqlalchemy. ext. declarative import declarative_basefrom sqlalchemy import Columnfrom sqlalchemy import Integer, String, TIMESTAMPfrom sqlalchemy import ForeignKey, UniqueConstraint, Indexfrom sqlalchemy. orm import sessionmaker, relationshipfrom sqlalchemy import create_engineENGINE = create_engine ("mysql + pymysql: // root@127.0.0.1: 3306/digchouti? Charset = utf8 ", max_overflow = 5) Base = declarative_base () class Person (Base): _ tablename _ = 'userinfo' id = Column (Integer, primary_key = True) name = Column (String (32) def _ repr _ (self): return "<Person (name = '% s')>" % self. name # create a connected database for submission. Now the table is created. You can view the Base in the database. metadata. create_all (ENGINE) Session = sessionmaker (bind = engine) # insert multiple data sessions into it = session () Session. add_all ([Person (name = 'zhang yanlin'), Person (name = 'handsome ')]) session. commit ()

Advanced usage of sqlalchemy table relationships

The operations on a table are described above. The following describes the one-to-many and many-to-many table relationships. All users who know the database know the Foreign keys, that is, the creation of table relationships.

1. One-to-multiple foreign keys (1)

In the first method, we only use common operations. This method is better than understanding. After the first table is created, insert the data and remember to submit the data, then, when creating data in the second chapter, you can take the first associated data directly. The Code is as follows:

#! /Usr/bin/env python #-*-coding: UTF-8-*-from sqlalchemy. ext. declarative import declarative_basefrom sqlalchemy import Column, Integer, ForeignKey, UniqueConstraint, Index, Stringfrom sqlalchemy. orm import sessionmaker, relationshipfrom sqlalchemy import create_engineengine = create_engine ('mysql + pymysql: // root@127.0.0.1: 3306/db1') Base = declarative_base () class Son (Base ): _ tablename _ = 'son' id = Column (Integer, primary_key = True) name = Column (String (32) age = Column (String (32 )) # create a foreign key. The id item father_id = Column (Integer, ForeignKey ('father. id ') class Father (Base): _ tablename _ = 'father' id = Column (Integer, primary_key = True) name = Column (String (32 )) age = Column (String (32) Base. metadata. create_all (engine) Session = sessionmaker (bind = engine) session = Session () f1 = Father (name = 'hangyanlin', age = '18') session. add (f1) session. commit () w1 = Son (name = 'xiaozhang1', age = 3, father_id = 1) w2 = Son (name = 'xiaozhang2', age = 3, father_id = 1) session. add_all ([w1, w2]) session. commit ()

2. One-to-multiple foreign key (2) relationship

The second method is the same as the first one, but the relationship is used here for foreign key relations. The Code is as follows:

#! /Usr/bin/env python #-*-coding: UTF-8-*-from sqlalchemy. ext. declarative import declarative_basefrom sqlalchemy import Column, Integer, ForeignKey, UniqueConstraint, Index, Stringfrom sqlalchemy. orm import sessionmaker, relationshipfrom sqlalchemy import create_engineengine = create_engine ('mysql + pymysql: // root@127.0.0.1: 3306/db1') Base = declarative_base () class Son (Base ): _ tablename _ = 'son' id = Column (Integer, primary_key = True) name = Column (String (32) age = Column (String (32 )) father_id = Column (Integer, ForeignKey ('father. id ') class Father (Base): _ tablename _ = 'father' id = Column (Integer, primary_key = True) name = Column (String (32 )) age = Column (String (32) son = relationship ('son') Base. metadata. create_all (engine) Session = sessionmaker (bind = engine) session = Session () f1 = Father (name = 'hangyanlin', age = '18 ') w1 = Son (name = 'xiaozhang1', age = '3') w2 = Son (name = 'xiaozhang2', age = '4 ') # The key point is that the binding relationship f1.son = [w1, w2] # You only need to pass the father in, and the son will naturally upload it to the session. add (f1) session. commit ()