Page 1 of 1

Python Class to return column names of a table

Posted: Wed Oct 17, 2012 8:36 pm
by clv100
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)')

Re: Python Class to return column names of a table

Posted: Thu Oct 18, 2012 1:31 pm
by JimKnicely
Hi,

I think when your query hits Vertica it is executed as this:

Code: Select all

select count(*) from columns where table_schema = 'public' and table_name = cv_tmp_1;
Instead of this:

Code: Select all

select count(*) from columns where table_schema = 'public' and table_name = 'cv_tmp_1';
That's why you are getting the invalid column name error...

Maybe when you are passing in the table name into the query you need to include the surrounding single quotes?

Something like this?
  • records = Table(cnxn, '''cv_tmp_1''')
Note: Those are three single quotes on each side of the cv_tmp_1 table name ...

But I am not positive of the syntax... It may be:
  • records = Table(cnxn, "'cv_tmp_1'")
In this case, I used a double quote and single quote to surround your table name.

Re: Python Class to return column names of a table

Posted: Thu Oct 18, 2012 2:41 pm
by id10t
Hi!

Jim right.

PS: http://pastebin.com/vfRuzCUh

Code: Select all

[dbadmin@vertica ~]$ vsql -c "select * from Foo"
 id |  c  
----+-----
  4 | 9@4
 55 | ICD
 65 | C>?
(3 rows)
[dbadmin@vertica ~]$ python /tmp/clv.py

--------------------
total cols: 2
--------------------

{ schema : public, table : Foo, columns : 2, rows : 3 }

Re: Python Class to return column names of a table

Posted: Thu Oct 18, 2012 3:32 pm
by clv100
Thank you so much! That worked!! The odd thing is, the first block of code (where I am retrieving the rows from a table, works fine without the single quote within the double quote)? The only difference in the second block of code is that I am retrieving the columns from the table so I am now in the v_catalog schema (retrieving column_name for columns). When I follow your suggestion and include the single quote within the double quote for the table_name it finally works but I don't understand why? I really appreciate your help as I am trying to get a grasp on two new concepts at the same time!!!

Re: Python Class to return column names of a table

Posted: Thu Oct 18, 2012 4:08 pm
by JimKnicely
In your first execute statement the substitution is for an object name (in your case, a table name) where you wouldn't want the single quotes surrounding it.

For instance, this is incorrect:

Code: Select all

dbadmin=> select count(*) from 'dual';
ERROR 4856:  Syntax error at or near "'dual'" at character 22
LINE 1: select count(*) from 'dual';
                             ^
This is correct:

Code: Select all

dbadmin=> select count(*) from dual;
 count
-------
     1
(1 row)
In your second execute statement your substitution is for a "value" in the table_name column of the columns table... It has to be surrounded by single quotes. Otherwise Vertica thinks it is also an object in the database:

This is incorrect:

Code: Select all

dbadmin=> select count(*) from dual where dummy = X;
ERROR 2624:  Column "X" does not exist
This is correct:

Code: Select all

dbadmin=> select count(*) from dual where dummy = 'X';
 count
-------
     1
(1 row)

Re: Python Class to return column names of a table

Posted: Thu Oct 18, 2012 4:25 pm
by clv100
Thank you!! I understand!!