Python Class to return column names of a table

Moderator: NorbertKrupa

Post Reply
clv100
Beginner
Beginner
Posts: 27
Joined: Tue Oct 16, 2012 6:04 pm

Python Class to return column names of a table

Post by clv100 » 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)')

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1778
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Python Class to return column names of a table

Post by JimKnicely » Thu Oct 18, 2012 1:31 pm

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.
Jim Knicely

Image

Note: I work for HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Python Class to return column names of a table

Post by id10t » Thu Oct 18, 2012 2:41 pm

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 }

clv100
Beginner
Beginner
Posts: 27
Joined: Tue Oct 16, 2012 6:04 pm

Re: Python Class to return column names of a table

Post by clv100 » Thu Oct 18, 2012 3:32 pm

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!!!

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1778
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Python Class to return column names of a table

Post by JimKnicely » Thu Oct 18, 2012 4:08 pm

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)
Jim Knicely

Image

Note: I work for HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

clv100
Beginner
Beginner
Posts: 27
Joined: Tue Oct 16, 2012 6:04 pm

Re: Python Class to return column names of a table

Post by clv100 » Thu Oct 18, 2012 4:25 pm

Thank you!! I understand!!

Post Reply

Return to “Python”