Skip to main content
 首页 » 编程设计

python之SQLAlchemy之从 yaml 或字典创建表

2024年12月31日9pengyingh

有没有办法从 yaml 文件中指定的字典创建动态表?我在 yaml 文件中定义了很多 ETL 配置,所以我很好奇我是否也可以向其中添加表创建方面,这样我就不必在单独的目录中修改单独的 .sql 文件。

database: 
  table: 'schema.fact_stuff' 
  create_columns: [ 
    {} 
  ] #not sure how this section should be 

我在 stackoverflow 上找到了一个解决方案,它将一些列表压缩在一起,这是类似的,但我更愿意明确定义每一列。
{'column_name': 'id', 'column_type': Integer, 'primary_key': False, 'nullable': True} 

我最终让它与这个一起工作:
from sqlalchemy.types import (Integer, NUMERIC, TEXT, BOOLEAN, TIMESTAMP, DATE) 
 
sql_types = {'integer': Integer, 
        'numeric': NUMERIC, 
        'text': TEXT, 
        'date': DATE, 
        'timestamp': TIMESTAMP(timezone=False), 
        'timestamptz': TIMESTAMP(timezone=True)} 
 
exclude_list = ['original_name'] 
table_dict = [{k: v for k, v in d.items() if k not in exclude_list} for d in c[variable]['load']['columns']] 
for column in table_dict: 
    for key, val in column.copy().items(): 
        if key == 'type_': 
            column[key] = sql_types[val] 
        elif key == 'default': 
            column[key] = dt.datetime.utcnow 
 
metadata = sa.MetaData(schema=c[variable]['load']['schema']) 
metadata.reflect(bind=engine, autoload=True) 
fact = sa.Table(c[variable]['load']['table'], metadata, extend_existing=True, 
        *(sa.Column(**kwargs) for kwargs in table_dict)) 
fact.create_all(engine, checkfirst=True) 

但是后来我转移到让 Pandas 确定 dtypes 而不是在 yaml 文件中定义它们。这将使用 jinja2 模板创建 sql,然后我遍历所有数据源以创建 DDL。
def pandas_to_postgres(df): 
    dtype_dict = { 
      'i': 'integer', 
      'O': 'text', 
      'f': 'real', 
      'b': 'boolean', 
      'datetime64[ns]': 'timestamp', 
      'datetime64[ns, UTC]': 'timestampz', 
    } 
    column_list = [] 
    column_dict = {} 
    for k, v in df.dtypes.items(): 
        column_dict['name'] = k 
        column_dict['dtype'] = dtype_dict.get(v.kind, 'text') 
        column_list.append(column_dict.copy()) 
    return column_list 
 
 
def generate_create_table(df, schema, table, table_type, columns, constraint, unique_columns): 
    """ Returns a dictionary of coefs from training """ 
    query = Template( 
        template 
    ).render( 
        schema_name=schema, 
        table_name=table, 
        table_type=table_type, 
        columns=columns, 
        constraint=constraint, 
        constraint_columns=unique_columns 
    ) 
    print(query) 

请您参考如下方法:

今日发布 SQLAthanor (v.0.3.0) 正是支持这一点。使用 SQLAthanor ,您可以以编程方式生成 SQLAlchemy Table具有以下代码的对象(假设 metadata 包含您的 SQLAlchemy MetaData 对象):

from sqlathanor import Table 
 
my_table = Table.from_yaml('yaml_file.yaml',  
                           'my_table_name',  
                           metadata,  
                           primary_key = 'id') 

预计到达时间:请注意,您也可以创建 Table对象使用 Table.from_json() , Table.from_dict() , 和 Table.from_csv()以及。

这是它如何工作的文档(一般): https://sqlathanor.readthedocs.io/en/latest/using.html#generating-sqlalchemy-tables-from-serialized-data

这是指向特定文档的链接 Table.from_yaml()方法: https://sqlathanor.readthedocs.io/en/latest/api.html#sqlathanor.schema.Table.from_yaml

(我建议查看方法文档 - 它涉及从序列化数据以编程方式构建 Table 对象的一些“陷阱”)

预计到达时间:

基本上,程序化的方式 Table生成工作是SQLAthanor:
  • 首先将序列化的字符串(或文件)转换为 Python dict .对于 YAML,默认的反序列化器是 PyYAML .对于 JSON,默认的反序列化器是 simplejson (可以使用 deserialize_function 参数覆盖两个默认解串器)。
  • 曾经是 Python dict已生成,SQLAthanor 读取该 dict 中的每个键确定列名。它读取每个键的值,并根据值的数据类型尝试“猜测”SQLAlchemy 数据类型。
  • 鉴于它在第 2 步中的发现,它创建了一个 Table对象 Column每个对象所在的位置 Column对象对应于反序列化 dict 中的一个键.

  • 如果您需要更精确地控制每个 Column , 你可以:
  • 使用 type_mapping 覆盖其 SQLAlchemy 数据类型参数( type_mapping 接收一个 dict ,其中顶级键对应一个列名,每个值是应用于 Column 的数据类型)
  • 将额外的关键字参数传递给 Column使用 column_kwargs 的构造函数参数( column_kwargs 接收一个 dict,其中顶级键对应于列名,每个值都是一个 dict ,关键字参数将提供给该列的构造函数。

  • 默认情况下, Table.from_<format>()不支持嵌套数据结构。默认情况下, skip_nested设置为 True ,这意味着反序列化 dict 中的一个键包含嵌套对象(可迭代对象或 dict )将被跳过(即不会收到相应的 Column )。如果您的 Table需要存储嵌套数据,可以设置 skip_nested至 False并激活 default_to_str至 True .这会将嵌套数据(可迭代对象或 dict 对象)转换为字符串,从而将它们保存在 Text 中。列(除非被 type_mapping 覆盖)。

    Table.from_dict()示例

    以下是一个例子 dict可以提供给 Table.from_dict() :
    sample_dict = { 
        'id': 123, 
        'some_column_name': 'Some Column Value', 
        'created_datetime': datetime.utcnow() 
    } 
     
    my_table = Table.from_dict(sample_dict,  
                               'my_table',  
                               metadata,  
                               primary_key = 'id') 
    

    当提供给 Table.from_dict() 时此 dict将产生 Table具有数据库表名的对象 my_table包含三列:
  • id类型为 Integer设置为表的主键
  • some_column_name类型为 Text
  • created_datetime类型为 DateTime

  • Table.from_yaml()示例

    以下是相同的示例,但使用了可以提供给 Table.from_yaml() 的 YAML 字符串/文档。 :
    sample_yaml = """ 
        id: 123 
        some_column_name: Test Value 
        created_timestamp: 2018-01-01T01:23:45.67890 
    """ 
     
    my_table = Table.from_yaml(sample_yaml,  
                               'my_table',  
                               metadata,  
                               primary_key = 'id') 
    

    当提供给 Table.from_yaml()这将首先反序列化 sample_yaml成 dict就像前面的例子一样,然后产生一个 Table具有数据库表名的对象 my_table包含三列:
  • id类型为 Integer设置为表的主键
  • some_column_name类型为 Text
  • created_datetime类型为 DateTime

  • 希望这可以帮助!