I am trying to load a big list of sql queries into a table in Vertica using PYODBC. Here's my code:
tablename = DVXTEMP.my_table_name
sql = my_sql_query.strip().strip(';')
samplesize = 1000
createstring = 'CREATE TABLE %s AS %s \n limit %s;' %(tablename, sql, samplesize)
cursor.execute(createstring)
when I print createstring and run it in Toad, it works fine. when I try to execute it in pyodbc, it gives me the following error:
'Syntax error at or near "DVXTEMP" at character 1\n (4856) (SQLExecDirectW)'
We are using Vertica Analytic Database v7.1.2-6
Any ideas what might be causing this?
Thanks
1) did you import pyodbc?
2) did you define "cursor" from "pyodbc.connect"?
import pyodbc
DB = '[string for dbfile]'
DRV = '[string of which driver you are going to use]'
con = pyodbc.connect('DRIVER={};DBQ={}'.format(DRV,DB))
cursor = con.cursor()
##build SQL code and execute as you have done
Try SQL commands after you can connect without an error.
3) I use pyodbc for mdb files (MS Access) and some of my queries will not run unless I put single quotes outside double quotes on table/field names.
mytbl_1 = "mytbl"
SQL = 'SELECT * FROM ' + mytbl_1
print SQL
print result -> SELECT * FROM mytbl
(this fails)
mytbl_2 = '"mytbl"' #single quotes outside of double quote
SQL = 'SELECT * FROM ' + mytbl_2
print SQL
print result -> SELECT * FROM "mytbl"
(this string gets passed w/o error works for me with MDB files)