Python Class to return column names of a table
Posted: Wed Oct 17, 2012 8:36 pm
First of all, we are VERY new to Vertica and Python. We have been running Vertica since August for a very limited process and we are now trying to expand the processing. I have created a class to return the rows in a table :
#########################################################
import pyodbc
class Table:
def __init__(self, db, name):
self.db = db
self.name = name
self.dbc = self.db.cursor()
def __getitem__(self, item):
self.dbc.execute("select * from %s offset %s;" % (self.name, item))
return self.dbc.fetchone()
def __len__(self):
self.dbc.execute("select count(*) from %s" % (self.name))
l = int(self.dbc.fetchone() [0])
return l
cnstr = "DSN=xxxxxx;PWD=xxxxxxxx;Charset=UTF-8"
targetSchema = 'public'
cnxn = pyodbc.connect(cnstr, autocommit=False)
##########################################################################
And this works without a problem. Now, I am trying to use the same type of structure to retrieve the columns from a table (column_name from columns...) but it fails. In this example I am only trying to retrieve the number of columns (if I hard code the table name it works fine!!!). I have run the query string in a cursor.execute and in vsql and it works.
################################################################################
import pyodbc
class Table:
def __init__(self, db, name):
self.db = db
self.name = name
self.dbc = self.db.cursor()
def __len__(self):
self.dbc.execute("select count(*) from columns where table_schema = 'public' and table_name = %s;" % (self.name))
l = int(self.dbc.fetchone() [0])
return l
cnstr = "DSN=xxxxxx;PWD=xxxxxxxxx;Charset=UTF-8"
targetSchema = 'public'
cnxn = pyodbc.connect(cnstr, autocommit=False)
records = Table(cnxn, 'cv_tmp_1')
def main():
lgth = len(records)
print "lgth = ", lgth
main()
#################################################################################
Any help would be greatly appreciated!!! Thank you!!
This is the error I get:
Traceback (most recent call last):
File "./testcols2.py", line 27, in ?
main()
File "./testcols2.py", line 24, in main
lgth = len(records)
File "./testcols2.py", line 12, in __len__
self.dbc.execute("select count(*) from columns where table_schema = 'public' and table_name = %s;" % (self.name))
pyodbc.ProgrammingError: ('42703', '[42703] ERROR 2624: Column "cv_tmp_1" does not exist\n (2624) (SQLExecDirectW)')
#########################################################
import pyodbc
class Table:
def __init__(self, db, name):
self.db = db
self.name = name
self.dbc = self.db.cursor()
def __getitem__(self, item):
self.dbc.execute("select * from %s offset %s;" % (self.name, item))
return self.dbc.fetchone()
def __len__(self):
self.dbc.execute("select count(*) from %s" % (self.name))
l = int(self.dbc.fetchone() [0])
return l
cnstr = "DSN=xxxxxx;PWD=xxxxxxxx;Charset=UTF-8"
targetSchema = 'public'
cnxn = pyodbc.connect(cnstr, autocommit=False)
##########################################################################
And this works without a problem. Now, I am trying to use the same type of structure to retrieve the columns from a table (column_name from columns...) but it fails. In this example I am only trying to retrieve the number of columns (if I hard code the table name it works fine!!!). I have run the query string in a cursor.execute and in vsql and it works.
################################################################################
import pyodbc
class Table:
def __init__(self, db, name):
self.db = db
self.name = name
self.dbc = self.db.cursor()
def __len__(self):
self.dbc.execute("select count(*) from columns where table_schema = 'public' and table_name = %s;" % (self.name))
l = int(self.dbc.fetchone() [0])
return l
cnstr = "DSN=xxxxxx;PWD=xxxxxxxxx;Charset=UTF-8"
targetSchema = 'public'
cnxn = pyodbc.connect(cnstr, autocommit=False)
records = Table(cnxn, 'cv_tmp_1')
def main():
lgth = len(records)
print "lgth = ", lgth
main()
#################################################################################
Any help would be greatly appreciated!!! Thank you!!
This is the error I get:
Traceback (most recent call last):
File "./testcols2.py", line 27, in ?
main()
File "./testcols2.py", line 24, in main
lgth = len(records)
File "./testcols2.py", line 12, in __len__
self.dbc.execute("select count(*) from columns where table_schema = 'public' and table_name = %s;" % (self.name))
pyodbc.ProgrammingError: ('42703', '[42703] ERROR 2624: Column "cv_tmp_1" does not exist\n (2624) (SQLExecDirectW)')