在 SQLAlchemy 列类型和 python 数据类型之间轻松转换? [英] Easy convert betwen SQLAlchemy column types and python data types?

问题描述

我正在寻找一种简单的 Python 方法来比较 SQLAlchemy 中的列类型与基本类型.例如，如果我的列类型是任意长度的 VARCHAR，我想将其作为字符串读取.

I'm looking for an easy python way to compare column types from SQLAlchemy to base types. For example, if my column type is a VARCHAR of any length, I want to read it as a string.

我可以读取列类型，但是我不确定有什么简单的方法来验证它的基本类型……如果我可以使用类似if isinstance(mycolumn, int)"这样的东西就好了——但是我我是 Python 新手，不确定这会如何工作.

I can read the column type okay, but I'm not sure an easy way to verify it's basic type... it would be nice if I could use something like "if isinstance(mycolumn, int)" - but I'm new to python and not sure how this would work.

这是我目前所拥有的:

from sqlalchemy import MetaData
from sqlalchemy import create_engine, Column, Table
engine = create_engine('mysql+mysqldb://user:pass@localhost:3306/mydb', pool_recycle=3600)
meta = MetaData()
meta.bind = engine
meta.reflect()
datatable = meta.tables['my_data_table']
[c.type for c in datatable.columns]

输出:

[INTEGER(display_width=11), DATE(), VARCHAR(length=127), DOUBLE(precision=None, scale=None, asdecimal=True)]

我的最终目的是双重的，首先是因为我想在将输出加载到我的 jQuery jqGrid 时根据类型格式化输出.第二，我正在慢慢地将非规范化数据表转换为规范化结构，并希望确保我的类型保持一致 - (以确保我在上一个表中的数字存储为数字而不是字符串......)

My end purpose is twofold, first because I want to format the output based on the type when I load it into my jQuery jqGrid. The second, is I'm slowly converting non-normalized data tables into a normalized structure, and want to ensure that I keep my types consistent - (to make sure my numbers in the previous table are stored as numbers and not strings...)

推荐答案

一种解决方案是手动进行转换 - 例如，这有效:

One solution is to do the conversion manually - for example, this works:

def convert(self, saType):
    type = "Unknown"
    if isinstance(saType,sqlalchemy.types.INTEGER):
        type = "Integer"
    elif isinstance(saType,sqlalchemy.types.VARCHAR):
        type = "String"
    elif isinstance(saType,sqlalchemy.types.DATE):
        type = "Date"
    elif isinstance(saType,sqlalchemy.dialects.mysql.base._FloatType):
        type = "Double"
    return type

不确定这是否是正常的 Python 做事方式...我仍然像 Java 程序员一样思考.

Not sure if this is a normal python way of doing things... I still think like a java programmer.

这篇关于在 SQLAlchemy 列类型和 python 数据类型之间轻松转换?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！