[Free book benefit, free 10 Python books](Mp.weixin.qq.com/s/QaUaclR-B…

)

This article was first published on the public account “Zone7”. Stay tuned for the latest tweets!

directory

  • preface
  • MySQL GUI tools
  • MySQL meets the Docker
  • Add and delete
  • More than a pair of
  • One to one
  • Many to many
  • Afterword.

preface

MySQL orM SQLAlchemy oracle database SQLAlchemy Oracle database SQLAlchemy oracle database SQLAlchemy oracle database SQLAlchemy oracle database SQLAlchemy oracle database SQLAlchemy oracle database SQLAlchemy oracle database So what is ORM? Object Relational Mapper, a general term that describes the mapping between objects in a program and data records in a database. That’s it. Let’s go!

MySQL GUI tools

Navicat is a GUI tool for MySQL. Navicat is a GUI tool for MySQL. Navicat is a GUI tool for MySQL. You can see data add, delete, change and check in real time without operating the command line to view.

MySQL meets the Docker

Continue to share the Docker compose code snippet. After using Docker, you will no longer have to worry about configuring various development environments.

version: '3'
services:
  mysql_container:
    image: mysql
    ports:
      - "3306:3306"
    volumes:
      - /usr/local/db/mysql:/var/lib/mysql
# - /root/docker/test-mysql/conf.d:/etc/mysql/conf.d
    environment:
      - MYSQL_DATABASE=dbname
      - MYSQL_ROOT_PASSWORD=your_password
Copy the code

Add and delete

Start by defining the table structure

Create a single table
class Users(Base):
    # the name of the table
    __tablename__ = 'users'
    id = Column(BIGINT, primary_key=True, autoincrement=True)
    # define field
    name = Column(String(32))
    age = Column(Integer())
Initialize the database
def init_db():
    Base.metadata.create_all(engine)
Mysql > delete database
def drop_db():
    Base.metadata.drop_all(engine)
Copy the code

The connection

from sqlalchemy import create_engine, Column, Integer, String, BIGINT, ForeignKey, UniqueConstraint, Index, and_, or_, inspect
from sqlalchemy.orm import sessionmaker, relationship,contains_eager
# echo True will print SQL native statements
engine = create_engine('mysql+pymysql://username:password@localhost:3306/db_name'.echo=True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
Session = sessionmaker(bind=engine)
session = Session()
Copy the code

increase

new_user = Users(name='zone', age=18)
session.add(new_user)
# Batch add
session.add_all([
    User(name='zone2', age=25),
    User(name='zone3', age=32)
])
# submit
session.commit()
Copy the code

delete

session.query(User).filter_by(name="zone").delete()
# submit
session.commit()
Copy the code

Modify the

session.query(User).filter(User.name == 2).update({"name": "new name"})
session.query(User).filter(User.id >= 3).update({User.name: "Zone7"}, synchronize_session=False)
session.query(User).filter(User.age == 50).update({"age": 123}, synchronize_session="evaluate")
session.commit()
Copy the code

To find the

Search requirements will be more variable, I will list the more common query requirements.

result = session.query(User).all()   The result is a list
result = session.query(User.id, User.age).all()                    
result = session.query(User).filter_by(name='zone').first()                  
result = session.query(User).filter_by(name='zone2').all()                   
# and, or
result = session.query(User).filter_by(and_(name='zone5',age="23")).all()    
result = session.query(User).filter_by(or_(name='zone5',age="23")).all()     
# fuzzy query
result = session.query(User).filter(User.name.like('zon%')).all()            
# sort
result = session.query(User).order_by(User.age.desc()).all()
# page query
result = session.query(User).offset(1).limit(1).all()                 
Copy the code

More than a pair of

Relational database, not a variety of tables and table relations. Back_populates establishes a two-way relationship in a one-to-many relationship, so that it appears to the other party as a many-to-one relationship.

def one_to_many():
    class Parent(Base):
        __tablename__ = 'parent'
        id = Column(Integer, primary_key=True)
        children = relationship("Child", back_populates="parent")

    class Child(Base):
        __tablename__ = 'child'
        id = Column(Integer, primary_key=True)
        parent_id = Column(Integer, ForeignKey('parent.id'))
        parent = relationship("Parent", back_populates="children")
        name = Column(String(32))

        Append a relationship() method to the child table class
        # And use the relationship.back_populates parameter in the relationship() method of the (parent) child table class

    drop_db()
    init_db()

    child1 = Child(name="zone1")
    child2 = Child(name="zone2")
    parent = Parent(children=[child1, child2])
    session.add(parent)
    session.commit()
    result = session.query(Parent).join(Child).first()
    print(object_as_dict(result.children[0]))

one_to_many()
Copy the code

The results

One to one

Back_populates specifies bidirectional relationships. Uselist =False is simply represented by the uselist argument in the parent table based on a one-to-many relationship

def one_to_one():
    class Parent(Base):
        __tablename__ = 'parent'
        id = Column(Integer, primary_key=True)
        child = relationship("Child", uselist=False, back_populates="parent")

    class Child(Base):
        __tablename__ = 'child'
        id = Column(Integer, primary_key=True)
        parent_id = Column(Integer, ForeignKey('parent.id'))
        parent = relationship("Parent", back_populates="child")
        name = Column(String(32))

    Empty the database and reinitialize it
    drop_db()
    init_db()
    child = Child(name="zone")
    parent = Parent(child=child)
    session.add(parent)
    session.commit()

    result = session.query(Parent).join(Child).first()
    print(object_as_dict(result.child))

one_to_one()
Copy the code

Many to many

Many-to-many relationships add an associated table between the two classes to represent the relationships. The associated table is represented by the secondary parameter in the relationship() method. Typically, this table is associated with the declaration base class via a MetaData object.

def many_to_many():
    association_table = Table('association', Base.metadata,
                              Column('left_id', Integer, ForeignKey('left.id')),
                              Column('right_id', Integer, ForeignKey('right.id'))
                              )

    class Parent(Base):
        __tablename__ = 'left'
        id = Column(Integer, primary_key=True,autoincrement=True)
        children = relationship(
            "Child",
            secondary=association_table,
            back_populates="parents")

    class Child(Base):
        __tablename__ = 'right'
        id = Column(Integer, primary_key=True,autoincrement=True)
        name = Column(String(32))
        parents = relationship(
            "Parent",
            secondary=association_table,
            back_populates="children")

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

    child1 = Child(name="zone1")
    child2 = Child(name="zone2")
    child3 = Child(name="zone3")

    parent = Parent()
    parent2 = Parent()
    # parent add child
    parent.children.append(child1)
    parent.children.append(child2)
    parent2.children.append(child1)
    parent2.children.append(child2)
    # save
    session.add(parent)
    session.add(parent2)
    session.commit()
    # query
    result = session.query(Parent).first()
    print(object_as_dict(result))
    print(object_as_dict(result.children[1]))
    result2 = session.query(Child).first()
    print(object_as_dict(result2))
    print(object_as_dict(result2.parents[1]))
    
many_to_many()
Copy the code

Afterword.

Ok, Python is finished with all three databases. Have you practiced it well?