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
