PGAdmin可正常执行的SQL脚本在SQLAlchemy中触发数据转换错误

背景 #

原本通过PGAdmin4修改数据表，现在重构代码，要把SQLTools里的查询改成用SQLAlchemy执行。因为认为SQLAlchemy不能运行多语句脚本，所以把dim_products表的操作拆成了两个SQL文件。

拆分的SQL文件内容 #

file 00.sql #

ALTER TABLE public.dim_products
ADD COLUMN weight_class varchar(20);

UPDATE public.dim_products
SET removed =
CASE
    WHEN removed = 'Removed' THEN FALSE
        WHEN removed = 'Still_avaliable' THEN TRUE
END,
product_price = REPLACE(product_price, '£', ''),
weight = REPLACE(weight, 'kg', '');

ALTER TABLE public.dim_products
RENAME COLUMN removed to still_available;

UPDATE public.dim_products
SET weight_class =
CASE
    WHEN weight::float < 2 THEN 'Light'
    WHEN weight::float >= 2 AND weight::float < 41 THEN 'Mid_Sized'
    WHEN weight::float >= 41 AND weight::float < 141 THEN 'HEAVY'
    WHEN weight::float >= 141 THEN 'Truck_Required'
END;

ALTER TABLE public.dim_products
RENAME COLUMN product_price to product_price_in_£;

ALTER TABLE public.dim_products
RENAME COLUMN weight to weight_in_kg;

file 01.sql #

ALTER TABLE public.dim_products
ALTER COLUMN product_price_in_£ TYPE FLOAT USING product_price_in_£::float,
ALTER COLUMN weight_in_kg TYPE FLOAT USING weight_in_kg::float,
ALTER COLUMN "EAN" TYPE VARCHAR(14) USING "EAN"::varchar(14),
ALTER COLUMN product_code TYPE VARCHAR(14) USING product_code::varchar(14),
ALTER COLUMN date_added TYPE DATE USING date_added::date,
ALTER COLUMN uuid TYPE UUID USING uuid::uuid,
ALTER COLUMN still_available TYPE BOOL using still_available::bool,
ALTER COLUMN weight_class TYPE VARCHAR(20) USING weight_class::varchar(20),
ADD PRIMARY KEY (product_code);

错误信息 #

执行时持续触发以下错误：

sqlalchemy.exc.DataError: (psycopg2.errors.InvalidTextRepresentation) invalid input syntax for type double precision: "£3.00"

当前执行代码 #

def main():
    # 从各处爬取数据初始化多个表
    user_table_init()
    card_details_table_init()
    store_details_init()
    datetime_table_init()
    product_details_init()
    order_table_init()
    
    database_engine = database_utils.create_engine('postgresql://postgres:USERNAME@localhost:5432/Sale_Data')
    
    with database_engine.begin() as con:
        for file in os.listdir('Database-structure'): # 存放所有SQL文件的目录
            if file.endswith(".sql"):
                with open(os.path.join('Database-structure', file)) as alter_statements_sql:
                    query = text(alter_statements_sql.read()) # 部分文件包含多语句
                    
                    print(file)
                    con.execute(query) # 曾了解到MySQL可设置Multi=true执行多语句，但PostgreSQL无此功能
                    print('done')
                    
                    
        for file in os.listdir('Database-structure\Database-schema'):
            if file.endswith(".sql"):
                with open(os.path.join('Database-structure\Database-schema', file)) as relationships_statements_sql:
                    query = text(relationships_statements_sql.read())
                    con.execute(query)

问题总结 #

同一SQL脚本在PGAdmin中可正常运行，但通过SQLAlchemy执行时触发数据转换错误。推测原因是PostgreSQL的SQLAlchemy不允许从单个SQL文件执行多语句，导致REPLACE语句被跳过，转换列类型时仍存在带£符号的字符串。

内容的提问来源于stack exchange，提问作者j stevenage