如何轻松转换SQLAlchemy列类型和Python数据类型？

Question

我想找一种简单的方法，用Python来比较SQLAlchemy中的列类型和基本类型。比如说，如果我的列类型是任意长度的VARCHAR，我希望把它当作字符串来处理。

我能读取列的类型，但不太确定怎么简单地验证它的基本类型……如果我能用类似“if isinstance(mycolumn, int)”这样的方式就好了，但我对Python还不太熟悉，不知道该怎么做。

这是我目前的代码：

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时，根据类型来格式化输出。其次，我正在慢慢把非规范化的数据表转换成规范化的结构，想确保我的数据类型保持一致——（也就是确保之前表格中的数字是以数字的形式存储，而不是字符串……）

Answer 1

Python类型与SQL类型的对应关系：

我在创建SQL表时遇到了一些麻烦，特别是需要动态生成默认的SQL类型。最后，我写了一些方便的函数，解决了Python类型和SQL类型之间的转换问题。将SQL类型转换为Python类型很简单，下一节会详细说明。

import sqlalchemy
import numpy as np

import datetime
import decimal

_type_py2sql_dict = {
 int: sqlalchemy.sql.sqltypes.BigInteger,
 str: sqlalchemy.sql.sqltypes.Unicode,
 float: sqlalchemy.sql.sqltypes.Float,
 decimal.Decimal: sqlalchemy.sql.sqltypes.Numeric,
 datetime.datetime: sqlalchemy.sql.sqltypes.DateTime,
 bytes: sqlalchemy.sql.sqltypes.LargeBinary,
 bool: sqlalchemy.sql.sqltypes.Boolean,
 datetime.date: sqlalchemy.sql.sqltypes.Date,
 datetime.time: sqlalchemy.sql.sqltypes.Time,
 datetime.timedelta: sqlalchemy.sql.sqltypes.Interval,
 list: sqlalchemy.sql.sqltypes.ARRAY,
 dict: sqlalchemy.sql.sqltypes.JSON
}

def type_py2sql(pytype):
    '''Return the closest sql type for a given python type'''
    if pytype in _type_py2sql_dict:
        return _type_py2sql_dict[pytype]
    else:
        raise NotImplementedError(
            "You may add custom `sqltype` to `"+str(pytype)+"` assignment in `_type_py2sql_dict`.")

def type_np2py(dtype=None, arr=None):
    '''Return the closest python type for a given numpy dtype'''

    if ((dtype is None and arr is None) or
        (dtype is not None and arr is not None)):
        raise ValueError(
            "Provide either keyword argument `dtype` or `arr`: a numpy dtype or a numpy array.")

    if dtype is None:
        dtype = arr.dtype

    #1) Make a single-entry numpy array of the same dtype
    #2) force the array into a python 'object' dtype
    #3) the array entry should now be the closest python type
    single_entry = np.empty([1], dtype=dtype).astype(object)

    return type(single_entry[0])

def type_np2sql(dtype=None, arr=None):
    '''Return the closest sql type for a given numpy dtype'''
    return type_py2sql(type_np2py(dtype=dtype, arr=arr))

一些使用场景：

>>> sqlalchemy.Column(type_py2sql(int))
Column(None, BigInteger(), table=None)

>>> type_py2sql(type('hello'))
sqlalchemy.sql.sqltypes.Unicode

>>> type_np2sql(arr=np.array([1.,2.,3.]))
sqlalchemy.sql.sqltypes.Float

我如何选择我的转换集合：

我做的就是把所有的SQL类型和它们对应的Python类型一一对应起来。然后我打印出每种Python类型对应哪些SQL类型，并为每种Python类型选择了最合适的SQL类型。下面是我用来生成这个对应关系的代码：

#********** SQL to Python: one to one **********
type_sql2py_dict = {}
for key in sqlalchemy.types.__dict__['__all__']:
    sqltype = getattr(sqlalchemy.types, key)

    if 'python_type' in dir(sqltype) and not sqltype.__name__.startswith('Type'):
        try:
            typeinst = sqltype()
        except TypeError as e: #List/array wants inner-type
            typeinst = sqltype(None)

        try:
            type_sql2py_dict[sqltype] = typeinst.python_type
        except NotImplementedError:
            pass

#********** Python to SQL: one to many **********
type_py2sql_dict = {}
for key, val in type_sql2py_dict.items():
    if not val in type_py2sql_dict:
        type_py2sql_dict[val] = [key]
    else:
        type_py2sql_dict[val].append(key)

这是在sqlalchemy版本1.3.5下，type_py2sql_dict的输出结果：

{int: [sqlalchemy.sql.sqltypes.INTEGER,
  sqlalchemy.sql.sqltypes.BIGINT,
  sqlalchemy.sql.sqltypes.SMALLINT,
  sqlalchemy.sql.sqltypes.Integer,
  sqlalchemy.sql.sqltypes.SmallInteger,
  sqlalchemy.sql.sqltypes.BigInteger],
 str: [sqlalchemy.sql.sqltypes.CHAR,
  sqlalchemy.sql.sqltypes.VARCHAR,
  sqlalchemy.sql.sqltypes.NCHAR,
  sqlalchemy.sql.sqltypes.NVARCHAR,
  sqlalchemy.sql.sqltypes.TEXT,
  sqlalchemy.sql.sqltypes.Text,
  sqlalchemy.sql.sqltypes.CLOB,
  sqlalchemy.sql.sqltypes.String,
  sqlalchemy.sql.sqltypes.Unicode,
  sqlalchemy.sql.sqltypes.UnicodeText,
  sqlalchemy.sql.sqltypes.Enum],
 float: [sqlalchemy.sql.sqltypes.FLOAT,
  sqlalchemy.sql.sqltypes.REAL,
  sqlalchemy.sql.sqltypes.Float],
 decimal.Decimal: [sqlalchemy.sql.sqltypes.NUMERIC,
  sqlalchemy.sql.sqltypes.DECIMAL,
  sqlalchemy.sql.sqltypes.Numeric],
 datetime.datetime: [sqlalchemy.sql.sqltypes.TIMESTAMP,
  sqlalchemy.sql.sqltypes.DATETIME,
  sqlalchemy.sql.sqltypes.DateTime],
 bytes: [sqlalchemy.sql.sqltypes.BLOB,
  sqlalchemy.sql.sqltypes.BINARY,
  sqlalchemy.sql.sqltypes.VARBINARY,
  sqlalchemy.sql.sqltypes.LargeBinary,
  sqlalchemy.sql.sqltypes.Binary],
 bool: [sqlalchemy.sql.sqltypes.BOOLEAN, sqlalchemy.sql.sqltypes.Boolean],
 datetime.date: [sqlalchemy.sql.sqltypes.DATE, sqlalchemy.sql.sqltypes.Date],
 datetime.time: [sqlalchemy.sql.sqltypes.TIME, sqlalchemy.sql.sqltypes.Time],
 datetime.timedelta: [sqlalchemy.sql.sqltypes.Interval],
 list: [sqlalchemy.sql.sqltypes.ARRAY],
 dict: [sqlalchemy.sql.sqltypes.JSON]}

Answer 2

只需使用所有AQLAlchemy类型中都可以找到的 python_type 属性：

[c.type.python_type for c in datatable.columns]

Answer 3

一种解决办法是手动进行转换，比如说，下面这个方法就可以用：

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程序员的思维方式。