1、环境
Python 3.8.16
Mysql 5.7.26
SQLAlchemy 2.0.7
pymysql 1.0.2
2、SQLAlchemy insepctor 模块与 MetaData 类
insepctor 检查模块提供 inspect() 函数,它提供有关各种SQLAlchemy对象的运行时信息,包括核心和ORM中的这些对象。为给定目标生成检验对象。
MetaData SQLAlchemy orm 元数据操作
3、 insepctor 模块使用
# inspect 使用
from sqlalchemy import create_engine, inspect
from sqlalchemy.engine import reflection
# 连接数据库名
db_name = 'test'
# 数据库用户名
db_user = 'root'
# 数据库密码
db_passwd = 'root'
# 数据库地址
db_ip = '192.168.10.1'
# 数据库端口
db_port = 3306
engine = create_engine(f'mysql+pymysql://{db_user}:{db_passwd}@{db_ip}:{db_port}/{db_name}?charset=utf8')
# # insp 创建方式一 推荐
insp = inspect(engine)
# insp 创建方式二 该方式将被移除
# insp = reflection.Inspector.from_engine(engine)
schema_names = insp.get_schema_names()
print("schema_names :\n" ,schema_names)
table_names = insp.get_table_names(schema='test') # schema: 数据库名称
print("table_names :\n" , table_names)
columns = insp.get_columns('users', schema='test') # 表名,库名
print("table_columns :\n" , columns)
运行结果
schema_names :
['information_schema', 'admin_db', 'application', 'hippo', 'myapp', 'mysql', 'performance_schema', 'school', 'sys', 'test']
table_names :
['addresses', 'keywords', 'post_keywords', 'posts', 'users']
table_columns :
[{'name': 'id', 'type': INTEGER(display_width=11), 'default': None, 'comment': None, 'nullable': False, 'autoincrement': True}, {'name': 'name', 'type': VARCHAR(collation='utf8_unicode_ci', length=64), 'default': None, 'comment': None, 'nullable': True}, {'name': 'fullname', 'type': VARCHAR(collation='utf8_unicode_ci', length=64), 'default': None, 'comment': None, 'nullable': True}, {'name': 'nickname', 'type': VARCHAR(collation='utf8_unicode_ci', length=64), 'default': None, 'comment': None, 'nullable': True}]
4、 MetaData 类使用
# MetaData 使用
from sqlalchemy import MetaData,create_engine
# 连接数据库名
db_name = 'test'
# 数据库用户名
db_user = 'root'
# 数据库密码
db_passwd = 'root'
# 数据库地址
db_ip = '192.168.10.1'
# 数据库端口
db_port = 3306
engine = create_engine(f'mysql+pymysql://{db_user}:{db_passwd}@{db_ip}:{db_port}/{db_name}?charset=utf8')
metadata = MetaData()
# reflect 映射dash_base库下的表结构
metadata.reflect(schema='test',bind=engine)
tables_info = {i.name:i for i in metadata.tables.values()}
print("tables_info :\n",tables_info)
运行结果
tables_info :
{'addresses': Table('addresses', MetaData(), Column('id', INTEGER(display_width=11), table=<addresses>, primary_key=True, nullable=False), Column('email_address', VARCHAR(collation='utf8_unicode_ci', length=64), table=<addresses>, nullable=False), Column('user_id', INTEGER(display_width=11), ForeignKey('test.users.id'), table=<addresses>), schema='test'), 'users': Table('users', MetaData(), Column('id', INTEGER(display_width=11), table=<users>, primary_key=True, nullable=False), Column('name', VARCHAR(collation='utf8_unicode_ci', length=64), table=<users>), Column('fullname', VARCHAR(collation='utf8_unicode_ci', length=64), table=<users>), Column('nickname', VARCHAR(collation='utf8_unicode_ci', length=64), table=<users>), schema='test'), 'keywords': Table('keywords', MetaData(), Column('id', INTEGER(display_width=11), table=<keywords>, primary_key=True, nullable=False), Column('keyword', VARCHAR(collation='utf8_unicode_ci', length=50), table=<keywords>, nullable=False), schema='test'), 'post_keywords': Table('post_keywords', MetaData(), Column('post_id', INTEGER(display_width=11), ForeignKey('test.posts.id'), table=<post_keywords>, primary_key=True, nullable=False), Column('keyword_id', INTEGER(display_width=11), ForeignKey('test.keywords.id'), table=<post_keywords>, primary_key=True, nullable=False), schema='test'), 'posts': Table('posts', MetaData(), Column('id', INTEGER(display_width=11), table=<posts>, primary_key=True, nullable=False), Column('user_id', INTEGER(display_width=11), ForeignKey('test.users.id'), table=<posts>), Column('headline', VARCHAR(collation='utf8_unicode_ci', length=255), table=<posts>, nullable=False), Column('body', TEXT(collation='utf8_unicode_ci'), table=<posts>), schema='test')}
5、 MetaData 类结合session实现已有数据表查询
方式一:查询前已知表名,使用Table先实例化已有数据表为对象,然后使用session实现查询
# 方式一
from sqlalchemy import MetaData,create_engine
from sqlalchemy import Table
from sqlalchemy.orm import sessionmaker
# 连接数据库名
db_name = 'test'
# 数据库用户名
db_user = 'root'
# 数据库密码
db_passwd = 'root'
# 数据库地址
db_ip = '192.168.10.1'
# 数据库端口
db_port = 3306
engine = create_engine(f'mysql+pymysql://{db_user}:{db_passwd}@{db_ip}:{db_port}/{db_name}?charset=utf8')
# MetaData 实列
metadata = MetaData()
# reflect 映射dash_base库下的表结构
metadata.reflect(schema='test',bind=engine)
# 创建表实例
User = Table('users', metadata, schema='test')
# session 实列
session = sessionmaker(bind=engine)()
rows = [ row for row in session.query(User).all() ]
print('rows_list :\n',rows)
运行结果
rows_list :
[(1, 'ed', 'Ed Jones', 'edsnickname'), (2, 'ed', 'Ed Jones', 'edsnickname'), (3, 'ed', 'Ed Jones', 'edsnickname'), (4, 'ed', 'Ed Jones', 'edsnickname'), (5, 'fred', 'Fred Flintstone', 'freddy'), (6, 'ed', 'Ed Jones', 'edsnickname')]
方式二: 字典的形式将metadata.tables解析出来,方便检索,也不用再使用Table()类,也可以检索其他数据表
# 方式二
from sqlalchemy import MetaData,create_engine
from sqlalchemy.orm import sessionmaker
# 连接数据库名
db_name = 'test'
# 数据库用户名
db_user = 'root'
# 数据库密码
db_passwd = 'root'
# 数据库地址
db_ip = '192.168.10.1'
# 数据库端口
db_port = 3306
engine = create_engine(f'mysql+pymysql://{db_user}:{db_passwd}@{db_ip}:{db_port}/{db_name}?charset=utf8')
# MetaData 实列
metadata = MetaData()
# reflect 映射dash_base库下的表结构
metadata.reflect(schema='test',bind=engine)
# 字典的形式将metadata.tables解析出来,方便检索,也不用再使用Table()类
table_dicts = {i.name: i for i in metadata.tables.values()}
print('table_name_list :\n',table_dicts.keys())
print('table_columns_list :\n', [ i.name for i in table_dicts['users'].columns])
# session 实列
session = sessionmaker(bind=engine)()
rows = [ instance for instance in session.query(table_dicts['users']).all() ]
print('rows_list :\n',rows)
运行结果
table_name_list :
dict_keys(['addresses', 'users', 'keywords', 'post_keywords', 'posts'])
table_columns_list :
['id', 'name', 'fullname', 'nickname']
rows_list :
[(1, 'ed', 'Ed Jones', 'edsnickname'), (2, 'ed', 'Ed Jones', 'edsnickname'), (3, 'ed', 'Ed Jones', 'edsnickname'), (4, 'ed', 'Ed Jones', 'edsnickname'), (5, 'fred', 'Fred Flintstone', 'freddy'), (6, 'ed', 'Ed Jones', 'edsnickname')]