def execute(self, sql, values=(), qualified=False):
"""Executes the statement with the values and does conversion
of the return result as necessary.
result is list of dictionaries, or number of rows affected"""
if self.verbose:
debug("SQL: %s", sql)
debug("bind variables: %s", values)
c = self.conn.cursor()
if values:
c.execute(sql, values)
else:
c.execute(sql)
if c.description is None:
resultset = None
else:
lob_types = set((cx_Oracle.CLOB, cx_Oracle.BLOB))
field_types = set(d[1] for d in c.description)
have_lobs = field_types & lob_types
if have_lobs:
resultset = [list(self.field_values(row)) for row in c]
else:
resultset = c.fetchall()
if not resultset:
return c.rowcount
res = self._convertResultSet(c.description, resultset, qualified)
c.close()
return res
def execute(self, sql, values=(), qualified=False):
"""Executes the statement with the values and does conversion
of the return result as necessary.
result is list of dictionaries, or number of rows affected"""
if self.verbose:
debug("SQL: %s", sql)
debug("bind variables: %s", values)
c = self.conn.cursor()
if values:
c.execute(sql, values)
else:
c.execute(sql)
if c.description is None:
resultset = None
else:
lob_types = set((cx_Oracle.CLOB, cx_Oracle.BLOB))
field_types = set(d[1] for d in c.description)
have_lobs = field_types & lob_types
if have_lobs:
resultset = [list(self.field_values(row)) for row in c]
else:
resultset = c.fetchall()
if not resultset:
return c.rowcount
res = self._convertResultSet(c.description, resultset, qualified)
c.close()
return res
def execute(self, sql, values=(), qualified=False):
"""Executes the statement with the values and does conversion
of the return result as necessary.
result is list of dictionaries, or number of rows affected"""
if self.verbose:
debug("SQL: %s", sql)
debug("bind variables: %s", values)
c = self.conn.cursor()
if values:
c.execute(sql, values)
else:
c.execute(sql)
if c.statusmessage == 'SELECT':
resultset = c.fetchall()
else:
resultset = None
if not resultset:
if c.statusmessage.startswith(
'INSERT') or c.statusmessage.startswith('UPDATE'):
# rowcount doesn't work
return int(c.statusmessage.split()[-1])
return -1
res = self._convertResultSet(c.description, resultset, qualified)
c.close()
return res
def getSequence(self, name, field, table):
cur=self.conn.cursor()
sql="select %s.nextval from dual" % name
if self.verbose:
debug("SQL: %s", (sql,))
cur.execute(sql)
res=cur.fetchone()
if not res:
raise PyDOError, "could not get value for sequence %s!" % name
return res[0]
def getSequence(self, name, field, table):
cur = self.conn.cursor()
sql = "select %s.nextval from dual" % name
if self.verbose:
debug("SQL: %s", (sql, ))
cur.execute(sql)
res = cur.fetchone()
if not res:
raise PyDOError, "could not get value for sequence %s!" % name
return res[0]
def getuser():
user=getpass.getuser()
if user:
return user
try:
from win32api import GetUserName
except ImportError:
debug("can't find username, returning empty string")
return ''
else:
return GetUserName()
def listTables(self, schema=None):
""" lists tables in the database."""
sql="SHOW TABLES"
cur=self.conn.cursor()
if self.verbose:
debug('SQL: %s', (sql,))
cur.execute(sql)
res=cur.fetchall()
cur.close()
if not res:
return []
return sorted(x[0] for x in res)
def listTables(self, schema=None):
""" lists tables in the database."""
sql = "SHOW TABLES"
cur = self.conn.cursor()
if self.verbose:
debug('SQL: %s', (sql, ))
cur.execute(sql)
res = cur.fetchall()
cur.close()
if not res:
return []
return sorted(x[0] for x in res)
def getSequence(self, name, field, table):
if name==True:
# not a string; infer the sequence name
name='%s_%s_seq' % (table, field)
debug('inferring sequence name: %s', name)
cur=self.conn.cursor()
sql="select nextval('%s')" % name
if self.verbose:
debug("SQL: %s", (sql,))
cur.execute(sql)
res=cur.fetchone()
if not res:
raise PyDOError, "could not get value for sequence %s!" % name
return res[0]
def getSequence(self, name, field, table):
if name == True:
# not a string; infer the sequence name
name = '%s_%s_seq' % (table, field)
debug('inferring sequence name: %s', name)
cur = self.conn.cursor()
sql = "select nextval('%s')" % name
if self.verbose:
debug("SQL: %s", (sql, ))
cur.execute(sql)
res = cur.fetchone()
if not res:
raise PyDOError, "could not get value for sequence %s!" % name
return res[0]
def listTables(self, schema=None):
"""lists the tables in the database schema"""
if schema is None:
schema = 'PUBLIC'
sql = """
SELECT t.object_name
FROM sys.all_objects t
WHERE t.owner = :schema AND t.object_type IN ('TABLE', 'VIEW')
"""
cur = self.conn.cursor()
if self.verbose:
debug("SQL: %s", (sql, ))
cur.execute(sql, schema=schema)
res = cur.fetchall()
cur.close()
if not res:
return []
return sorted(x[0] for x in res)
def listTables(self, schema=None):
"""lists the tables in the database schema"""
if schema is None:
schema = 'PUBLIC'
sql = """
SELECT t.object_name
FROM sys.all_objects t
WHERE t.owner = :schema AND t.object_type IN ('TABLE', 'VIEW')
"""
cur = self.conn.cursor()
if self.verbose:
debug("SQL: %s", (sql,))
cur.execute(sql, schema=schema)
res = cur.fetchall()
cur.close()
if not res:
return []
return sorted(x[0] for x in res)
def execute(self, sql, values=(), qualified=False):
"""Executes the statement with the values and does conversion
of the return result as necessary.
result is list of dictionaries, or number of rows affected"""
if self.verbose:
debug("SQL: %s", sql)
debug("bind variables: %s", values)
c = self.conn.cursor()
if values:
c.execute(sql, values)
else:
c.execute(sql)
if c.description is None:
resultset = None
else:
resultset = OracleResultSet(c)
if not resultset:
return c.rowcount
res = self._convertResultSet(c.description, resultset, qualified)
c.close()
return res
def listTables(self, schema=None):
"""lists the tables in the database schema"""
if schema is None:
schema = 'public'
sql = """
SELECT t.tablename AS name
FROM pg_catalog.pg_tables t
WHERE t.schemaname=%s
UNION
SELECT v.viewname AS name
FROM pg_catalog.pg_views v
WHERE v.schemaname=%s
"""
cur = self.conn.cursor()
if self.verbose:
debug("SQL: %s", (sql, ))
cur.execute(sql, (schema, schema))
res = cur.fetchall()
cur.close()
if not res:
return []
return sorted(x[0] for x in res)
def listTables(self, schema=None):
"""lists the tables in the database schema"""
if schema is None:
schema='public'
sql="""
SELECT t.tablename AS name
FROM pg_catalog.pg_tables t
WHERE t.schemaname=%s
UNION
SELECT v.viewname AS name
FROM pg_catalog.pg_views v
WHERE v.schemaname=%s
"""
cur=self.conn.cursor()
if self.verbose:
debug("SQL: %s", (sql,))
cur.execute(sql, (schema, schema))
res=cur.fetchall()
cur.close()
if not res:
return []
return sorted(x[0] for x in res)
def execute(self, sql, values=(), qualified=False):
"""Executes the statement with the values and does conversion
of the return result as necessary.
result is list of dictionaries, or number of rows affected"""
if self.verbose:
debug("SQL: %s", sql)
debug("bind variables: %s", values)
c=self.conn.cursor()
if values:
c.execute(sql, values)
else:
c.execute(sql)
if c.statusmessage=='SELECT':
resultset=c.fetchall()
else:
resultset=None
if not resultset:
if c.statusmessage.startswith('INSERT') or c.statusmessage.startswith('UPDATE'):
# rowcount doesn't work
return int(c.statusmessage.split()[-1])
return -1
res=self._convertResultSet(c.description, resultset, qualified)
c.close()
return res
def execute(sql):
debug('SQL: %s', (sql,))
c.execute(sql)
def execute(sql):
debug('SQL: %s', (sql, ))
c.execute(sql)
def iterfetch(resultSpec, sqlTemplate, *values, **kwargs):
"""
a method that executes sql and returns rows of tuples of PyDO
objects and scalar values, ordered according to a result set
specification.
resultSpec is a list that may contain:
* PyDO classes;
* 2-tuples of (PyDO class, alias string), which indicate that
the table represented by the PyDO class is to be referenced by
the given alias;
* strings, which represent arbitrary SQL expressions that may
occur in a SQL column-list specification.
sqlTemplate is a string that may contain interpolation variables
in the style of string.Template. In particular, two variables are
supplied to this template automatically:
$COLUMNS -- a list of columns computed from the supplied resultSpec;
$TABLES -- a list of tables similarly computed.
Additional interpolation variables may be passed in as keyword
arguments. Bind variables to the SQL may also be passed in,
through positional arguments; if there is only one positional
argument, and it is a dictionary, it will be used instead of a
list of values, under the assumption that either the 'named' or
'pyformat' paramstyle is being used.
For each element E in the resultSpec, the result row contains one
element F. If E is a PyDO class, F will either be an instance of
E, or, if all its corresponding columns were null for that row and
E has a uniqueness constraint (which in PyDO is implicitly a not
null constraint), None. If E is a string, F will be whatever the
cursor returned for that column.
"""
resultSpec=list(_processResultSpec(resultSpec))
objs=[x for x in resultSpec if not isinstance(x, basestring)]
# check that all objs have the same connectionAlias
caliases=tuple(frozenset(o.connectionAlias for o in objs))
if len(caliases)>1:
raise ValueError, \
"objects passed to fetch must have same connection alias"
elif len(caliases)==0:
raise ValueError, "must supply some object in result spec"
dbi=objs[0].getDBI()
tables = ', '.join(x.getTable() for x in objs)
# if an item has no uniqueness constraints, it really could
# be all null; otherwise, take all-nullness to mean that
# we're dealing with a join with a no matching row for that
# table. "noneable" means here, "we can represent it as None"
noneable=[o for o in objs if o.getUniquenessConstraints()]
allcols=[]
for item in resultSpec:
if hasattr(item, 'getColumns'):
allcols.append(sorted(item.getColumns(True)))
else:
allcols.append(item)
columns=', '.join(iflatten(allcols))
sql=string.Template(sqlTemplate).substitute(kwargs,
TABLES=tables,
COLUMNS=columns)
c=dbi.cursor()
if len(values)==1 and isinstance(values[0], dict):
values=values[0]
if dbi.verbose:
debug('SQL: %s', sql)
debug('bind variables: %s', values)
c.execute(sql, values)
result=c.fetchall()
c.close()
if not result:
raise StopIteration
retrow=[]
for row in result:
del retrow[:]
p=0
for o, cols in izip(resultSpec, allcols):
if isinstance(o, basestring):
retrow.append(row[p])
p+=1
else:
assert isinstance(o, TableAlias) or (isclass(o) and issubclass(o, PyDO))
d={}
for col in cols:
d[_strip_tablename(col)]=row[p]
p+=1
if o in noneable:
for v in d.itervalues():
if v is not None:
notnull=True
break
else:
notnull=False
else:
notnull=True
if notnull:
retrow.append(o(**d))
else:
retrow.append(None)
yield tuple(retrow)
def describeTable(self, table, schema=None):
# verify that the table exists
sql = """
SELECT t.tablename AS tname
FROM pg_catalog.pg_tables t
WHERE t.tablename=%s
AND t.schemaname=%s
UNION
SELECT v.viewname AS tname
FROM pg_catalog.pg_views v
WHERE v.viewname=%s
and v.schemaname=%s
"""
if schema is None:
schema = 'public'
cur = self.conn.cursor()
bind = (table, schema, table, schema)
if self.verbose:
debug("SQL: %s", (sql, ))
cur.execute(sql, bind)
for row in cur.fetchall():
# it exists, get on with it
break
else:
raise ValueError, "no such table or view: %s.%s" % (schema, table)
sql = """
SELECT a.attname, a.attnum
FROM pg_catalog.pg_attribute a,
pg_catalog.pg_namespace n,
pg_catalog.pg_class c
WHERE a.attrelid = %s::regclass
AND c.oid=a.attrelid
AND c.relnamespace=n.oid
AND n.nspname=%s
AND a.attnum > 0
AND NOT a.attisdropped
ORDER BY a.attnum
"""
fields = {}
cur = self.conn.cursor()
if self.verbose:
debug("SQL: %s", (sql, ))
cur.execute(sql, (table, schema))
for row in cur.fetchall():
if self.verbose:
debug("Found column %s" % list(row))
fields[row[1]] = Field(row[0])
sql = """
SELECT indkey
FROM pg_catalog.pg_index i,
pg_catalog.pg_class c,
pg_catalog.pg_namespace n,
pg_catalog.pg_attribute a
WHERE i.indrelid = %s::regclass
AND c.oid=i.indrelid
AND c.relnamespace=n.oid
AND n.nspname=%s
AND i.indisunique
AND a.attrelid=c.oid
AND a.attnum=indkey[0]
AND a.attnotnull
"""
unique = set()
if self.verbose:
debug("SQL: %s", (sql, ))
cur.execute(sql, (table, schema))
for row in cur.fetchall():
L = [int(i) for i in row[0].split(' ')]
if self.verbose:
debug("Found unique index on %s" % L)
if len(L) == 1:
fields[L[0]].unique = True
else:
unique.add(frozenset([fields[i].name for i in L]))
sql = """
SELECT c.relname
FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n
WHERE relname like '%s_%%%%_seq'
AND c.relnamespace=n.oid
AND n.nspname=%%s
AND relkind = 'S'
""" % table
if self.verbose:
debug("SQL: %s", (sql, ))
cur.execute(sql, (schema, ))
for row in cur.fetchall():
maybecolname = row[0][len(table) + 1:-4]
for field in fields.values():
if field.name == maybecolname:
if self.verbose:
debug("Found sequence %s on %s" % (row[0], field.name))
field.sequence = row[0]
break
cur.close()
d = {}
for f in fields.values():
d[f.name] = f
return (d, unique)
def describeTable(self, table, schema=None):
# verify that the table exists
sql = """
SELECT t.tablename AS tname
FROM pg_catalog.pg_tables t
WHERE t.tablename=%s
AND t.schemaname=%s
UNION
SELECT v.viewname AS tname
FROM pg_catalog.pg_views v
WHERE v.viewname=%s
and v.schemaname=%s
"""
if schema is None:
schema='public'
cur=self.conn.cursor()
bind=(table, schema, table, schema)
if self.verbose:
debug("SQL: %s", (sql,))
cur.execute(sql, bind)
for row in cur.fetchall():
# it exists, get on with it
break
else:
raise ValueError, "no such table or view: %s.%s" % (schema, table)
sql = """
SELECT a.attname, a.attnum
FROM pg_catalog.pg_attribute a,
pg_catalog.pg_namespace n,
pg_catalog.pg_class c
WHERE a.attrelid = %s::regclass
AND c.oid=a.attrelid
AND c.relnamespace=n.oid
AND n.nspname=%s
AND a.attnum > 0
AND NOT a.attisdropped
ORDER BY a.attnum
"""
fields = {}
cur = self.conn.cursor()
if self.verbose:
debug("SQL: %s", (sql,))
cur.execute(sql, (table,schema))
for row in cur.fetchall():
if self.verbose:
debug("Found column %s" % list(row))
fields[row[1]] = Field(row[0])
sql = """
SELECT indkey
FROM pg_catalog.pg_index i,
pg_catalog.pg_class c,
pg_catalog.pg_namespace n,
pg_catalog.pg_attribute a
WHERE i.indrelid = %s::regclass
AND c.oid=i.indrelid
AND c.relnamespace=n.oid
AND n.nspname=%s
AND i.indisunique
AND a.attrelid=c.oid
AND a.attnum=indkey[0]
AND a.attnotnull
"""
unique = set()
if self.verbose:
debug("SQL: %s", (sql,))
cur.execute(sql, (table,schema))
for row in cur.fetchall():
L = [int(i) for i in row[0].split(' ')]
if self.verbose:
debug("Found unique index on %s" % L)
if len(L) == 1:
fields[L[0]].unique = True
else:
unique.add(frozenset([fields[i].name for i in L]))
sql = """
SELECT c.relname
FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n
WHERE relname like '%s_%%%%_seq'
AND c.relnamespace=n.oid
AND n.nspname=%%s
AND relkind = 'S'
""" % table
if self.verbose:
debug("SQL: %s", (sql,))
cur.execute(sql, (schema,))
for row in cur.fetchall():
maybecolname = row[0][len(table) + 1:-4]
for field in fields.values():
if field.name == maybecolname:
if self.verbose:
debug("Found sequence %s on %s" % (row[0], field.name))
field.sequence = row[0]
break
cur.close()
d = {}
for f in fields.values():
d[f.name] = f
return (d, unique)
def execute(sql):
debug("SQL: %s", (sql,))
cur.execute(sql)
def iterfetch(resultSpec, sqlTemplate, *values, **kwargs):
"""
a method that executes sql and returns rows of tuples of PyDO
objects and scalar values, ordered according to a result set
specification.
resultSpec is a list that may contain:
* PyDO classes;
* 2-tuples of (PyDO class, alias string), which indicate that
the table represented by the PyDO class is to be referenced by
the given alias;
* strings, which represent arbitrary SQL expressions that may
occur in a SQL column-list specification.
sqlTemplate is a string that may contain interpolation variables
in the style of string.Template. In particular, two variables are
supplied to this template automatically:
$COLUMNS -- a list of columns computed from the supplied resultSpec;
$TABLES -- a list of tables similarly computed.
Additional interpolation variables may be passed in as keyword
arguments. Bind variables to the SQL may also be passed in,
through positional arguments; if there is only one positional
argument, and it is a dictionary, it will be used instead of a
list of values, under the assumption that either the 'named' or
'pyformat' paramstyle is being used.
For each element E in the resultSpec, the result row contains one
element F. If E is a PyDO class, F will either be an instance of
E, or, if all its corresponding columns were null for that row and
E has a uniqueness constraint (which in PyDO is implicitly a not
null constraint), None. If E is a string, F will be whatever the
cursor returned for that column.
"""
resultSpec = list(_processResultSpec(resultSpec))
objs = [x for x in resultSpec if not isinstance(x, basestring)]
# check that all objs have the same connectionAlias
caliases = tuple(frozenset(o.connectionAlias for o in objs))
if len(caliases) != 1:
raise ValueError, \
"objects passed to fetch must have same connection alias"
dbi = objs[0].getDBI()
tables = ', '.join(x.getTable() for x in objs)
# if an item has no uniqueness constraints, it really could
# be all null; otherwise, take all-nullness to mean that
# we're dealing with a join with a no matching row for that
# table. "noneable" means here, "we can represent it as None"
noneable = [o for o in objs if o.getUniquenessConstraints()]
allcols = []
for item in resultSpec:
if hasattr(item, 'getColumns'):
allcols.append(sorted(item.getColumns(True)))
else:
allcols.append(item)
columns = ', '.join(iflatten(allcols))
sql = string.Template(sqlTemplate).substitute(kwargs,
TABLES=tables,
COLUMNS=columns)
c = dbi.cursor()
if len(values) == 1 and isinstance(values, dict):
values = values[0]
if dbi.verbose:
debug('SQL: %s', sql)
debug('bind variables: %s', values)
c.execute(sql, values)
result = c.fetchall()
c.close()
if not result:
raise StopIteration
retrow = []
for row in result:
del retrow[:]
p = 0
for o, cols in izip(resultSpec, allcols):
if isinstance(o, basestring):
retrow.append(row[p])
p += 1
else:
assert isinstance(o, TableAlias) or (isclass(o)
and issubclass(o, PyDO))
d = {}
for col in cols:
d[_strip_tablename(col)] = row[p]
p += 1
if o in noneable:
for v in d.itervalues():
if v is not None:
notnull = True
break
else:
notnull = False
else:
notnull = True
if notnull:
retrow.append(o(**d))
else:
retrow.append(None)
yield tuple(retrow)
