def execute(self, sql, all_args=None): """ :param sql: :param all_args: A list of lists of equal lenght to sql (if sql is a list) containing arguments for ? in the corresponding sql. :return: """ if isinstance(sql, str): sql = [sql] elif not isinstance(sql, (list, tuple)): raise TypeError(ru(QCoreApplication.translate('DbConnectionManager', 'DbConnectionManager.execute: sql must be type string or a list/tuple of strings. Was %s'))%ru(type(sql))) for idx, line in enumerate(sql): if all_args is None: try: self.cursor.execute(line) except Exception as e: textstring = ru(QCoreApplication.translate('sql_load_fr_db', """DB error!\n SQL causing this error:%s\nMsg:\n%s""")) % (ru(line), ru(str(e))) utils.MessagebarAndLog.warning( bar_msg=utils.sql_failed_msg(), log_msg=textstring) raise elif isinstance(all_args, (list, tuple)): args = all_args[idx] try: self.cursor.execute(line, args) except Exception as e: textstring = ru(QCoreApplication.translate('sql_load_fr_db', """DB error!\n SQL causing this error:%s\nusing args %s\nMsg:\n%s""")) % (ru(line), ru(args), ru(str(e))) utils.MessagebarAndLog.warning( bar_msg=utils.sql_failed_msg(), log_msg=textstring) raise else: raise TypeError(ru(QCoreApplication.translate('DbConnectionManager', 'DbConnectionManager.execute: all_args must be a list/tuple. Was %s')) % ru(type(all_args)))
def execute(self, sql, all_args=None): """ :param sql: :param all_args: A list of lists of equal lenght to sql (if sql is a list) containing arguments for ? in the corresponding sql. :return: """ if isinstance(sql, basestring): sql = [sql] elif not isinstance(sql, (list, tuple)): raise TypeError( utils.returnunicode( QCoreApplication.translate( u'DbConnectionManager', u'DbConnectionManager.execute: sql must be type string or a list/tuple of strings. Was %s' )) % utils.returnunicode(type(sql))) for idx, line in enumerate(sql): if all_args is None: try: self.cursor.execute(line) except Exception as e: textstring = utils.returnunicode( QCoreApplication.translate( u'sql_load_fr_db', u"""DB error!\n SQL causing this error:%s\nMsg:\n%s""" )) % (utils.returnunicode(line), utils.returnunicode(str(e))) utils.MessagebarAndLog.warning( bar_msg=utils.sql_failed_msg(), log_msg=textstring) raise elif isinstance(all_args, (list, tuple)): args = all_args[idx] try: self.cursor.execute(line, args) except Exception as e: textstring = utils.returnunicode( QCoreApplication.translate( u'sql_load_fr_db', u"""DB error!\n SQL causing this error:%s\nusing args %s\nMsg:\n%s""" )) % (utils.returnunicode(line), utils.returnunicode(args), utils.returnunicode(str(e))) utils.MessagebarAndLog.warning( bar_msg=utils.sql_failed_msg(), log_msg=textstring) raise else: raise TypeError( utils.returnunicode( QCoreApplication.translate( u'DbConnectionManager', u'DbConnectionManager.execute: all_args must be a list/tuple. Was %s' )) % utils.returnunicode(type(all_args)))
def get_table_info(tablename, dbconnection=None): if not isinstance(dbconnection, DbConnectionManager): dbconnection = DbConnectionManager() if dbconnection.dbtype == 'spatialite': columns_sql = """PRAGMA table_info ('%s')""" % (tablename) try: columns = dbconnection.execute_and_fetchall(columns_sql) except Exception as e: utils.MessagebarAndLog.warning( bar_msg=utils.sql_failed_msg(), log_msg=ru( QCoreApplication.translate('get_table_info', 'Sql failed: %s\msg:%s')) % (columns_sql, str(e))) return None else: columns_sql = "SELECT ordinal_position, column_name, data_type, CASE WHEN is_nullable = 'NO' THEN 1 ELSE 0 END AS notnull, column_default, 0 AS primary_key FROM information_schema.columns WHERE table_schema = '%s' AND table_name = '%s'" % ( dbconnection.schemas(), tablename) columns = [ list(x) for x in dbconnection.execute_and_fetchall(columns_sql) ] primary_keys = [ x[0] for x in dbconnection.execute_and_fetchall( "SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type FROM pg_index i JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey) WHERE i.indrelid = '%s'::regclass AND i.indisprimary;" % tablename) ] for column in columns: if column[1] in primary_keys: column[5] = 1 columns = [tuple(column) for column in columns] return columns
def execute_sqlfile(self, sqlfilename, dbconnection, merge_newlines=False): with open(sqlfilename, 'r') as f: lines = [ ru(line).rstrip(u'\r').rstrip(u'\n') for rownr, line in enumerate(f) if rownr > 0 ] lines = [ line for line in lines if all([line.strip(), not line.strip().startswith(u"#")]) ] if merge_newlines: lines = [ u'{};'.format(line) for line in u''.join(lines).split(u';') if line.strip() ] for line in lines: if line: try: dbconnection.execute(line) except Exception, e: utils.MessagebarAndLog.critical( bar_msg=utils.sql_failed_msg(), log_msg=ru( QCoreApplication.translate( u'NewDb', u'sql failed:\n%s\nerror msg:\n%s\n')) % (ru(line), str(e)))
def sql_load_fr_db(sql, dbconnection=None): try: if not isinstance(dbconnection, DbConnectionManager): dbconnection = DbConnectionManager() result = dbconnection.execute_and_fetchall(sql) except Exception as e: textstring = ru(QCoreApplication.translate('sql_load_fr_db', """DB error!\n SQL causing this error:%s\nMsg:\n%s""")) % (ru(sql), ru(str(e))) utils.MessagebarAndLog.warning( bar_msg=utils.sql_failed_msg(), log_msg=textstring, duration=4) return False, [] else: return True, result
def execute_and_fetchall(self, sql): try: self.cursor.execute(sql) except (sqlite.OperationalError, Exception) as e: textstring = ru(QCoreApplication.translate('sql_load_fr_db', """DB error!\n SQL causing this error:%s\nMsg:\n%s""")) % ( ru(sql), ru(str(e))) utils.MessagebarAndLog.warning( bar_msg=utils.sql_failed_msg(), log_msg=textstring) raise return self.cursor.fetchall()
def execute_and_fetchall(self, sql): try: self.cursor.execute(sql) except Exception as e: textstring = utils.returnunicode( QCoreApplication.translate( u'sql_load_fr_db', u"""DB error!\n SQL causing this error:%s\nMsg:\n%s""") ) % (utils.returnunicode(sql), utils.returnunicode(str(e))) utils.MessagebarAndLog.warning(bar_msg=utils.sql_failed_msg(), log_msg=textstring) raise return self.cursor.fetchall()
def get_distinct_values(tablename, columnname): if not tablename or not columnname: return [] sql = '''SELECT DISTINCT %s FROM %s''' % (columnname, tablename) connection_ok, result = db_utils.sql_load_fr_db(sql) if not connection_ok: utils.MessagebarAndLog.critical( bar_msg=utils.sql_failed_msg(), log_msg=ru(QCoreApplication.translate('DistinctValuesBrowser', """Cannot get data from sql %s"""))%ru(sql)) return [] values = [ru(col[0]) for col in result] return values
def sql_load_fr_db(sql, dbconnection=None): try: if not isinstance(dbconnection, DbConnectionManager): dbconnection = DbConnectionManager() result = dbconnection.execute_and_fetchall(sql) except Exception as e: textstring = ru( QCoreApplication.translate( 'sql_load_fr_db', """DB error!\n SQL causing this error:%s\nMsg:\n%s""")) % ( ru(sql), ru(str(e))) utils.MessagebarAndLog.warning(bar_msg=utils.sql_failed_msg(), log_msg=textstring, duration=4) return False, [] else: return True, result
def get_distinct_values(tablename, columnname): if not tablename or not columnname: return [] sql = '''SELECT DISTINCT %s FROM %s''' % (columnname, tablename) connection_ok, result = db_utils.sql_load_fr_db(sql) if not connection_ok: utils.MessagebarAndLog.critical( bar_msg=utils.sql_failed_msg(), log_msg=ru( QCoreApplication.translate( u'DistinctValuesBrowser', u"""Cannot get data from sql %s""")) % ru(sql)) return [] values = [ru(col[0]) for col in result] return values
def get_table_info(tablename, dbconnection=None): if not isinstance(dbconnection, DbConnectionManager): dbconnection = DbConnectionManager() if dbconnection.dbtype == 'spatialite': columns_sql = """PRAGMA table_info ('%s')""" % (tablename) try: columns = dbconnection.execute_and_fetchall(columns_sql) except Exception as e: utils.MessagebarAndLog.warning(bar_msg=utils.sql_failed_msg(), log_msg=ru( QCoreApplication.translate('get_table_info', 'Sql failed: %s\msg:%s')) % (columns_sql, str(e))) return None else: columns_sql = "SELECT ordinal_position, column_name, data_type, CASE WHEN is_nullable = 'NO' THEN 1 ELSE 0 END AS notnull, column_default, 0 AS primary_key FROM information_schema.columns WHERE table_schema = '%s' AND table_name = '%s'"%(dbconnection.schemas(), tablename) columns = [list(x) for x in dbconnection.execute_and_fetchall(columns_sql)] primary_keys = [x[0] for x in dbconnection.execute_and_fetchall("SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type FROM pg_index i JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey) WHERE i.indrelid = '%s'::regclass AND i.indisprimary;"%tablename)] for column in columns: if column[1] in primary_keys: column[5] = 1 columns = [tuple(column) for column in columns] return columns