I am working with Postgresql and am trying to get a way of working with
schemas through web2py.
In psql I can do:
js=# show search_path;
search_path
----------------
"$user",public
(1 row)js=# set search_path to toets0;
SET
js=# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+-------
toets0 | auth_user | table | js
(1 row)
js=# select count(*) from auth_user;
count
-------
0
(1 row)
But when I try that in web2py I get syntax errors when I try 'show schema'
or 'set search_path'.
In [2]: db.executesql('select count(*) from auth_user;')
Out[2]: [(0,)]
In [3]: db.executesql('show schema;')
---------------------------------------------------------------------------
OperationalError Traceback (most recent call last)
/home/js/web2py/applications/nakb/models/menu.py in <module>()
----> 1 db.executesql('show schema;')
/home/js/web2py/gluon/dal.pyc in executesql(self, query, placeholders,as_dict
)
6827 self._adapter.execute(query, placeholders)
6828 else:
-> 6829 self._adapter.execute(query)
6830 if as_dict:
6831 if not hasattr(self._adapter.cursor,'description'):
/home/js/web2py/gluon/dal.pyc in execute(self, *a, **b)
1467
1468 def execute(self, *a, **b):
-> 1469 return self.log_execute(*a, **b)
1470
1471 def represent(self, obj, fieldtype):
/home/js/web2py/gluon/dal.pyc in log_execute(self, *a, **b)
1461 self.db._lastsql = command
1462 t0 = time.time()
-> 1463 ret = self.cursor.execute(*a, **b)
1464 self.db._timings.append((command,time.time()-t0))
1465 del self.db._timings[:-TIMINGSSIZE]
OperationalError: near "show": syntax error
db._lastsql
Out[4]: 'show schema;'
Clearly there is not something wrong with the sql-syntax as it works on
psql.
Regards
Johann
