def load_files(self):
charset = utils.ask_for_charset()
if not charset:
raise utils.UserInterruptError()
filename = utils.select_files(only_one_file=True, extension=ru(QCoreApplication.translate('GeneralCsvImportGui', "Comma or semicolon separated csv file %s;;Comma or semicolon separated csv text file %s;;Comma or semicolon separated file %s"))%('(*.csv)', '(*.txt)', '(*.*)'))
if isinstance(filename, (list, tuple)):
filename = filename[0]
filename = ru(filename)
delimiter = utils.get_delimiter(filename=filename, charset=charset, delimiters=[',', ';'])
self.file_data = self.file_to_list(filename, charset, delimiter)
header_question = utils.Askuser(question="YesNo", msg=ru(QCoreApplication.translate('GeneralCsvImportGui', """Does the file contain a header?""")))
utils.start_waiting_cursor()
if header_question.result:
# Remove duplicate header entries
header = self.file_data[0]
seen = set()
seen_add = seen.add
remove_cols = [idx for idx, x in enumerate(header) if x and (x in seen or seen_add(x))]
self.file_data = [[col for idx, col in enumerate(row) if idx not in remove_cols] for row in self.file_data]
self.table_chooser.file_header = self.file_data[0]
else:
header = ['Column ' + str(colnr) for colnr in range(len(self.file_data[0]))]
self.table_chooser.file_header = header
self.file_data.reverse()
self.file_data.append(header)
self.file_data.reverse()
utils.stop_waiting_cursor()
def ask_for_locale(self):
locales = [
PyQt4.QtCore.QLocale(PyQt4.QtCore.QLocale.Swedish,
PyQt4.QtCore.QLocale.Sweden),
PyQt4.QtCore.QLocale(PyQt4.QtCore.QLocale.English,
PyQt4.QtCore.QLocale.UnitedStates)
]
locale_names = [localeobj.name() for localeobj in locales]
locale_names.append(locale.getdefaultlocale()[0])
locale_names = list(set(locale_names))
question = utils.NotFoundQuestion(
dialogtitle=ru(
QCoreApplication.translate(u'NewDb', u'User input needed')),
msg=ru(
QCoreApplication.translate(
u'NewDb',
u'Supply locale for the database.\nCurrently, only locale sv_SE has special meaning,\nall other locales will use english.'
)),
existing_list=locale_names,
default_value=u'',
combobox_label=ru(QCoreApplication.translate(u'newdb',
u'Locales')),
button_names=[u'Cancel', u'Ok'])
answer = question.answer
submitted_value = ru(question.value)
if answer == u'cancel':
raise utils.UserInterruptError()
elif answer == u'ok':
return submitted_value
def parse_filesettings(self, filename):
"""
:param filename: Parses the file settings of an interlab4 file
:return: a tuple like (file_error, version, encoding, decimalsign, quotechar)
"""
version = None
quotechar = False
decimalsign = None
file_error = False
encoding = None
#First find encoding
for test_encoding in ['utf-16', 'utf-8', 'iso-8859-1']:
try:
with io.open(filename, 'r', encoding=test_encoding) as f:
for rawrow in f:
if '#tecken=' in rawrow.lower():
row = rawrow.lstrip('#').rstrip('\n').lower()
cols = row.split('=')
encoding = cols[1]
break
if not rawrow.startswith('#'):
break
except UnicodeError:
continue
if encoding is None:
encoding = utils.ask_for_charset(
default_charset='utf-16',
msg=ru(
QCoreApplication.translate(
u'Interlab4Import',
u'Give charset used in the file %s')) % filename)
if encoding is None or not encoding:
utils.MessagebarAndLog.info(bar_msg=ru(
QCoreApplication.translate(u'Interlab4Import',
u'Charset not given, stopping.')))
raise utils.UserInterruptError()
#Parse the filedescriptor
with io.open(filename, 'r', encoding=encoding) as f:
for rawrow in f:
if not rawrow.startswith('#'):
if any(x is None
for x in (version, decimalsign, quotechar)):
file_error = True
break
row = rawrow.lstrip('#').rstrip('\n').lower()
cols = row.split(u'=')
if cols[0].lower() == u'version':
version = cols[1]
elif cols[0].lower() == u'decimaltecken':
decimalsign = cols[1]
elif cols[0].lower() == u'textavgränsare':
if cols[1].lower() == 'ja':
quotechar = '"'
return (file_error, version, encoding, decimalsign, quotechar)
def create_temporary_table_for_import(self,
temptable_name,
fieldnames_types,
geometry_colname_type_srid=None):
if not temptable_name.startswith(u'temp_'):
temptable_name = u'temp_%s' % temptable_name
existing_names = tables_columns(dbconnection=self).keys()
while temptable_name in existing_names: #this should only be needed if an earlier import failed. if so, propose to rename the temporary import-table
reponse = PyQt4.QtGui.QMessageBox.question(
None,
utils.returnunicode(
QCoreApplication.translate(
u'DbConnectionManager',
u"Warning - Table name confusion!")),
utils.returnunicode(
QCoreApplication.translate(
u'midv_data_importer',
u'''The temporary import table '%s' already exists in the current DataBase. This could indicate a failure during last import. Please verify that your table contains all expected data and then remove '%s'.\n\nMeanwhile, do you want to go on with this import, creating a temporary table '%s_2' in database?'''
)) % (self.temptable_name, self.temptable_name,
self.temptable_name),
PyQt4.QtGui.QMessageBox.Yes | PyQt4.QtGui.QMessageBox.No)
if reponse == PyQt4.QtGui.QMessageBox.Yes:
self.temptable_name = '%s_2' % self.temptable_name
else:
raise utils.UserInterruptError()
if self.dbtype == u'spatialite':
temptable_name = u'mem.' + temptable_name
self.execute(u"""ATTACH DATABASE ':memory:' AS mem""")
if geometry_colname_type_srid is not None:
geom_column = geometry_colname_type_srid[0]
geom_type = geometry_colname_type_srid[1]
srid = geometry_colname_type_srid[2]
fieldnames_types.append(u'geometry %s' %
geometry_colname_type_srid[0])
sql = u"""CREATE table %s (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, %s)""" % (
temptable_name, u', '.join(fieldnames_types))
self.execute(sql)
sql = u"""SELECT RecoverGeometryColumn('%s','%s',%s,'%s',2) from %s AS a""" % (
temptable_name, geom_column, srid, geom_type,
temptable_name)
self.execute(sql)
else:
sql = u"""CREATE table %s (%s)""" % (
temptable_name, u', '.join(fieldnames_types))
self.execute(sql)
else:
self.execute(u"""CREATE TEMPORARY table %s (%s)""" %
(temptable_name, u', '.join(fieldnames_types)))
if geometry_colname_type_srid is not None:
geom_column = geometry_colname_type_srid[0]
geom_type = geometry_colname_type_srid[1]
srid = geometry_colname_type_srid[2]
sql = u"""ALTER TABLE %s ADD COLUMN %s geometry(%s, %s);""" % (
temptable_name, geom_column, geom_type, srid)
self.execute(sql)
return temptable_name
def select_files(self):
self.charsetchoosen = utils.ask_for_charset(
default_charset=self.default_charset)
if not self.charsetchoosen:
raise utils.UserInterruptError()
files = utils.select_files(only_one_file=False,
extension="csv (*.csv)")
return files
def ask_for_CRS(self, set_locale):
# USER MUST SELECT CRS FIRST!!
if set_locale == 'sv_SE':
default_crs = 3006
else:
default_crs = 4326
EPSGID = qgis.PyQt.QtWidgets.QInputDialog.getInt(None, ru(QCoreApplication.translate('NewDb', "Select CRS")), ru(QCoreApplication.translate('NewDb', "Give EPSG-ID (integer) corresponding to\nthe CRS you want to use in the database:")),default_crs)
if not EPSGID[1]:
raise utils.UserInterruptError()
return EPSGID
def select_files_and_load_gui(self):
self.files = self.select_files()
if not self.files:
raise utils.UserInterruptError()
self.date_time_filter = DateTimeFilter(calendar=True)
self.add_row(self.date_time_filter.widget)
self.add_row(get_line())
self.skip_rows = CheckboxAndExplanation(QCoreApplication.translate('DiverofficeImport', u'Skip rows without water level'),
QCoreApplication.translate('DiverofficeImport', u'Checked = Rows without a value for columns Water head[cm] or Level[cm] will be skipped.'))
self.skip_rows.checked = True
self.add_row(self.skip_rows.widget)
self.add_row(get_line())
self.confirm_names = CheckboxAndExplanation(QCoreApplication.translate('DiverofficeImport', u'Confirm each logger obsid before import'),
QCoreApplication.translate('DiverofficeImport', u'Checked = The obsid will be requested of the user for every file.\n\n') +
QCoreApplication.translate('DiverofficeImport', u'Unchecked = the location attribute, both as is and capitalized, in the\n') +
QCoreApplication.translate('DiverofficeImport', u'file will be matched against obsids in the database.\n\n') +
QCoreApplication.translate('DiverofficeImport', u'In both case, obsid will be requested of the user if no match in the database is found.'))
self.confirm_names.checked = True
self.add_row(self.confirm_names.widget)
self.add_row(get_line())
self.import_all_data = CheckboxAndExplanation(QCoreApplication.translate('DiverofficeImport', u'Import all data'),
QCoreApplication.translate('DiverofficeImport', u'Checked = any data not matching an exact datetime in the database\n') +
QCoreApplication.translate('DiverofficeImport', u'for the corresponding obsid will be imported.\n\n') +
QCoreApplication.translate('DiverofficeImport', u'Unchecked = only new data after the latest date in the database,\n') +
QCoreApplication.translate('DiverofficeImport', u'for each observation point, will be imported.'))
self.import_all_data.checked = False
self.add_row(self.import_all_data.widget)
self.close_after_import = PyQt4.QtGui.QCheckBox(ru(QCoreApplication.translate(u'DiverofficeImport', u'Close dialog after import')))
self.close_after_import.setChecked(True)
self.gridLayout_buttons.addWidget(self.close_after_import, 0, 0)
self.start_import_button = PyQt4.QtGui.QPushButton(QCoreApplication.translate('DiverofficeImport', u'Start import'))
self.gridLayout_buttons.addWidget(self.start_import_button, 1, 0)
self.connect(self.start_import_button, PyQt4.QtCore.SIGNAL("clicked()"), lambda : self.start_import(files=self.files, skip_rows_without_water_level=self.skip_rows.checked, confirm_names=self.confirm_names.checked, import_all_data=self.import_all_data.checked, from_date=self.date_time_filter.from_date, to_date=self.date_time_filter.to_date))
self.gridLayout_buttons.setRowStretch(2, 1)
self.show()
def ask_for_stored_settings(self, stored_settings):
old_string = utils.anything_to_string_representation(
stored_settings,
itemjoiner=',\n',
pad=' ',
dictformatter='{\n%s}',
listformatter='[\n%s]',
tupleformatter='(\n%s, )')
msg = ru(
QCoreApplication.translate(
'DrillreportUi',
'Replace the settings string with a new settings string.'))
new_string = qgis.PyQt.QtWidgets.QInputDialog.getText(
None,
ru(
QCoreApplication.translate('DrillreportUi',
"Edit settings string")), msg,
qgis.PyQt.QtWidgets.QLineEdit.Normal, old_string)
if not new_string[1]:
raise utils.UserInterruptError()
new_string_text = ru(new_string[0])
if not new_string_text:
return {}
try:
as_dict = ast.literal_eval(new_string_text)
except Exception as e:
utils.MessagebarAndLog.warning(bar_msg=ru(
QCoreApplication.translate(
'DrillreportUi',
'Translating string to dict failed, see log message panel')
),
log_msg=str(e))
raise utils.UsageError()
else:
return as_dict
def __init__(self, db_settings=None):
"""
Manuals for db connectors:
https://github.com/qgis/QGIS/blob/master/python/plugins/db_manager/db_plugins/connector.py
https://github.com/qgis/QGIS/blob/master/python/plugins/db_manager/db_plugins/postgis/connector.py
https://github.com/qgis/QGIS/blob/master/python/plugins/db_manager/db_plugins/spatialite/connector.py
"""
self.conn = None
self.cursor = None
self.connector = None
if db_settings is None:
db_settings = QgsProject.instance().readEntry(
"Midvatten", "database")[0]
if isinstance(db_settings, str):
#Test if the db_setting is an old database
if os.path.isfile(db_settings):
db_settings = {'spatialite': {'dbpath': db_settings}}
else:
if not db_settings:
raise utils.UsageError(
ru(
QCoreApplication.translate(
'DbConnectionManager',
'Database setting was empty. Check DB tab in Midvatten settings.'
)))
else:
try:
db_settings = ast.literal_eval(db_settings)
except:
raise utils.UsageError(
ru(
QCoreApplication.translate(
'DbConnectionManager',
'Database could not be set. Check DB tab in Midvatten settings.'
)))
elif isinstance(db_settings, dict):
# Assume it the dict is a valid db_settings dict.
pass
else:
raise Exception(
ru(
QCoreApplication.translate(
'DbConnectionManager',
"DbConnectionManager programming error: db_settings must be either a dict like {'spatialite': {'dbpath': 'x'} or a string representation of it. Was: %s"
)) % ru(db_settings))
db_settings = ru(db_settings, keep_containers=True)
self.db_settings = db_settings
self.dbtype = list(self.db_settings.keys())[0]
self.connection_settings = list(self.db_settings.values())[0]
self.uri = QgsDataSourceUri()
if self.dbtype == 'spatialite':
self.dbpath = ru(self.connection_settings['dbpath'])
if not os.path.isfile(self.dbpath):
raise utils.UsageError(
ru(
QCoreApplication.translate(
'DbConnectionManager',
'Database error! File "%s" not found! Check db tab in Midvatten settings!'
)) % self.dbpath)
self.check_db_is_locked()
#Create the database if it's not existing
self.uri.setDatabase(self.dbpath)
try:
self.connector = spatialite_connector.SpatiaLiteDBConnector(
self.uri)
except Exception as e:
utils.MessagebarAndLog.critical(bar_msg=ru(
QCoreApplication.translate(
'DbConnectionManager',
'Connecting to spatialite db %s failed! Check that the file or path exists.'
)) % self.dbpath,
log_msg=ru(
QCoreApplication.translate(
'DbConnectionManager',
'msg %s')) % str(e))
raise
elif self.dbtype == 'postgis':
connection_name = self.connection_settings['connection'].split(
'/')[0]
self.postgis_settings = get_postgis_connections()[connection_name]
self.uri.setConnection(self.postgis_settings['host'],
self.postgis_settings['port'],
self.postgis_settings['database'],
self.postgis_settings['username'],
self.postgis_settings['password'])
try:
self.connector = postgis_connector.PostGisDBConnector(self.uri)
except Exception as e:
print(str(e))
if 'no password supplied' in str(e):
utils.MessagebarAndLog.warning(bar_msg=ru(
QCoreApplication.translate(
'DbConnectionManager',
'No password supplied for postgis connection')))
raise utils.UserInterruptError()
else:
raise
if self.connector is not None:
self.conn = self.connector.connection
self.cursor = self.conn.cursor()
def create_temporary_table_for_import(self,
temptable_name,
fieldnames_types,
geometry_colname_type_srid=None):
if not temptable_name.startswith('temp_'):
temptable_name = 'temp_%s' % temptable_name
existing_names = list(tables_columns(dbconnection=self).keys())
while temptable_name in existing_names: #this should only be needed if an earlier import failed. if so, propose to rename the temporary import-table
reponse = qgis.PyQt.QtWidgets.QMessageBox.question(
None,
ru(
QCoreApplication.translate(
'DbConnectionManager',
"Warning - Table name confusion!")),
ru(
QCoreApplication.translate(
'midv_data_importer',
'''The temporary import table '%s' already exists in the current DataBase. This could indicate a failure during last import. Please verify that your table contains all expected data and then remove '%s'.\n\nMeanwhile, do you want to go on with this import, creating a temporary table '%s_2' in database?'''
)) % (self.temptable_name, self.temptable_name,
self.temptable_name),
qgis.PyQt.QtWidgets.QMessageBox.Yes
| qgis.PyQt.QtWidgets.QMessageBox.No)
if reponse == qgis.PyQt.QtWidgets.QMessageBox.Yes:
self.temptable_name = '%s_2' % self.temptable_name
else:
raise utils.UserInterruptError()
if self.dbtype == 'spatialite':
temptable_name = 'mem.' + temptable_name
self.execute("""ATTACH DATABASE ':memory:' AS mem""")
if geometry_colname_type_srid is not None:
geom_column = geometry_colname_type_srid[0]
geom_type = geometry_colname_type_srid[1]
srid = geometry_colname_type_srid[2]
fieldnames_types.append('geometry %s' %
geometry_colname_type_srid[0])
sql = """CREATE table %s (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, %s)""" % (
temptable_name, ', '.join(fieldnames_types))
self.execute(sql)
self.conn.commit()
# This sql doesnt work for some reason, error msg "RecoverGeometryColumn() error: table 'mem.temp_temporary_section_line' does not exist"
# Doesn't seem to work with memory databases. It doesn't seem to be needed for us though.
try:
sql = """SELECT RecoverGeometryColumn('%s','%s',%s,'%s',2) from %s AS a""" % (
temptable_name, geom_column, srid, geom_type,
temptable_name)
self.execute(sql)
except:
pass
else:
sql = """CREATE table %s (%s)""" % (
temptable_name, ', '.join(fieldnames_types))
self.execute(sql)
else:
self.execute("""CREATE TEMPORARY table %s (%s)""" %
(temptable_name, ', '.join(fieldnames_types)))
if geometry_colname_type_srid is not None:
geom_column = geometry_colname_type_srid[0]
geom_type = geometry_colname_type_srid[1]
srid = geometry_colname_type_srid[2]
sql = """ALTER TABLE %s ADD COLUMN %s geometry(%s, %s);""" % (
temptable_name, geom_column, geom_type, srid)
self.execute(sql)
return temptable_name
def create_new_spatialite_db(self, verno, user_select_CRS='y', EPSG_code='4326', delete_srids=True): #CreateNewDB(self, verno):
"""Open a new DataBase (create an empty one if file doesn't exists) and set as default DB"""
utils.stop_waiting_cursor()
set_locale = self.ask_for_locale()
utils.start_waiting_cursor()
if user_select_CRS=='y':
utils.stop_waiting_cursor()
EPSGID=str(self.ask_for_CRS(set_locale)[0])
utils.start_waiting_cursor()
else:
EPSGID=EPSG_code
if EPSGID=='0' or not EPSGID:
raise utils.UserInterruptError()
# If a CRS is selectd, go on and create the database
#path and name of new db
utils.stop_waiting_cursor()
dbpath = ru(utils.get_save_file_name_no_extension(parent=None, caption="New DB",
directory="midv_obsdb.sqlite",
filter="Spatialite (*.sqlite)"))
utils.start_waiting_cursor()
if os.path.exists(dbpath):
utils.MessagebarAndLog.critical(
bar_msg=ru(QCoreApplication.translate('NewDb', 'A database with the chosen name already existed. Cancelling...')))
utils.stop_waiting_cursor()
return ''
#Create the database
conn = db_utils.connect_with_spatialite_connect(dbpath)
conn.close()
self.db_settings = ru(utils.anything_to_string_representation({'spatialite': {'dbpath': dbpath}}))
#dbconnection = db_utils.DbConnectionManager(self.db_settings)
try:
# creating/connecting the test_db
dbconnection = db_utils.DbConnectionManager(self.db_settings)
dbconnection.execute("PRAGMA foreign_keys = ON") #Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database dbconnection separately.
except Exception as e:
utils.MessagebarAndLog.critical(bar_msg=ru(QCoreApplication.translate('NewDb', "Impossible to connect to selected DataBase, see log message panel")), log_msg=ru(QCoreApplication.translate('NewDb', 'Msg:\n') + str(e)))
#utils.pop_up_info("Impossible to connect to selected DataBase")
utils.stop_waiting_cursor()
return ''
d =dbconnection.connector
#First, find spatialite version
versionstext = dbconnection.execute_and_fetchall('select spatialite_version()')[0][0]
# load sql syntax to initialise spatial metadata, automatically create GEOMETRY_COLUMNS and SPATIAL_REF_SYS
# then the syntax defines a Midvatten project db according to the loaded .sql-file
if not int(versionstext[0][0]) > 3: # which file to use depends on spatialite version installed
utils.pop_up_info(ru(QCoreApplication.translate('NewDb', "Midvatten plugin needs spatialite4.\nDatabase can not be created")))
utils.stop_waiting_cursor()
return ''
filenamestring = "create_db.sql"
SQLFile = os.path.join(os.sep,os.path.dirname(__file__),"..","definitions",filenamestring)
qgisverno = Qgis.QGIS_VERSION#We want to store info about which qgis-version that created the db
replace_word_replace_with = [('CHANGETORELEVANTEPSGID', ru(EPSGID)),
('CHANGETOPLUGINVERSION', ru(verno)),
('CHANGETOQGISVERSION', ru(qgisverno)),
('CHANGETODBANDVERSION', 'SpatiaLite version %s'%ru(versionstext)),
('CHANGETOLOCALE', ru(set_locale)),
(('SPATIALITE ', ''))]
with open(SQLFile, 'r') as f:
f.readline() # first line is encoding info....
lines = [ru(line) for line in f]
sql_lines = ['{};'.format(l) for l in ' '.join(lines).split(';') if l]
for line in sql_lines:
if all([line, not line.startswith("#"), 'POSTGIS' not in line]):
sql = self.replace_words(line, replace_word_replace_with)
try:
dbconnection.execute(sql)
except:
try:
print(str(sql))
except:
pass
raise
if delete_srids:
db_utils.delete_srids(dbconnection, EPSGID)
self.insert_datadomains(set_locale, dbconnection)
execute_sqlfile(get_full_filename("insert_obs_points_triggers.sql"), dbconnection)
execute_sqlfile(get_full_filename('qgis3_obsp_fix.sql'), dbconnection)
self.add_metadata_to_about_db(dbconnection)
#FINISHED WORKING WITH THE DATABASE, CLOSE CONNECTIONS
dbconnection.commit()
dbconnection.vacuum()
dbconnection.commit_and_closedb()
#create SpatiaLite Connection in QGIS QSettings
settings=qgis.PyQt.QtCore.QSettings()
settings.beginGroup('/SpatiaLite/dbconnections')
settings.setValue('%s/sqlitepath'%os.path.basename(dbpath),'%s'%dbpath)
settings.endGroup()
"""
#The intention is to keep layer styles in the database by using the class AddLayerStyles but due to limitations in how layer styles are stored in the database, I will put this class on hold for a while.
#Finally add the layer styles info into the data base
AddLayerStyles(dbpath)
"""
utils.stop_waiting_cursor()
def populate_postgis_db(self, verno, user_select_CRS='y', EPSG_code='4326'):
dbconnection = db_utils.DbConnectionManager()
db_settings = dbconnection.db_settings
if not isinstance(db_settings, str):
self.db_settings = ru(utils.anything_to_string_representation(dbconnection.db_settings))
else:
self.db_settings = ru(db_settings)
if dbconnection.dbtype != 'postgis':
raise utils.UsageError('Database type postgis not selected, check Midvatten settings!')
dbconnection.execute('CREATE EXTENSION IF NOT EXISTS postgis;')
result = dbconnection.execute_and_fetchall('select version(), PostGIS_full_version();')
versionstext = ', '.join(result[0])
utils.stop_waiting_cursor()
set_locale = self.ask_for_locale()
utils.start_waiting_cursor()
if user_select_CRS=='y':
utils.stop_waiting_cursor()
EPSGID=str(self.ask_for_CRS(set_locale)[0])
utils.start_waiting_cursor()
else:
EPSGID=EPSG_code
if EPSGID=='0' or not EPSGID:
raise utils.UserInterruptError()
filenamestring = "create_db.sql"
SQLFile = os.path.join(os.sep,os.path.dirname(__file__),"..","definitions",filenamestring)
qgisverno = Qgis.QGIS_VERSION#We want to store info about which qgis-version that created the db
replace_word_replace_with = [
('CHANGETORELEVANTEPSGID', ru(EPSGID)),
('CHANGETOPLUGINVERSION', ru(verno)),
('CHANGETOQGISVERSION', ru(qgisverno)),
('CHANGETODBANDVERSION', 'PostGIS version %s' % ru(versionstext)),
('CHANGETOLOCALE', ru(set_locale)),
('double', 'double precision'),
('"', ''),
('rowid as rowid', 'CTID as rowid'),
('POSTGIS ', '')]
created_tables_sqls = {}
with open(SQLFile, 'r') as f:
f.readline() # first line is encoding info....
lines = [ru(line) for line in f]
sql_lines = ['{};'.format(l) for l in ' '.join(lines).split(';') if l]
for linenr, line in enumerate(sql_lines):
if all([line, not line.startswith("#"), 'InitSpatialMetadata' not in line, 'SPATIALITE' not in line,
line.replace(';', '').strip().replace('\n', '').replace('\r', '')]):
sql = self.replace_words(line, replace_word_replace_with)
try:
dbconnection.execute(sql)
except:
try:
print(str(sql))
print("numlines: " + str(len(sql_lines)))
print("Error on line nr {}".format(str(linenr)))
print("before " + sql_lines[linenr - 1])
if linenr + 1 < len(sql_lines):
print("after " + sql_lines[linenr + 1 ])
except:
pass
raise
else:
_sql = sql.lstrip('\r').lstrip('\n').lstrip()
if _sql.startswith('CREATE TABLE'):
tablename = ' '.join(_sql.split()).split()[2]
created_tables_sqls[tablename] = sql
#lines = [self.replace_words(line.decode('utf-8').rstrip('\n').rstrip('\r'), replace_word_replace_with) for line in f if all([line,not line.startswith("#"), 'InitSpatialMetadata' not in line])]
#db_utils.sql_alter_db(lines)
self.insert_datadomains(set_locale, dbconnection)
execute_sqlfile(get_full_filename('insert_obs_points_triggers_postgis.sql'), dbconnection)
execute_sqlfile(get_full_filename('insert_functions_postgis.sql'), dbconnection)
self.add_metadata_to_about_db(dbconnection, created_tables_sqls)
dbconnection.vacuum()
dbconnection.commit_and_closedb()
"""
#The intention is to keep layer styles in the database by using the class AddLayerStyles but due to limitations in how layer styles are stored in the database, I will put this class on hold for a while.
#Finally add the layer styles info into the data base
AddLayerStyles(dbpath)
"""
utils.stop_waiting_cursor()
def __init__(self, db_settings=None):
"""
Manuals for db connectors:
https://github.com/qgis/QGIS/blob/master/python/plugins/db_manager/db_plugins/connector.py
https://github.com/qgis/QGIS/blob/master/python/plugins/db_manager/db_plugins/postgis/connector.py
https://github.com/qgis/QGIS/blob/master/python/plugins/db_manager/db_plugins/spatialite/connector.py
"""
self.conn = None
self.cursor = None
if db_settings is None:
db_settings = QgsProject.instance().readEntry(
"Midvatten", "database")[0]
if isinstance(db_settings, basestring):
#Test if the db_setting is an old database
if os.path.isfile(db_settings):
db_settings = {u'spatialite': {u'dbpath': db_settings}}
else:
if not db_settings:
# TODO: Something feels off here. It should not return None, as that will just cause other hard to solve errors.
# TODO An exception feels better but is uglier for the user.
utils.MessagebarAndLog.critical(bar_msg=utils.returnunicode(
QCoreApplication.translate(
u'DbConnectionManager',
u'Database not chosen correctly. Check DB tab in Midvatten settings.'
)))
return None
else:
try:
db_settings = ast.literal_eval(db_settings)
except:
#TODO: Something feels off here. It should not return None, as that will just cause other hard to solve errors.
#TODO An exception feels better but is uglier for the user.
utils.MessagebarAndLog.critical(
bar_msg=utils.returnunicode(
QCoreApplication.translate(
u'DbConnectionManager',
u'Database connection failed. Try reset settings.'
)))
return None
elif isinstance(db_settings, dict):
pass
else:
raise Exception(
utils.returnunicode(
QCoreApplication.translate(
u'DbConnectionManager',
u"DbConnectionManager error: db_settings must be either a dict like {u'spatialite': {u'dbpath': u'x'} or a string representation of it. Was: %s"
)) % utils.returnunicode(db_settings))
db_settings = utils.returnunicode(db_settings, keep_containers=True)
self.db_settings = db_settings
self.dbtype = self.db_settings.keys()[0]
self.connection_settings = self.db_settings.values()[0]
self.uri = QgsDataSourceURI()
if self.dbtype == u'spatialite':
self.dbpath = utils.returnunicode(
self.connection_settings[u'dbpath'])
self.check_db_is_locked()
#Create the database if it's not existing
self.uri.setDatabase(self.dbpath)
try:
self.conn = sqlite.connect(self.dbpath,
detect_types=sqlite.PARSE_DECLTYPES
| sqlite.PARSE_COLNAMES)
except:
utils.MessagebarAndLog.critical(bar_msg=utils.returnunicode(
QCoreApplication.translate(
u'DbConnectionManager',
u'Connecting to spatialite db %s failed! Check that the file or path exists.'
)) % self.dbpath)
self.conn = None
else:
try:
self.connector = spatialite_connector.SpatiaLiteDBConnector(
self.uri)
except:
pass
self.cursor = self.conn.cursor()
elif self.dbtype == u'postgis':
connection_name = self.connection_settings[u'connection'].split(
u'/')[0]
self.postgis_settings = get_postgis_connections()[connection_name]
self.uri.setConnection(self.postgis_settings[u'host'],
self.postgis_settings[u'port'],
self.postgis_settings[u'database'],
self.postgis_settings[u'username'],
self.postgis_settings[u'password'])
try:
self.connector = postgis_connector.PostGisDBConnector(self.uri)
except Exception as e:
if u'no password supplied' in str(e):
utils.MessagebarAndLog.warning(bar_msg=utils.returnunicode(
QCoreApplication.translate(
u'DbConnectionManager',
u'No password supplied for postgis connection')))
raise utils.UserInterruptError()
else:
raise
self.conn = self.connector.connection
self.cursor = self.connector._get_cursor()
