[Python] SQLAlchemy

A database table is a two-dimensional table that contains multiple rows and columns. If the contents of a table are represented by a Python data structure, a list can be used to represent multiple rows, and each element of the list is a tuple representing a row of records, for example, a id table containing and name user :

[    (‘1‘, ‘Michael‘),    (‘2‘, ‘Bob‘),    (‘3‘, ‘Adam‘)]

The data structure returned by Python's db-api is represented as above.

But it is hard to see the structure of a table with a tuple. If a tuple is represented by a class instance, it is easier to see the structure of the table:

class User(object):    def __init__(self, id, name): self.id = id self.name = name[ User(‘1‘, ‘Michael‘), User(‘2‘, ‘Bob‘), User(‘3‘, ‘Adam‘)]

This is the legendary ORM technique: Object-relational Mapping, which maps the table structure of a relational database to an object. Isn't it simple?

But who will do the conversion? So the ORM framework emerges.

In Python, the most famous ORM framework is sqlalchemy. Let's take a look at the usage of SQLAlchemy.

First install SQLAlchemy via PIP:

$ pip install sqlalchemy

Then, using the table we created in the MySQL test database last time user , try it with SQLAlchemy:

The first step is to import the SQLAlchemy and initialize the dbsession:

ImportFrom SQLAlchemyImport Column, String, Create_enginefrom sqlalchemy.orm import sessionmakerfrom sqlalchemy.ext.declarative import declarative_base# Create base class for objects: base = Declarative_base () # define user object: class user (Base): # table name: __tablename__ =  ' user ' # table structure: id = Column (string (20), primary_key=< Span class= "built_in" >true) name = Column (String (20)) # Initialize database connection: engine = Create_engine ( ' mysql+mysqlconnector://root:[email protected]:3306/test ') # create dbsession type: dbsession = Sessionmaker (bind=engine)       

The above code completes the initialization of the SQLAlchemy and the class definition for each table. If you have more than one table, continue to define other classes, such as school:

class School(Base):    __tablename__ = ‘school‘ id = ... name = ...

create_engine()Used to initialize a database connection. SQLAlchemy represents the connection information with a string:

‘数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名‘

You only need to replace the user name, password and other information as needed.

Next, let's look at how to add a row of records to a database table.

With ORM, we add a row of records to a database table and can be thought of as adding an User object:

# 创建session对象:session = DBSession()# 创建新User对象:new_user = User(id=‘5‘, name=‘Bob‘)# 添加到session:session.add(new_user)# 提交即保存到数据库:session.commit()# 关闭session:session.close()

Visible, the key is to get the session, then add the object to the session, and finally commit and close. DBSessionobject can be considered the current database connection.

How do I query data from a database table? With ORM, the query can be no longer a tuple, but an User object. The query interfaces provided by SQLAlchemy are as follows:

# 创建Session:session = DBSession()# 创建Query查询，filter是where条件，最后调用one()返回唯一行，如果调用all()则返回所有行:user = session.query(User).filter(User.id==‘5‘).one()# 打印类型和对象的name属性:print(‘type:‘, type(user))print(‘name:‘, user.name)# 关闭Session:session.close()

The results of the operation are as follows:

<class ‘__main__.User‘>name: Bob

As can be seen, ORM is the database table of the row with the corresponding object to establish an association, and convert each other.

Since multiple tables in a relational database can also be used to implement one-to-many, many-to-many associations with foreign keys, the ORM Framework can also provide one-to-many, many-to-many functions between two objects.

For example, if a user has more than one book, you can define a one-to-many relationship as follows:

 class user  ' user ' id = Column (string (20) , Primary_key=true) name = Column (String (20) # One-to-many: Books = relationship ( "book") class book20), Primary_key=true) name = Column (String (20)) #" is associated to the user table by a foreign key: user_id = Column (String (20), ForeignKey (  ' user.id ')             

When we query a user object, the books property of the object returns a list containing a number of book objects.

[Python] SQLAlchemy