使用sqlalchemy模块获取数据库已有表的表名及字段名

最近使用Python里ORM模块，例如SQLalchemy来读取数据库中的表及相关数据，刚接触时，大多数时候是对着网上其他博主或者官方的案例进行模仿，针对自己需要操作的数据库对代码做些微调，进而使用过程中稍有些心得，特写下此篇仅作为经验分享。

/ 模块及数据库

• windows 10

• python 3.7

• sqlalchemy 1.3.13

• mysql-connector 8.0.28

• mysql 8.0.18

/ 简介

通过对比操作mysql下的sql语法及orm语法学习在读取数据表时的异同点。

/ mysql语法

连接数据库：

使用命令行进入数据库，如果没有将mysql.exe设置在系统环境中，请自行切换命令行路径至mysql.exe所在路径，Ctrl+R后再输入cmd进入命令行。

mysql -u root -p
Enter password: password

显示所有数据库：

show databases;

使用数据库：

use database;  -- database: 输入自己的数据库名

查询数据表的数据：

select * from table; -- table: 目标数据表

以上就是使用sql语法使用数据库查询当前表下的数据表的数据方式。

/ ORM(SQLalchemy)方式

使用sqlalchemy时，在当前库没有对应表的情况下会新建立一个表模型类，以此来作为查询条件，当然在此篇中已说明是存在表的情况下，固然可以不建立模型类就能完成获取数据表的操作。

这次使用的时候发现用pymysql作为连接器总有警告，换成mysqlconnector就好了。

pip install mysql-connector-python

建立表模型类

from sqlalchemy import create_engine, MetaData, Column
from sqlalchemy.dialects.mysql import INTEGER, DOUBLE, BIGINT, VARCHAR, CHAR, TEXT, DATETIME
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# 创建数据库引擎，ps:这里并没有连接具体数据库
engine = create_engine("mysql+mysqlconnector://root:password@localhost:3306", pool_recycle=7200)
# 创建基类, 使用MetaData模块构建库模型元数据类
Base = declarative_base(bind=engine, metadata=MetaData(schema='dash_base'))

# 使用sessionmaker保持数据库会话连接
session = sessionmaker(bind=engine)()

class Tips(Base):
    """继承基类，对表中具体列名或者格式进行设计"""
    __tablename__ = 'tips'
    idx = Column('idx', BIGINT(20), primary_key=True)
    total_bill = Column('total_bill', DOUBLE())
    tip = Column('tip', DOUBLE())
    sex = Column('sex', TEXT())
    smoker = Column('smoker', TEXT())
    day = Column('day', TEXT())
    time = Column('time', TEXT())
    size = Column('size', BIGINT(20))

查询所有数据

[tips.__dict__ for tips in session.query(Tips).all()]

查询部分列数据

session.query(Tips.total_bill, Tips.tip).all()

有时候会结合pandas一起使用。

import pandas as pd

# table名，con：参数的链接要具体到数据库名
pd.read_sql(Tips.__tablename__, con="mysql+mysqlconnector://root:password@localhost:3306/dash_base")

在上面这段代码里在理解上没有什么难度，主要是在每次都需要手动敲入表模型类，对于已经存在的表再次这样写不免会觉得麻烦，如果表字段增多，要查询的表数量增多，就会想去寻找更为优质的解决办法，当然是有的，请继续往下看。

使用 insepctor 模块与 MetaData 类

insepctor创建有两种方式：

• 方式一

from sqlalchemy.engine import reflection

engine = create_engine("mysql+mysqlconnector://root:password@localhost:3306", pool_recycle=7200)
insp = reflection.Inspector.from_engine(engine)

• 方式二

from sqlalchemy import create_engine, inspect, MetaData, Table
from sqlalchemy.orm import sessionmaker

engine = create_engine("mysql+mysqlconnector://root:password@localhost:3306", pool_recycle=7200)
insp = inspect(engine)

获取数据库名

insp.get_schema_names()

获取当前数据库下的表名

insp.get_table_names(schema='dash_base')  # schema: 数据库名称

获取当前数据表的列名

inspect_resp.get_columns('tips', schema='dash_base')  # 表名，库名

name值就是列名，type值就是列的类型。但是这种获取方式对于查询表中数据是没有帮助的，原因是不能用str类型作为session.query()的对象。

接下来使用MetaData类连接数据库映射出库中的表结构。

在上一步获取到库名，这里选择一个库名填入到schema参数中。

metadata = MetaData(bind=engine)
# reflect 映射dash_base库下的表结构
metadata.reflect(schema='dash_base')

通过这段代码后，可以使用其下的tables方法获取表名及表结构：

表名：

表结构：

也可以使用Table类新建一个表结构：

from sqlalchemy import Table

Table('tips', metadata, schema='dash_base')

我是推荐使用字典的形式将metadata.tables解析出来，方便检索，也不用再使用Table()类。

table_dicts = {i.name: i for i in metadata.tables.values()}

再通过具体表获取其中的字段名：

table_dicts['tips'].c.keys()

# ['idx', 'total_bill', 'tip', 'sex', 'smoker', 'day', 'time', 'size']

这样获取的字段名使用session.query查询是一个列表数据，而不是迭代器。

额外说一句，可以使用Base.metadata代替MetaData，使用效果一样。

总结

每次使用一个模块都会多一点感想，及时记录才会更好的理解并运用这个模块吧，像起初我每次想要通过该模块进行查询都要建立表模型类，还一边吐槽这个模块设计的不合理，如果不知道表结构的具体设计该如何查询，通过经验的积累，慢慢地感受到orm模型设计是如此健壮，如此方便。

👆点击关注｜设为星标｜干货速递👆