def insert_rows(self, table_name, lst_lst_field, int_start_row,
int_len_column):
query1 = 'insert into ' + table_name + ' values ('
query3 = ')'
try:
count = 0
for i in range(int_start_row, len(lst_lst_field)):
row = lst_lst_field[i]
if len(row) != 0: # empty record
query2 = ''
if len(
row
) < int_len_column: # row could have less fields than what columns indicate
for i in range(int_len_column - len(row)):
row.append('')
for j in range(len(row)):
query2 = query2 + self.prepare_field(row[j])
if j < len(row) - 1:
query2 = query2 + ','
query = query1 + query2 + query3
self.cursor.execute(query)
count = count + 1
if count % 10000 == 0:
self.cursor.execute('commit')
print count
self.cursor.execute('commit')
print 'row_inserted: ' + str(count)
return count
except cx_Oracle.DatabaseError as e:
raise cx_Oracle.DatabaseError(table_name + "|" + query)
def execute(self, sql, dic=None):
try:
sql = sql.encode('utf-8')
cursor = self.db.cursor()
query = None
if (dic == None):
query = cursor.execute(sql)
else:
query = cursor.execute(sql, dic)
if (query == None):
query = cursor.rowcount
cursor.close()
else:
query = query.fetchall()
cursor.close()
return query
except cx_Oracle.DatabaseError:
info = sys.exc_info()
oracle_info = str(info[1])
print oracle_info
if (oracle_info == 'ORA-00028: your session has been killed\n'):
getPool()
raise Exception('数据库连接出现问题，程序已自动重新连接.请重新操作')
else:
error = oracle_info + "\n" + sql
raise cx_Oracle.DatabaseError(error)
def create_table(self, table_name, lst_column, lst_data_type):
query1 = 'create table ' + table_name + ' ('
#print table_name
query2 = ''
count = -1
if len(lst_column) == len(lst_data_type):
count = len(lst_column)
else:
print table_name + ' has ' + str(len(lst_column)) + 'columns'
print ', but some records have ' + str(
len(lst_column_length)) + ' fields'
print ', delimiting was not sucessful.'
for i in range(0, count):
if len(lst_column[i]) == 0:
lst_column[i] = 'field' + str(i)
query2 = query2 + lst_column[i] + ' ' + lst_data_type[i]
if i < count - 1:
query2 = query2 + ','
query3 = ')'
query = query1 + query2 + query3
try:
self.cursor.execute(query)
except cx_Oracle.DatabaseError as e:
print table_name
print e
print query
raise cx_Oracle.DatabaseError(table_name + "|" + query)
def do_discovery(conn_config, filter_schemas):
LOGGER.info("starting discovery")
connection = orc_db.open_connection(conn_config)
cur = connection.cursor()
row_counts = produce_row_counts(connection, filter_schemas)
table_info = {}
binds_sql = [":{}".format(b) for b in range(len(filter_schemas))]
sql = filter_schemas_sql_clause(
"""
SELECT owner, table_name
FROM all_tables
WHERE owner != 'SYS'""", binds_sql)
LOGGER.info("fetching tables: %s %s", sql, filter_schemas)
for row in cur.execute(sql, filter_schemas):
schema = row[0]
table = row[1]
if schema not in table_info:
table_info[schema] = {}
is_view = False
table_info[schema][table] = {
'row_count': row_counts[table],
'is_view': is_view
}
sql = filter_schemas_sql_clause(
"""
SELECT owner, view_name
FROM sys.all_views
WHERE owner != 'SYS'""", binds_sql)
LOGGER.info("fetching views")
for row in cur.execute(sql, filter_schemas):
view_name = row[1]
schema = row[0]
if schema not in table_info:
table_info[schema] = {}
table_info[schema][view_name] = {'is_view': True}
# If table_info is empty, send a SYMON error that no table was found with current input.
if not table_info:
cur.close()
connection.close()
# Raise database error because only database error messages are interpretted during discovery
raise cx_Oracle.DatabaseError(
'SYM-00001: No Table Found in the Database')
catalog = discover_columns(connection, table_info, filter_schemas)
json.dump(catalog, sys.stdout, indent=2)
cur.close()
connection.close()
return catalog
def __init__(self, *args, **kwargs):
Pyro.core.ObjBase.__init__(self)
global daemon
self.daemon = daemon
self.id = id(self)
try:
#self.connection = cx_Oracle.connect(threaded=True ,*args, **kwargs)
self.connection = cx_Oracle.connect(
settings.DATABASE_USER_CX,
settings.DATABASE_PASSWORD,
'//%s:%s/%s' % (settings.DATABASE_HOST, settings.DATABASE_PORT,
settings.DATABASE_NAME),
threaded=True)
except cx_Oracle.DatabaseError, e:
#if ORA-12170 in e: #timeout
log.critical("error while connecting to oracle: %s" % e)
#re-raise
raise cx_Oracle.DatabaseError(e)
def executemany(self, sql, value):
try:
sql = sql.encode("utf-8")
cursor = self.db.cursor()
query = None
query = cursor.executemany(sql, value)
query = cursor.rowcount
cursor.close()
return query
except cx_Oracle.DatabaseError:
info = sys.exc_info()
oracle_info = str(info[1])
print oracle_info
if (oracle_info == 'ORA-00028: your session has been killed\n'):
getPool()
raise \
Exception('数据库连接出现问题，程序已自动重新连接.请重新操作')
else:
error = oracle_info + "\n" + sql
raise cx_Oracle.DatabaseError(error)
def connect(*_, **__):
try:
# Here we use the Connection class directly, to avoid a stack overflow if we mock `cxo.connect` later.
return cxo.Connection(cfg.user, cfg.pwd, cfg.dsn, encoding="UTF-8", nencoding="UTF-8")
except Exception as err:
raise cxo.DatabaseError("Could not connect to the test database. Did you forget to start it?") from err