There are many databases supported by Python, and MySQL is one of the major databases. Let’s take a look at the operation interaction between them.

There are three Python libraries that operate MySQL, python-mysql (MySQLdb), PyMySQL and SQLAlchemy.

  • Commonly used in python2Python - MySQL (MySQLdb), the core is built by C language, the best performance, the disadvantage is that the installation is complex, has stopped updating, does not support python3.
  • PyMySQLBuilt to replace it, pure Python, easy to install, python3 support.
  • SQLAlchemyIs an ORM framework, ORM framework is the role of the database table a row of records and an object each other to do automatic conversion, it itself can not operate the database, but to rely on MySQLdb, PyMySQL and other third-party libraries to complete, SQLAlchemy is widely used in the field of Web programming.

This article will mainly take SQLAlchemy to learn.

Installation tools

Start by installing the basic database driver Pymysql

pip3 install pymysql
Copy the code

Then install ORM framework SQLAlchemy

pip3 install sqlalchemy
Copy the code

In daily work, if you don’t want to look at your data every time you run a command line. Navicat is recommended for MySQL. This graphical tool allows you to quickly and easily operate the database and query data in real time.

Initializing the database

With the necessary tools installed, we tried to create a user data user table. First, initialize the DBSession using SQLAlchemy to connect to the database and define the table structure.

# import SQLAlchemy
from sqlalchemy import Column, BIGINT, String, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Create base class
Base = declarative_base()

Mysql > initialize database connection
# 'database type + database driver name :// username: password @ database address: port number/database name'
engine = create_engine('mysql+pymysql://root:123123@mysql:3306/test')

Create DBSession type:
DBSession = sessionmaker(bind=engine)
Create session object:
session = DBSession()

# database operation method
Initialize the database
def init_db(a):
    Base.metadata.create_all(engine)
Mysql > delete database
def drop_db(a):
    Base.metadata.drop_all(engine)
Copy the code

Create user table model:

Define the user class
class User(Base):
    # the name of the table
    __tablename__ = "user"

    # table structure
    # set id as primary key and increment
    id = Column(BIGINT, primary_key=True, autoincrement=True)
    name = Column(String(20))
    gender = Column(String(2))

The user table will be created automatically if there is no user table
init_db()
Copy the code

One caveat here is that the user table model needs to be defined before the database is initialized, otherwise the user table cannot be created properly. Session (session), can be thought of as an object that manages persistent connections to the database. Subsequent operations will be based on the Session object.

If the INT increment type is used, an error occurs when the number of records in a table exceeds 2,147,483,647 (about 2.1 billion). A maximum of 92.2 trillion records can be obtained using the BIGINT autoincrement type.

Add, delete, change and check operation

increase

After initializing the ORM object, let’s try inserting a record.

Create a new User object
new_user = User(name='mrlizi', gender='man')
# add to session:
session.add(new_user)
# Batch add
session.add_all([
    User(name='Child not fish', gender='M'),
    User(name="Concubine", gender='F'),
    User(name='Mulan', gender='F')])Commit to database:
session.commit()
Copy the code

Results:

check

The Session query function returns a Query object. The Query function can take multiple parameter types.

# query: Outputs all user names
result = session.query(User.name)
# order: Output all users in reverse order
result = session.query(User).order_by(User.id.desc())
result = session.query(User).order_by(-User.id)
# label: user-defined field name. The user name can be obtained from the query result through item.my_name
for item in session.query(User.name.label('my_name')).all()
# filter and filter_by: Filter users with user name 'mrlizi'
result = session.query(User).filter(User.name=='mrlizi').one()
result = session.query(User).filter_by(name='mrlizi').one()
Offset and limit: used together to do paging queries (using Python slicing as well), the following two statements give the same result
result = session.query(User).offset(2).limit(1).all()
result = session.query(User)[1:3]

# AND: AND query
result = session.query(User).filter(and_(User.name=='mrlizi', User.gender=='M')).all()
result = session.query(User).filter(User.name=='mrlizi', User.gender=='M')
result = session.query(User).filter(User.name=='mrlizi').filter(User.gender=='M').all()
# OR: OR query
result = session.query(User).filter(or_(User.name == 'Child not fish', User.name == 'Mulan'))
# fuzzy query
result = session.query(User).filter(User.name.like('a child %')).all()
Copy the code

Basic daily use of query methods are these, object-oriented operations are more flexible, you can freely combine according to different scenarios.

change

It is much easier to modify than to query, find the hit record, and then modify it through the UPDATE method. The synchronize_session parameter of the update method is used to determine whether the current session is updated after data is updated. Synchronize_session = False Asynchronously update current session synchronize_session = ‘fetch’ Update to session object synchronize_session = ‘evaluate’ Record the corresponding object before update and delete the recorded object after update. (This means that the current session record is not updated synchronously with the database)

Method a #
session.query(User).filter(User.name == 'mrlizi').update({'name': '李白'})
Method # 2
user = session.query(User).filter(User.name == '李白').first()
user.name = 'the armoured'
# Operation mode
result = session.query(User).filter(User.name == "Concubine").update({User.name: 'Sun Shangxiang'}, synchronize_session='fetch')
# submit
session.commit()
Copy the code

delete

Delete, is nothing more than to query the target record, and then delete.

Select * from 'filter' where 'filter';
session.query(User).filter_by(name="Armor").delete()
session.commit()
Copy the code

Associated table query

MySQL as a relational database, you can set the foreign key to perform multiple table associated query. SQLAlchemy, in turn, provides one-to-one, one-to-many, and many-to-many associations between objects.

More than a pair of

In the one-to-many relationship of SQLAlchemy, ForeignKey() is used to represent the ForeignKey of a table, and relationship() represents the attributes associated with tables.

def one_to_many(a):
    Define the user class
    class User(Base):
        # the name of the table
        __tablename__ = "user"

        # table structure
        # set id as primary key and increment
        id = Column(BIGINT, primary_key=True, autoincrement=True)
        name = Column(String(20))
        # Define the public account attribute of user concern, specify the relationship between the two
        account = relationship('Account', back_populates="user")

    class Account(Base):
        __tablename__ = 'account'

        id = Column(BIGINT, primary_key=True, autoincrement=True)
        name = Column(String(20))
        Select * from user where user = user;
        user_id = Column(BIGINT, ForeignKey('user.id'))
        Define the user attribute of the Account
        user = relationship("User", back_populates="account")

    Empty the database and reinitialize it
    drop_db()
    init_db()

    mrlizi = User(name='mrlizi')
    mrlizi.account = [
        Account(name='Siege of lion Canyon'),
        Account(name='zone7')
    ]
    session.add(mrlizi)

    result = session.query(User).filter(User.name == 'mrlizi').one()
    for item in result.account:
        print(item.name)

    result = session.query(Account).filter(Account.name == 'Siege of lion Canyon').one()
    print(result.user.name)

    session.commit()

one_to_many()
Copy the code

The implementation of the above code:

  • Build a one-to-many data table model
  • Delete the previous data and re-initialize it. Create a new user with the new table model and add the public account of interest
  • Add name mrliziuserTable records, at the same time to create the associated public number information record.
  • throughuserTable queries associated public number data
  • throughaccountTable queries associated user data

One to one

One-to-one is two tables related to each other. We just use the uselist parameter in the parent table based on the one-to-many relationship. The implementation code is as follows:

def one_to_one(a):
    Define the user class
    class User(Base):
        __tablename__ = "user"

        id = Column(BIGINT, primary_key=True, autoincrement=True)
        name = Column(String(20))
        account = relationship('Account', uselist=False, back_populates="user")

    # Public account class
    class Account(Base):
        __tablename__ = 'account'

        id = Column(BIGINT, primary_key=True, autoincrement=True)
        name = Column(String(20))
        Select * from user where user = user;
        user_id = Column(BIGINT, ForeignKey('user.id'))
        Define the user attribute of the Account
        user = relationship("User", back_populates="account")

    Empty the database and reinitialize it
    drop_db()
    init_db()

    # add record
    user = User(name='Child not fish')
    user.account = Account(name='Siege of lion Canyon')
    session.add(user)
    session.commit()

    # query
    result = session.query(User).filter(User.name == 'Child not fish').one()
    print(result.account.name)
    # output:
    # Siege lion Canyon

one_to_one()
Copy the code

Many to many

Many-to-many is implemented by adding an associated table between two tables. The associated table uses MetaData objects to associate two tables, and uses ForeignKey parameters to specify links to locate two different tables. For two different tables, the association table is specified with the secondary parameter in the relationship() method.

def many_to_many(a):
    # associative table
    association_table = Table('association', Base.metadata,
                              Column('user_id', BIGINT, ForeignKey('user.id')),
                              Column('account_id', BIGINT, ForeignKey('account.id')))class User(Base):
        __tablename__ = "user"

        id = Column(BIGINT, primary_key=True, autoincrement=True)
        name = Column(String(20))
        accounts = relationship('Account', secondary=association_table, back_populates="users")

    class Account(Base):
        __tablename__ = 'account'

        id = Column(BIGINT, primary_key=True, autoincrement=True)
        name = Column(String(20))
        users = relationship("User", secondary=association_table, back_populates="accounts")

    Empty the database and reinitialize it
    drop_db()
    init_db()
    
    # create record
    user1 = User(name='Child not fish')
    user2 = User(name='zone')
    user3 = User(name='mrlizi')
    account1 = Account(name='Siege of lion Canyon')
    account2 = Account(name='zone7')

    # associative record
    user1.accounts = [account1]
    user2.accounts = [account1, account2]
    user3.accounts = [account2]

    Add and save
    session.add(user1)
    session.add(user2)
    session.add(user3)
    session.commit()

    # two-way query
    result1 = session.query(User).filter(User.name == 'zone').one()
    for item in result1.accounts:
        print(item.name)
    result2 = session.query(Account).filter(Account.name == 'Siege of lion Canyon').one()
    for item in result2.users:
        print(item.name)

many_to_many()
Copy the code

conclusion

MySQL as one of the mainstream databases, we do not necessarily say how much in-depth study of its use, but at least some understanding. And MySQL is pretty easy to use in Python. It’s just a matter of coding.