def export_2_splite(self, target_db, dest_srid):
"""
Exports a datagbase to a new spatialite database file
:param target_db: The name of the new database file
:param dest_srid:
:return:
"""
self.source_dbconnection = db_utils.DbConnectionManager()
self.source_dbconnection.connect2db() #establish connection to the current midv db
self.dest_dbconnection = db_utils.DbConnectionManager(target_db)
self.dest_dbconnection.connect2db()
self.midv_data_importer = midv_data_importer()
self.write_data(self.to_sql, None, defs.get_subset_of_tables_fr_db(category='data_domains'), replace=True)
self.dest_dbconnection.commit()
self.write_data(self.to_sql, self.ID_obs_points, defs.get_subset_of_tables_fr_db(category='obs_points'))
self.dest_dbconnection.commit()
self.write_data(self.to_sql, self.ID_obs_lines, defs.get_subset_of_tables_fr_db(category='obs_lines'))
self.dest_dbconnection.commit()
db_utils.delete_srids(self.dest_dbconnection.cursor, dest_srid)
self.dest_dbconnection.commit()
#Statistics
statistics = self.get_table_rows_with_differences()
self.dest_dbconnection.cursor.execute('vacuum')
utils.MessagebarAndLog.info(bar_msg=ru(QCoreApplication.translate('ExportData', "Export done, see differences in log message panel")), log_msg=ru(QCoreApplication.translate('ExportData', "Tables with different number of rows:\n%s"))%statistics)
self.dest_dbconnection.commit_and_closedb()
self.source_dbconnection.closedb()
def export_2_splite(self, target_db, EPSG_code):
"""
Exports a datagbase to a new spatialite database file
:param target_db: The name of the new database file
:param EPSG_code:
:return:
"""
dbconnection = db_utils.DbConnectionManager()
source_db = dbconnection.dbpath
dbconnection.closedb()
conn = sqlite.connect(target_db,
detect_types=sqlite.PARSE_DECLTYPES
| sqlite.PARSE_COLNAMES)
self.curs = conn.cursor()
self.curs.execute("PRAGMA foreign_keys = ON")
self.curs.execute(r"""ATTACH DATABASE '%s' AS a""" % source_db)
conn.commit() # commit sql statements so far
old_table_column_srid = self.get_table_column_srid(prefix='a')
self.write_data(self.to_sql, self.ID_obs_points,
defs.get_subset_of_tables_fr_db(category='obs_points'),
self.verify_table_in_attached_db, 'a.')
conn.commit()
self.write_data(self.to_sql, self.ID_obs_lines,
defs.get_subset_of_tables_fr_db(category='obs_lines'),
self.verify_table_in_attached_db, 'a.')
conn.commit()
self.write_data(
self.zz_to_sql, u'no_obsids',
defs.get_subset_of_tables_fr_db(category='data_domains'),
self.verify_table_in_attached_db, 'a.')
conn.commit()
db_utils.delete_srids(self.curs, EPSG_code)
conn.commit()
#Statistics
statistics = self.get_table_rows_with_differences()
self.curs.execute(r"""DETACH DATABASE a""")
self.curs.execute('vacuum')
utils.MessagebarAndLog.info(
bar_msg=ru(
QCoreApplication.translate(
u'ExportData',
u"Export done, see differences in log message panel")),
log_msg=ru(
QCoreApplication.translate(
u'ExportData',
u"Tables with different number of rows:\n%s")) %
statistics)
conn.commit()
conn.close()
def export_2_csv(self,exportfolder):
self.source_dbconnection = db_utils.DbConnectionManager()
self.source_dbconnection.connect2db() #establish connection to the current midv db
self.exportfolder = exportfolder
self.write_data(self.to_csv, None, defs.get_subset_of_tables_fr_db(category='data_domains'))
self.write_data(self.to_csv, self.ID_obs_points, defs.get_subset_of_tables_fr_db(category='obs_points'))
self.write_data(self.to_csv, self.ID_obs_lines, defs.get_subset_of_tables_fr_db(category='obs_lines'))
self.source_dbconnection.closedb()
def __init__(self, iface, settingsdict={},group_name='Midvatten_OBS_DB'):
self.settingsdict = settingsdict
self.group_name = group_name
self.default_layers = defs.get_subset_of_tables_fr_db(category='default_layers')
self.default_nonspatlayers = defs.get_subset_of_tables_fr_db(category='default_nonspatlayers')
self.iface = iface
self.root = QgsProject.instance().layerTreeRoot()
self.remove_layers()
self.add_layers()
def export_2_csv(self,exportfolder):
database = utils.dbconnection()
database.connect2db() #establish connection to the current midv db
self.curs = database.conn.cursor()#get a cursor
self.exportfolder = exportfolder
self.write_data(self.to_csv, self.ID_obs_points, defs.get_subset_of_tables_fr_db(category='obs_points'), utils.verify_table_exists)
self.write_data(self.to_csv, self.ID_obs_lines, defs.get_subset_of_tables_fr_db(category='obs_lines'), utils.verify_table_exists)
self.write_data(self.zz_to_csv, u'no_obsids', defs.get_subset_of_tables_fr_db(category='data_domains'), utils.verify_table_exists)
database.closedb()
def __init__(self, iface, settingsdict={}, group_name='Midvatten_OBS_DB'):
self.settingsdict = settingsdict
self.group_name = group_name
self.default_layers = defs.get_subset_of_tables_fr_db(
category='default_layers')
self.default_nonspatlayers = defs.get_subset_of_tables_fr_db(
category='default_nonspatlayers')
self.iface = iface
self.root = QgsProject.instance().layerTreeRoot()
self.remove_layers()
self.add_layers()
def export_2_csv(self,exportfolder):
dbconnection = db_utils.DbConnectionManager()
dbconnection.connect2db() #establish connection to the current midv db
self.curs = dbconnection.cursor#get a cursor
self.exportfolder = exportfolder
self.write_data(self.to_csv, self.ID_obs_points, defs.get_subset_of_tables_fr_db(category='obs_points'),
lambda x: db_utils.verify_table_exists(x, dbconnection=dbconnection))
self.write_data(self.to_csv, self.ID_obs_lines, defs.get_subset_of_tables_fr_db(category='obs_lines'),
lambda x: db_utils.verify_table_exists(x, dbconnection=dbconnection))
self.write_data(self.zz_to_csv, 'no_obsids', defs.get_subset_of_tables_fr_db(category='data_domains'),
lambda x: db_utils.verify_table_exists(x, dbconnection=dbconnection))
dbconnection.closedb()
def export_2_splite(self,target_db,source_db, EPSG_code):
"""
Exports a datagbase to a new spatialite database file
:param target_db: The name of the new database file
:param source_db: The name of the source database file
:param EPSG_code:
:return:
"""
conn = sqlite.connect(target_db,detect_types=sqlite.PARSE_DECLTYPES|sqlite.PARSE_COLNAMES)
self.curs = conn.cursor()
self.curs.execute("PRAGMA foreign_keys = ON")
self.curs.execute(r"""ATTACH DATABASE '%s' AS a"""%source_db)
conn.commit() # commit sql statements so far
old_table_column_srid = self.get_table_column_srid(prefix='a')
self.write_data(self.to_sql, self.ID_obs_points, defs.get_subset_of_tables_fr_db(category='obs_points'), self.verify_table_in_attached_db, 'a.')
conn.commit()
self.write_data(self.to_sql, self.ID_obs_lines, defs.get_subset_of_tables_fr_db(category='obs_lines'), self.verify_table_in_attached_db, 'a.')
conn.commit()
self.write_data(self.zz_to_sql, u'no_obsids', defs.get_subset_of_tables_fr_db(category='data_domains'), self.verify_table_in_attached_db, 'a.')
conn.commit()
delete_srid_sql = r"""delete from spatial_ref_sys where srid NOT IN ('%s', '4326')""" % EPSG_code
try:
self.curs.execute(delete_srid_sql)
except:
utils.MessagebarAndLog.info(
log_msg=u'Removing srids failed using: ' + str(
delete_srid_sql))
conn.commit()
#Statistics
statistics = self.get_table_rows_with_differences()
self.curs.execute(r"""DETACH DATABASE a""")
self.curs.execute('vacuum')
utils.MessagebarAndLog.info("Export done, see differences in log message panel", "Tables with different number of rows:\n" + statistics)
conn.commit()
conn.close()
def export_2_csv(self, exportfolder):
dbconnection = db_utils.DbConnectionManager()
dbconnection.connect2db() #establish connection to the current midv db
self.curs = dbconnection.cursor #get a cursor
self.exportfolder = exportfolder
self.write_data(
self.to_csv, self.ID_obs_points,
defs.get_subset_of_tables_fr_db(category='obs_points'),
lambda x: db_utils.verify_table_exists(x,
dbconnection=dbconnection))
self.write_data(
self.to_csv, self.ID_obs_lines,
defs.get_subset_of_tables_fr_db(category='obs_lines'),
lambda x: db_utils.verify_table_exists(x,
dbconnection=dbconnection))
self.write_data(
self.zz_to_csv, u'no_obsids',
defs.get_subset_of_tables_fr_db(category='data_domains'),
lambda x: db_utils.verify_table_exists(x,
dbconnection=dbconnection))
dbconnection.closedb()
def export_2_splite(self,target_db, EPSG_code):
"""
Exports a datagbase to a new spatialite database file
:param target_db: The name of the new database file
:param EPSG_code:
:return:
"""
dbconnection = db_utils.DbConnectionManager()
source_db = dbconnection.dbpath
dbconnection.closedb()
conn = db_utils.connect_with_spatialite_connect(target_db)
self.curs = conn.cursor()
self.curs.execute("PRAGMA foreign_keys = ON")
self.curs.execute(r"""ATTACH DATABASE '%s' AS a"""%source_db)
conn.commit() # commit sql statements so far
old_table_column_srid = self.get_table_column_srid(prefix='a')
self.write_data(self.to_sql, self.ID_obs_points, defs.get_subset_of_tables_fr_db(category='obs_points'), self.verify_table_in_attached_db, 'a.')
conn.commit()
self.write_data(self.to_sql, self.ID_obs_lines, defs.get_subset_of_tables_fr_db(category='obs_lines'), self.verify_table_in_attached_db, 'a.')
conn.commit()
self.write_data(self.zz_to_sql, 'no_obsids', defs.get_subset_of_tables_fr_db(category='data_domains'), self.verify_table_in_attached_db, 'a.')
conn.commit()
db_utils.delete_srids(self.curs, EPSG_code)
conn.commit()
#Statistics
statistics = self.get_table_rows_with_differences()
self.curs.execute(r"""DETACH DATABASE a""")
self.curs.execute('vacuum')
utils.MessagebarAndLog.info(bar_msg=ru(QCoreApplication.translate('ExportData', "Export done, see differences in log message panel")), log_msg=ru(QCoreApplication.translate('ExportData', "Tables with different number of rows:\n%s"))%statistics)
conn.commit()
conn.close()
def add_layers_old_method(self):
"""
this method is depreceated and should no longer be used
"""
try: #newstyle
MyGroup = self.legend.addGroup ("Midvatten_OBS_DB",1,-1)
except: #olddstyle
MyGroup = self.legend.addGroup ("Midvatten_OBS_DB")
uri = QgsDataSourceURI()
uri.setDatabase(self.settingsdict['database'])#MacOSX fix1 #earlier sent byte string, now intending to send unicode string
for tablename in self.default_nonspatlayers: # first the non-spatial tables, THEY DO NOT ALL HAVE CUSTOM UI FORMS
firststring= 'dbname="' + self.settingsdict['database'] + '" table="' + tablename + '"'#MacOSX fix1 #earlier sent byte string, now unicode
layer = QgsVectorLayer(firststring,tablename, 'spatialite') # Adding the layer as 'spatialite' and not ogr vector layer is preferred
if not layer.isValid():
qgis.utils.iface.messageBar().pushMessage("Error","""Failed to load layer %s!"""%tablename,2)
else:
QgsMapLayerRegistry.instance().addMapLayers([layer])
group_index = self.legend.groups().index('Midvatten_OBS_DB')
self.legend.moveLayer (self.legend.layers()[0],group_index)
filename = tablename + ".qml" # load styles
stylefile = os.path.join(os.sep,os.path.dirname(__file__),"..","definitions",filename)
layer.loadNamedStyle(stylefile)
if tablename in ('w_levels','w_flow','stratigraphy'):
if utils.getcurrentlocale()[0] == 'sv_SE': #swedish forms are loaded only if locale settings indicate sweden
filename = tablename + ".ui"
else:
filename = tablename + "_en.ui"
try: # python bindings for setEditorLayout were introduced in qgis-master commit 9183adce9f257a097fc54e5a8a700e4d494b2962 november 2012
layer.setEditorLayout(2)
except:
pass
uifile = os.path.join(os.sep,os.path.dirname(__file__),"..","ui",filename)
layer.setEditForm(uifile)
formlogic = "form_logics." + tablename + "_form_open"
layer.setEditFormInit(formlogic)
for tablename in self.default_layers: # then the spatial ones, NOT ALL HAVE CUSTOM UI FORMS
uri.setDataSource('',tablename, 'Geometry')
layer = QgsVectorLayer(uri.uri(), tablename, 'spatialite') # Adding the layer as 'spatialite' instead of ogr vector layer is preferred
if not layer.isValid():
qgis.utils.iface.messageBar().pushMessage("Error","""Failed to load layer %s!"""%tablename,2)
else:
filename = tablename + ".qml"
stylefile = os.path.join(os.sep,os.path.dirname(__file__),"..","definitions",filename)
layer.loadNamedStyle(stylefile)
if tablename in defs.get_subset_of_tables_fr_db(category='default_layers_w_ui'): #= THE ONES WITH CUSTOM UI FORMS
if utils.getcurrentlocale()[0] == 'sv_SE': #swedish forms are loaded only if locale settings indicate sweden
filename = tablename + ".ui"
else:
filename = tablename + "_en.ui"
uifile = os.path.join(os.sep,os.path.dirname(__file__),"..","ui",filename)
try: # python bindings for setEditorLayout were introduced in qgis-master commit 9183adce9f257a097fc54e5a8a700e4d494b2962 november 2012
layer.setEditorLayout(2)
except:
pass
layer.setEditForm(uifile)
if tablename in ('obs_points','obs_lines'):
formlogic = "form_logics." + tablename + "_form_open"
layer.setEditFormInit(formlogic)
QgsMapLayerRegistry.instance().addMapLayers([layer])
group_index = self.legend.groups().index('Midvatten_OBS_DB') # SIPAPI UPDATE 2.0
self.legend.moveLayer (self.legend.layers()[0],group_index)
if tablename == 'obs_points':#zoom to obs_points extent
qgis.utils.iface.mapCanvas().setExtent(layer.extent())
elif tablename == 'w_lvls_last_geom':#we do not want w_lvls_last_geom to be visible by default
self.legend.setLayerVisible(layer,False)
def add_layers_old_method(self):
"""
this method is depreceated and should no longer be used
"""
try: #newstyle
MyGroup = self.legend.addGroup("Midvatten_OBS_DB", 1, -1)
except: #olddstyle
MyGroup = self.legend.addGroup("Midvatten_OBS_DB")
uri = QgsDataSourceUri()
uri.setDatabase(
self.settingsdict['database']
) #MacOSX fix1 #earlier sent byte string, now intending to send unicode string
for tablename in self.default_nonspatlayers: # first the non-spatial tables, THEY DO NOT ALL HAVE CUSTOM UI FORMS
firststring = 'dbname="' + self.settingsdict[
'database'] + '" table="' + tablename + '"' #MacOSX fix1 #earlier sent byte string, now unicode
layer = QgsVectorLayer(
firststring, self.dbtype
) # Adding the layer as 'spatialite' and not ogr vector layer is preferred
if not layer.isValid():
utils.MessagebarAndLog.critical(
bar_msg='Error, Failed to load layer %s!' % tablename)
else:
QgsProject.instance().addMapLayers([layer])
group_index = self.legend.groups().index('Midvatten_OBS_DB')
self.legend.moveLayer(self.legend.layers()[0], group_index)
filename = tablename + ".qml" # load styles
stylefile = os.path.join(os.sep, os.path.dirname(__file__),
"..", "definitions", filename)
layer.loadNamedStyle(stylefile)
if tablename in ('w_levels', 'w_flow', 'stratigraphy'):
if utils.getcurrentlocale(
)[0] == 'sv_SE': #swedish forms are loaded only if locale settings indicate sweden
filename = tablename + ".ui"
else:
filename = tablename + "_en.ui"
try: # python bindings for setEditorLayout were introduced in qgis-master commit 9183adce9f257a097fc54e5a8a700e4d494b2962 november 2012
layer.setEditorLayout(2)
except:
pass
uifile = os.path.join(os.sep, os.path.dirname(__file__),
"..", "ui", filename)
layer.setEditForm(uifile)
formlogic = "form_logics." + tablename + "_form_open"
layer.setEditFormInit(formlogic)
for tablename in self.default_layers: # then the spatial ones, NOT ALL HAVE CUSTOM UI FORMS
uri.setDataSource('', tablename, 'Geometry')
layer = QgsVectorLayer(
uri.uri(), self.dbtype
) # Adding the layer as 'spatialite' instead of ogr vector layer is preferred
if not layer.isValid():
utils.MessagebarAndLog.critical(
bar_msg='Error, Failed to load layer %s!' % tablename)
else:
filename = tablename + ".qml"
stylefile = os.path.join(os.sep, os.path.dirname(__file__),
"..", "definitions", filename)
layer.loadNamedStyle(stylefile)
if tablename in defs.get_subset_of_tables_fr_db(
category='default_layers_w_ui'
): #= THE ONES WITH CUSTOM UI FORMS
if utils.getcurrentlocale(
)[0] == 'sv_SE': #swedish forms are loaded only if locale settings indicate sweden
filename = tablename + ".ui"
else:
filename = tablename + "_en.ui"
uifile = os.path.join(os.sep, os.path.dirname(__file__),
"..", "ui", filename)
try: # python bindings for setEditorLayout were introduced in qgis-master commit 9183adce9f257a097fc54e5a8a700e4d494b2962 november 2012
layer.setEditorLayout(2)
except:
pass
layer.setEditForm(uifile)
if tablename in ('obs_points', 'obs_lines'):
formlogic = "form_logics." + tablename + "_form_open"
layer.setEditFormInit(formlogic)
QgsProject.instance().addMapLayers([layer])
group_index = self.legend.groups().index(
'Midvatten_OBS_DB') # SIPAPI UPDATE 2.0
self.legend.moveLayer(self.legend.layers()[0], group_index)
if tablename == 'obs_points': #zoom to obs_points extent
qgis.utils.iface.mapCanvas().setExtent(layer.extent())
elif tablename == 'w_lvls_last_geom': #we do not want w_lvls_last_geom to be visible by default
self.legend.setLayerVisible(layer, False)
