Attempting to use a plugin in QGIS (Midvatten) that utilizes splite. I am attempting to create a database in a different Coordinate reference system than the plugin defaults to, I'm getting "foreign key constraint failed" as my error. In the code it mentions something about the keys being turned off at default and having to enable the keys for each database connection separately.
I downloaded the SpatiaLite GUI in hopes to figure something out there but it seems like the installation of the that is involved on its own.
Can anyone steer me in the direction that I need to go in order to get this database created? Inserted is the error and the bit of code that I'm guessing I have to adjust, I've tried a few different things but so far nothing has worked.
Traceback (most recent call last):
File "C:/Users/spencerjr/.qgis2/python/plugins\midvatten\midvatten.py", line 654, in new_db
newdbinstance = newdb(verno)
File "C:\Users\spencerjr\.qgis2\python\plugins\midvatten\tools\create_db.py", line 35, in __init__
self.create_new_db(verno,user_select_CRS,EPSG_code)#CreateNewDB(verno)
File "C:\Users\spencerjr\.qgis2\python\plugins\midvatten\tools\create_db.py", line 78, in create_new_db
self.rs = self.cur.execute(line.replace('CHANGETORELEVANTEPSGID',str(EPSGID)).replace('CHANGETOPLUGINVERSION',str(verno)).replace('CHANGETOQGISVERSION',str(qgisverno)).replace('CHANGETOSPLITEVERSION',str(versionstext[0][0]))) # use tags to find and replace SRID and versioning info
IntegrityError: foreign key constraint failed
Python version: 2.7.5 (default, May 15 2013, 22:44:16) [MSC v.1500 64 bit (AMD64)]
QGIS version: 2.8.1-Wien Wien, exported
import PyQt4.QtCore
import PyQt4.QtGui
from qgis.core import QGis
import os
import locale
from pyspatialite import dbapi2 as sqlite# pyspatialite is absolutely necessary (sqlite3 not enough) due to InitSpatialMetaData()
import datetime
#plugin modules
import midvatten_utils as utils
class newdb():
def __init__(self, verno, user_select_CRS='y', EPSG_code='4326'):
self.dbpath = ''
self.create_new_db(verno,user_select_CRS,EPSG_code)#CreateNewDB(verno)
def create_new_db(self, verno, user_select_CRS='y', EPSG_code='4326'):#CreateNewDB(self, verno):
"""Open a new DataBase (create an empty one if file doesn't exists) and set as default DB"""
if user_select_CRS=='y':
EPSGID=str(self.ask_for_CRS()[0])
else:
EPSGID=EPSG_code
PyQt4.QtGui.QApplication.setOverrideCursor(PyQt4.QtCore.Qt.WaitCursor)
if EPSGID=='0' or not EPSGID:
utils.pop_up_info("Cancelling...")
else: # If a CRS is selectd, go on and create the database
#path and name of new db
self.dbpath = PyQt4.QtGui.QFileDialog.getSaveFileName(None, "New DB","midv_obsdb.sqlite","Spatialite (*.sqlite)")
if not self.dbpath:
PyQt4.QtGui.QApplication.restoreOverrideCursor()
return ''
#create Spatialite database
else:
try:
# creating/connecting the test_db
self.conn = sqlite.connect(self.dbpath)
# creating a Cursor
self.cur = self.conn.cursor()
self.cur.execute("PRAGMA foreign_keys = ON") #Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection separately.
except:
utils.pop_up_info("Impossible to connect to selected DataBase")
return ''
PyQt4.QtGui.QApplication.restoreOverrideCursor()
#First, find spatialite version
versionstext = self.cur.execute('select spatialite_version()').fetchall()
#print versionstext#debug
# 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 int(versionstext[0][0][0]) > 3: # which file to use depends on spatialite version installed
SQLFile = os.path.join(os.sep,os.path.dirname(__file__),"..","definitions","create_db_splite4.sql")
else:
SQLFile = os.path.join(os.sep,os.path.dirname(__file__),"..","definitions","create_db.sql")
qgisverno = QGis.QGIS_VERSION#We want to store info about which qgis-version that created the db
f = open(SQLFile, 'r')
linecounter = 1
for line in f:
if linecounter > 1: # first line is encoding info....
self.rs = self.cur.execute(line.replace('CHANGETORELEVANTEPSGID',str(EPSGID)).replace('CHANGETOPLUGINVERSION',str(verno)).replace('CHANGETOQGISVERSION',str(qgisverno)).replace('CHANGETOSPLITEVERSION',str(versionstext[0][0]))) # use tags to find and replace SRID and versioning info
linecounter += 1
self.cur.execute("PRAGMA foreign_keys = ON")
#FINISHED WORKING WITH THE DATABASE, CLOSE CONNECTIONS
self.rs.close()
self.conn.close()
#create SpatiaLite Connection in QGIS QSettings
settings=PyQt4.QtCore.QSettings()
settings.beginGroup('/SpatiaLite/connections')
#settings.setValue(u'%s/sqlitepath'%os.path.basename(str(self.dbpath)),'%s'%self.dbpath)
settings.setValue(u'%s/sqlitepath'%os.path.basename(self.dbpath),'%s'%self.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(self.dbpath)
"""
