Python - MySQL of Python operation database

Python - MySQL of Python operation database (1)

1, About MySQL:

MySQL is a relational database management system, developed by MySQLAB company in Sweden, which is a product of Oracle. MySQL is one of the most popular relational database management systems. In WEB applications, MySQL is the best RDBMS(Relational Database Management System,

One of the application software of RDBMS;

The SQL(Structured Query Language) used by MySQL is a structured query language between relational algebra and relational calculus, which was proposed by Boyce and Chamberlin in 1974. It is a general and powerful relational database language. )Language is the most commonly used standard for accessing database

Language. MySQL software adopts the dual authorization policy, which is divided into community version and commercial version. Due to its small size, fast speed and low total cost of ownership, especially the characteristics of open source, MySQL is generally selected as the website database for the development of small and medium-sized websites;


2, MySQL installation and use:

The available versions are MySQL community version and MariaDB version. The download address of MySQL is , the download address of MariaDB is , select the appropriate version to download and install;

For the relationship between MariaDB and MySQL, please refer to


3, How MySQL works:

1. Storage engine?

The default program for reading and writing data in the table is InnoDB (other MyISAM and Memory), which stores the data in the table to disk;

Taking pages as the basic unit of interaction between disk and memory, the size of pages in InnoDB is generally 16KB;

Refresh at least 16KB of memory to disk at a time, and each line has a fixed format;




Figure 1 location of data engine

2. Concept of transaction:

What is a transaction

The whole process of operating database from reading to writing, a group of atomic sql queries, or an independent unit of work.

Features of transaction:






4, Using Python to operate MySQL database -- SQLAIchemy:


Python SQL toolkit and object relational mapper;

SQLAlchemy is a Python SQL toolkit and object relational mapper, which provides all the functions and flexibility of SQL for application developers;

It provides a set of well-known enterprise persistence pattern, which is designed for efficient and high-performance database access, and is suitable for simple and Python domain languages;

By Mike Bayer blog:


2. Use ideas of sqlaichemy (Introduction to main functions):

Declarative ABCD base() - > create base class

Class - > data class - > table

Create engine - > Database Engine connect to database

Session - > create session, similar to transaction

Object - > create data pair

Query - > various query operations


3.Python operation:

Here is a simple case of many to many relationship between host and administrator:


pip install SQLAIchemy installation Library


#!/usr/bin/env python
# -*- coding:utf-8 -*-
# Author:catt1e

from sqlalchemy import create_engine, Column ,Integer ,String, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import and_
from sqlalchemy import or_

Base = declarative_base()

class Admin(Base):
    '''Definition Admin Table and its field name'''
    __tablename__= 'admin'

    id = Column('id', Integer, primary_key=True)
    name = Column('name', String(50), unique=True)

    hosts = relationship(

    def __repr__(self):

class Host(Base):
    '''Definition Host Table and its field name'''
    __tablename__= 'host'

    id = Column('id', Integer, primary_key=True)
    name = Column('name', String(50), unique=True, nullable=False)

    admins = relationship(

    def __repr__(self):

class AdminHost(Base):
    '''Definition AdminHost Table and its field name'''
    __tablename__ = 'admin_host'
    admin_id = Column(Integer, ForeignKey(''), primary_key=True)
    host_id = Column(Integer, ForeignKey(''), primary_key=True)

# engine = create_engine('mysql+pymysql://username:password@localhost/db')
engine = create_engine('mysql+pymysql://Root: pwdfordb826 @ localhost / cat1e? Charset = utf8 ') (engine)
Base.metadata.create_all(bind=engine) # When creating a database, note that you need to annotate it after the first creation. Otherwise, an error will be reported when you operate the database

# session
DBSession = sessionmaker(bind=engine) # class
session = DBSession() # Example

def add_admin(session, id, name):
    '''Add administrator'''
    s = Admin() = id = name
    return s

def main():
    # Add data
    # admin = add_admin(session, 4, 'admin_catt1e')
    # h1 = Host(id=1100, name='webweb.server')
    # h2 = Host(id=2200, name='dbdb.server')
    # h3 = Host(id=3300, name='devdev.server')
    # admin.hosts.append(h1)
    # admin.hosts.append(h2)
    # admin.hosts.append(h3)
    # session.add(admin)
    # session.commit()

    # Query data
    print('all: ', session.query(Admin).all())
    print('admin catt1e: ', session.query(Admin).filter('catt1e')).one())
    print('admin catt1e hosts: ', session.query(Admin).filter('catt1e')).one().hosts)
    print('dbdb.server Who is the administrator of? Start from the user list: ', session.query(Admin).filter(Admin.hosts.any('dbdb.server')).all())

if __name__ == '__main__':

Tags: Python MySQL Database Session

Posted on Tue, 24 Mar 2020 06:00:01 -0700 by imurkid