Python ORM - pymysql&sqlalchemy

pip3 install pymysql
pip3 install sqlalchemy

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `age` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_name` (`name`),
  KEY `idx_age` (`age`),
  KEY `idx_name_age` (`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

# coding=utf-8
from sqlalchemy import Column, String, Integer
from sqlalchemy.ext.declarative import declarative_base

# ´´½¨¶ÔÏóµÄ»ùÀà:
Base = declarative_base()

# ¶¨ÒåUser¶ÔÏó:
class User(Base):
    # ±íµÄÃû×Ö:
    __tablename__ = 'user'

    # ±íµÄ½á¹¹:
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)
    deleted = Column(Integer)

    def __str__(self):
        return "id: {}, name: {}, age: {}, deleted: {}" \
            .format(self.id, self.name, self.age, self.deleted)

# ´´½¨¼Ç¼
tenmao = User(name = 'tenmao', age = 100)
session.add(tenmao)
session.commit()

# coding=utf-8

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from model.user import User

# Êý¾Ý¿âÅäÖÃ
db_user = 'maibao'
db_password = 'maibao123'
db_host_port = '9.134.77.51:3306'

# ³õʼ»¯Êý¾Ý¿âÁ¬½Ó:
engine = create_engine(
    'mysql+pymysql://{db_user}:{db_password}@{db_host_port}/tenmao'.format(db_user=db_user,
                                                                                       db_password=db_password,
                                                                                       db_host_port=db_host_port))
# ´´½¨DBSessionÀàÐÍ:
DBSession = sessionmaker(bind=engine)

# ´´½¨Session:
session = DBSession()

# ´´½¨Query²éѯ£¬filterÊÇwhereÌõ¼þ£¬×îºóµ÷ÓÃone()·µ»ØΨһÐУ¬Èç¹ûµ÷ÓÃall()Ôò·µ»ØËùÓÐÐÐ:
user = session.query(User).filter(User.id==1).one()

# ´òÓ¡ÀàÐͺͶÔÏóµÄnameÊôÐÔ:
print(user)

# ¹Ø±ÕSession:
session.close()

# ²éѯËùÓÐUser
users = session.query(User).all()

print("there are {} users".format(len(users)))

# Ìõ¼þ²éѯ
users = session.query(User)\
    .filter(User.name == 'timxia')\
    .filter(User.age < 10)\
    .filter(User.name.like('%tim%'))\
    .order_by(User.name.desc(), User.id.asc())\
    .all()

print("there are {} users for given condition".format(len(users)))

Base = declarative_base()
class User(Base):