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)