sqlalchemy使用

from ast import Delete
from operator import or_
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
import json
#地址
HOSTNAME = ''
#端口
PORT = '4322'
#用户名
USERNAME = ''
#密码
PASSWORD = ''
#数据库名
DATABASE = ''

DB_URL = 'mysql+pymysql://{}:{}@{}:{}/{}'.format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)

#创建数据库引擎
engine = create_engine(DB_URL)

# SQLAlchemy 操作数据库，需要引入另外一个对象 Session。
# Session 建立与数据库的会话 (conversation)，可以将其想象成对象的容器，
# 包含的对象叫 identity map 的结构，identity map 的作用就是保证对象的唯一性。
# 另外，Session 对 Python 对象进行状态管理
# Session = sessionmaker(bind=engine)
# session = Session()
from models2 import *
# ------------------select语句------------------
# 单表查询常见的有两种方式，第一种方式是将 model 类作为参数传递给 query() 方法
# 获取全部信息
def test_query1():
    obj_list1 = engine.session.query(Clb_list_media_proxy).all()
    print(len(obj_list1)) #3475

test_query1()
# # 只需要获取部分字段
# def test_query2():
#     obj_list2 = session.query(Clb_list_media_proxy.clb_ip).all()
#     print(len(obj_list2)) #3475
# # Query 对象提供了 filter() 方法和 filter_by() 方法用于数据筛选
# # filter_by() 适用于简单的基于关键字参数的筛选。 filter() 适用于复杂条件的表达。
# def test_filter_le():
#     clb = session.query(Clb_list_media_proxy).filter_by(clb_ip = '1.13.151.144').first()
#     print(clb)

# def test_filter_ne():
#     clb = session.query(Clb_list_media_proxy).filter(Clb_list_media_proxy.clb_ip.like('1.13.151.%')).first()
#     print(clb)
# def test_filter_in():
#     clb = session.query(Clb_list_media_proxy).filter(Clb_list_media_proxy.clb_ip.in_(['1.13.151.144', '1.13.151.155'])).first()
#     print(clb)

# def test_filter_notin():
#     clb = session.query(Clb_list_media_proxy).filter(~Clb_list_media_proxy.clb_ip.in_(['1.13.151.144', '1.13.151.155'])).first()
#     print(clb)

# def test_filter_isnull():
#     clb = session.query(Clb_list_media_proxy).filter(Clb_list_media_proxy.clb_ip == None).first()
#     print(clb)

# def test_filter_isnotnull():
#     clb = session.query(Clb_list_media_proxy).filter(Clb_list_media_proxy.clb_ip != None).first()
#     print(clb)

# def test_filter_and():
#     clb = session.query(Clb_list_media_proxy).filter(Clb_list_media_proxy.clb_ip != None, Clb_list_media_proxy.clb_ip.like('1.13.151.%')).first()
#     print(clb)

# def test_filter_or():
#     clb = session.query(Clb_list_media_proxy).filter(or_(Clb_list_media_proxy.clb_ip != None, Clb_list_media_proxy.clb_ip.like('1.13.151.%'))).first()
#     print(clb)

# # ------------------insert语句------------------
# def test_add_obj():
#     # 创建一个行对象
#     obj = Clb_list_media_proxy(
#         clb_ip = '0',
#         clb_origin_region = '0',
#         clb_current_region = '0',
#         clb_isp_name = '0',
#         clb_vpc = '0',
#         bind_num = 0,
#         user = '0',
#         cluster_type = None,
#         clb_id = '0',
#         timestamp = None
#     )
#     session.add(obj)
#     session.commit()

# # ------------------update语句------------------
# def test_update_obj():
#     obj = session.query(Clb_list_media_proxy).filter(Clb_list_media_proxy.clb_id == '0').first()
#     print(obj.bind_num)
#     obj.bind_num = 1
#     session.commit()

# # ------------------delete语句------------------
# def test_delete_obj():
#     obj = session.query(Clb_list_media_proxy).filter(Clb_list_media_proxy.clb_id == '0').first()
#     session.delete(obj)
#     session.commit()

# # 多表操作

# # 参考文档
# # https://docs.sqlalchemy.org/en/14/tutorial/index.html

或者