话不多说
目标:为了实现低代码数据视图对接,有必要得到视图所对应物理表及字段名称,字段类型等
1)约束:视图中用到的物理表不能起别名,所以修改上一篇中存储过程建立语句
USE [agui_conn]
GO
/****** Object: StoredProcedure [dbo].[sp_GetOrdersByTimestamp] Script Date: 09/29/2024 14:40:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_GetOrdersByTimestamp]
@Timestamp NVARCHAR(100),
@Condition NVARCHAR(100) -- 假设这是一个额外的条件,如订单状态
AS
BEGIN
IF OBJECT_ID('dbo.vw_OrdersByTimestamp', 'V') IS NOT NULL
BEGIN
EXEC sp_executesql N'DROP VIEW dbo.vw_OrdersByTimestamp';
END
-- 创建一个视图来显示所需的订单信息
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'CREATE VIEW dbo.vw_OrdersByTimestamp AS
SELECT
Users.Username as 用户名,
Orders.OrderTime as 订单时间,
Orders.Amount as 订单金额,
'''+ @Timestamp + ''' AS 查询时间
FROM
Orders
INNER JOIN
Users ON Orders.UserId = Users.UserId
WHERE
(Orders.Status = ''' + @Condition+ ''')'; -- 假设订单表中有一个Status字段o.OrderTime >= ''' + CONVERT(NVARCHAR, @Timestamp, 120) + ''' AND
EXEC sp_executesql @sql;
END
2)在 SQL Server 中,用存储过程创建的视图没有出现在 sys.views
中,尝试查询 sys.objects
来获取所有对象的列表:
SELECT
OBJECT_SCHEMA_NAME(object_id) AS SchemaName,
OBJECT_NAME(object_id) AS ViewName
FROM
sys.objects
WHERE
OBJECTPROPERTY(object_id, 'IsView') = 1;
3)通过sys.objects获得指定视图的描述
SchemaName='dbo'
ViewName='vw_OrdersByTimestamp'
create_view_query = text(f"""SELECT
OBJECT_DEFINITION(OBJECT_ID('{SchemaName}.{ViewName}')) as ViewDefinition
FROM
sys.objects
WHERE
OBJECTPROPERTY(object_id, 'IsView') = 1
AND OBJECT_NAME(object_id) = '{ViewName}'
AND OBJECT_SCHEMA_NAME(object_id) = '{SchemaName}'""")
result = conn.execute(create_view_query)
create_view_result = result.fetchone()
view_creation_sql=create_view_result['ViewDefinition']
print(view_creation_sql)
结果如图:
4)用正则表达式匹配视图字段名和对应的物理表名及字段名
# 正则表达式匹配视图字段名和对应的物理表名及字段名
column_pattern = re.compile(r"(\w+\.\w+) as (\w+)", re.IGNORECASE)
# 提取字段映射
columns_mapping = column_pattern.findall(view_creation_sql)
# 常量值匹配
constant_pattern = re.compile(r"'([^']*)' AS (\w+)", re.IGNORECASE)
constants_mapping = constant_pattern.findall(view_creation_sql)
field_dict = {}
for column, alias in columns_mapping:
field_dict[f'{alias}'] = column
for constant, alias in constants_mapping:
field_dict[f'{alias}'] = None
# 反射视图的列信息
table = Table(view_name, metadata, autoload=True, autoload_with=engine)
for column in table.columns:
v_field = f"{column.name}"
tb_field = field_dict[v_field]
print(f"视图字段名: {column.name}, 对应表字段: {tb_field}, 数据类型: {column.type}")
最终实现结果
到此,祝大家节日快乐!!!