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)')
Python Class to return column names of a table
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Python Class to return column names of a table
Hi,
I think when your query hits Vertica it is executed as this:
Instead of this:
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?
But I am not positive of the syntax... It may be:
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;
Code: Select all
select count(*) from columns where table_schema = 'public' and table_name = 'cv_tmp_1';
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''')
But I am not positive of the syntax... It may be:
- records = Table(cnxn, "'cv_tmp_1'")
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Python Class to return column names of a table
Hi!
Jim right.
PS: http://pastebin.com/vfRuzCUh
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
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!!!
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Python Class to return column names of a table
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:
This is correct:
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:
This is correct:
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';
^
Code: Select all
dbadmin=> select count(*) from dual;
count
-------
1
(1 row)
This is incorrect:
Code: Select all
dbadmin=> select count(*) from dual where dummy = X;
ERROR 2624: Column "X" does not exist
Code: Select all
dbadmin=> select count(*) from dual where dummy = 'X';
count
-------
1
(1 row)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Python Class to return column names of a table
Thank you!! I understand!!