我尝试通过pandas和sqlalchemy将我的vertica(SQL类型)表附加到数据库中。
import pandas as pd
import sqlalchemy as sa
创建适用于Vertica的引擎:
def get_engine(base):
engine = sa.create_engine("{sys}+{dri}://{user}:" + \
"{password}@{host}:{port}/{database}".format(**login[base]))
return engine
engine = get_engine('vertica')
为了更清晰地理解,这是一个简单的查询:
table = '***'
sql =\
'''
select *
from public.{table}
'''.format(table=table)
connection = engine.connect()
data = pd.read_sql(sql, connection)
connection.close()
数据不为空:
print(len(data))
569955
并尝试写入相同的表:
fields = list(data.columns)
connection = engine.connect()
data.to_sql(table, connection, schema='public', index=False, if_exists='append', chunksize=30000,
dtype={fields[0]:sa.types.Integer,
fields[1]:sa.types.VARCHAR,
fields[2]:sa.types.Integer,
fields[3]:sa.types.Integer,
fields[4]:sa.types.Integer,
fields[5]:sa.types.VARCHAR,
fields[6]:sa.types.VARCHAR,
fields[7]:sa.types.VARCHAR,
fields[8]:sa.types.VARCHAR,
fields[9]:sa.types.VARCHAR,
fields[10]:sa.types.VARCHAR,
fields[11]:sa.types.VARCHAR,
fields[12]:sa.types.DateTime
})
connection.close()
并解决这个错误:
...
\Anaconda3\lib\site-packages\sqlalchemy\engine\default.py in do_executemany(self, cursor, statement, parameters, context)
465
466 def do_executemany(self, cursor, statement, parameters, context=None):
--> 467 cursor.executemany(statement, parameters)
468
469 def do_execute(self, cursor, statement, parameters, context=None):
\Anaconda3\lib\site-packages\vertica_python\vertica\cursor.py in executemany(self, operation, seq_of_parameters)
153 else:
154 raise NotImplementedError(
--> 155 "executemany is implemented for simple INSERT statements only")
156
157 def fetchone(self):
NotImplementedError: executemany is implemented for simple INSERT statements only
login['vertica']['sys']
和login['vertica']['dri']
是什么?不熟悉Vertica,因此希望它们被明确提到。可能是vertica+pyodbc
吗? - Ilja Everilälogin = {'vertica': {"sys":"vertica", "dri":"vertica_python", "database":"***", "user":"***", "password":"***", "host":"***", "port":"5433"}, }
- Ivan SavinCursor.executemany()
,所以也许您只需要升级。Pandas支持明确提到在(关闭的)关于缺失方法的问题中。 - Ilja Everilävertica_python
的哪个版本可用了吗? - lv10