使用Python导出和导入Vertica查询结果

1. 你可以用它 COPY FROM VERTICA 对于简单的问题。阅读 here
2. 对于python,您可以在我的模板中使用:

:

• python=2.7.x版
• vertica python==0.7.3
• Vertica分析数据库v8.1.1-10

源代码示例

#!/usr/bin/env python2
# coding: UTF-8

import csv
import cStringIO

# connection info: username, password, etc
SRC_DB_INFO = {...}
DST_DB_INFO = {...}

csvbuffer = cStringIO.StringIO()
csvwriter = csv.writer(csvbuffer, delimiter='|', lineterminator='\n', quoting=csv.QUOTE_MINIMAL)

# establish connection to source database
connection = vertica_python.connect(**SRC_DB_INFO)
cursor = connection.cursor()
cursor.execute('SELECT * FROM A')

# convert data to csv format
for row in cursor.iterate():
    csvwriter.writerow(row)

# cleanup 
cursor.close()
connection.close() 

# establish connection to destination database
connection = vertica_python.connect(**DST_DB_INFO)
cursor = connection.cursor()

# copy data
cursor.copy('COPY B FROM STDIN ABORT ON ERROR', csvbuffer.getvalue())
connection.commit()

# cleanup 
cursor.close()
connection.close()