Python Pandas使用to_sql()、SQLAlchemy和Exasol中的schema

Question

我正在尝试把一个 pandas 数据框上传到 SQL 表里。看起来 pandas 的 to_sql 函数是处理大数据框的最佳解决方案，但我就是搞不定它。我可以轻松提取数据，但在尝试写入新表时却出现了错误信息：

# connect to Exasol DB
exaString='DSN=exa'
conDB = pyodbc.connect(exaString)   

# get some data from somewhere, works without error
sqlString = "SELECT * FROM SOMETABLE"
data = pd.read_sql(sqlString, conDB)

# now upload this data to a new table
data.to_sql('MYTABLENAME', conDB, flavor='mysql')

conDB.close()

我收到的错误信息是

pyodbc.ProgrammingError: ('42000', "[42000] [EXASOL][EXASolution driver]语法错误，意外的标识符_chain2，期待赋值操作符或 ':' [第 1 行，第 6 列] (-1) (SQLExecDirectW)")

不幸的是，我不知道导致这个语法错误的查询是什么样的，或者还有什么其他问题。有人能给我指个方向吗？

（第二次）编辑：

根据 Humayun 和 Joris 的建议，我现在使用的是 Pandas 0.14 版本和 SQLAlchemy，并结合 Exasol 方言（？）。因为我连接的是一个定义好的模式，所以我使用了元数据选项，但程序崩溃了，显示“总线错误（核心转储）”。

engine = create_engine('exa+pyodbc://uid:passwd@exa/mySchemaName', echo=True)    

# get some data
sqlString = "SELECT * FROM SOMETABLE"    # SOMETABLE is a view in mySchemaName 
df = pd.read_sql(sqlString, con=engine)  # works

print engine.has_table('MYTABLENAME')    # MYTABLENAME is a view in mySchemaName
# prints "True"

# upload it to a new table
meta = sqlalchemy.MetaData(engine, schema='mySchemaName')
meta.reflect(engine, schema='mySchemaName')
pdsql = sql.PandasSQLAlchemy(engine, meta=meta)
pdsql.to_sql(df, 'MYTABLENAME')

我不太确定在 create_engine(..) 中设置 "mySchemaName" 是否正确，但结果是一样的。

Answer 1

问题是，在pandas 0.14版本中，read_sql和to_sql这两个功能无法处理数据库的模式（schemas），而在使用exasol时不考虑模式是没有意义的。这个问题将在0.15版本中修复。如果你想现在就使用，可以看看这个拉取请求 https://github.com/pydata/pandas/pull/7952

Answer 2

Pandas 默认不支持 EXASOL 的语法，所以需要稍微修改一下。下面是你代码的一个可运行示例，不需要用到 SQLAlchemy：

import pyodbc
import pandas as pd

con = pyodbc.connect('DSN=EXA')
con.execute('OPEN SCHEMA TEST2')

# configure pandas to understand EXASOL as mysql flavor
pd.io.sql._SQL_TYPES['int']['mysql'] = 'INT'
pd.io.sql._SQL_SYMB['mysql']['br_l'] = ''
pd.io.sql._SQL_SYMB['mysql']['br_r'] = ''
pd.io.sql._SQL_SYMB['mysql']['wld'] = '?'
pd.io.sql.PandasSQLLegacy.has_table = \
    lambda self, name: name.upper() in [t[0].upper() for t in con.execute('SELECT table_name FROM cat').fetchall()]

data = pd.read_sql('SELECT * FROM services', con)
data.to_sql('SERVICES2', con, flavor = 'mysql', index = False)

如果你使用 EXASolution 这个 Python 包，那么代码会像下面这样：

import exasol
con = exasol.connect(dsn='EXA') # normal pyodbc connection with additional functions
con.execute('OPEN SCHEMA TEST2')

data = con.readData('SELECT * FROM services') # pandas data frame per default
con.writeData(data, table = 'services2')