spatialite qgis and python

[heywoodstock]

Hi,
I'ts just possible into Qgis charge spatialite data through python as
we can do with PostgreSQL/Postgis, or we must to wait for new
bindings?

Thanks!

[Roy Hyunjin Han]

Can you please explain? I don't understand what you want to do.

[Luca Mandolesi]

This is the method I use into my python plugin for qgis, to load data from a postgres/postgis view.

Now I've transferred my data from postgres to sqlite, and I've builded qgis trunk for mac os x to do some test. 

I would to make the same from a sqlite/spatialite db, but I guess that now there isn't the python provider for sqlite/spatialite.

     def charge_vector_layers(self, data):

uri = QgsDataSourceURI()

uri.setConnection("localhost", "5432", "mydb", "myuser", "mypass")

# set database schema, table name, geometry column and optionaly subset (WHERE clause)

gidstr =  id_us = "id_us = " + str(data[0].id_us)

if len(data) > 1:

for i in range(len(data)):

gidstr += " OR id = " + str(data[i].id_us)

srs = QgsCoordinateReferenceSystem(3004, QgsCoordinateReferenceSystem.PostgisCrsId)

uri.setDataSource("public", "my_table_view", "the_geom", gidstr)

layer = QgsVectorLayer(uri.uri(), "My Layer label", "postgres")

layer.setCrs(srs)

if layer.isValid():

# add layer to the registry

QgsMapLayerRegistry.instance().addMapLayer(layer)

QMessageBox.warning(self,"Attenzione", "Layer loaded",  QMessageBox.Ok)

else:

QMessageBox.warning(self,"Attenzione!", "Error!",  QMessageBox.Ok)

[Douglas Mayle]

On May 12, 2009, at 6:15 PM, Luca Mandolesi wrote:

> This is the method I use into my python plugin for qgis, to load
> data from a postgres/postgis view.
> Now I've transferred my data from postgres to sqlite, and I've
> builded qgis trunk for mac os x to do some test.
> I would to make the same from a sqlite/spatialite db, but I guess
> that now there isn't the python provider for sqlite/spatialite.

Sure there is. It's a bit of a pain, but you can either compile your
own pysqlite module, editing setup.cfg to allow extensions and then
load in libspatialite. Your other choice is to edit the libspatialite
source to autoload spatialite when sqlite is loaded. I went the
second route (which is a much bigger pain), but you can find my
binaries (for Mac and 64-bit Ubuntu) here:
https://source.openplans.org/hg/communityalmanac/file/91c0b9627992/communityalmanac/lib/spatialite

[Alex Mandel]

Douglas Mayle wrote:
>
> On May 12, 2009, at 6:15 PM, Luca Mandolesi wrote:
>
>> This is the method I use into my python plugin for qgis, to load
>> data from a postgres/postgis view.
>> Now I've transferred my data from postgres to sqlite, and I've
>> builded qgis trunk for mac os x to do some test.
>> I would to make the same from a sqlite/spatialite db, but I guess
>> that now there isn't the python provider for sqlite/spatialite.
>
> Sure there is. It's a bit of a pain, but you can either compile your
> own pysqlite module, editing setup.cfg to allow extensions and then
> load in libspatialite. Your other choice is to edit the libspatialite
> source to autoload spatialite when sqlite is loaded. I went the
> second route (which is a much bigger pain), but you can find my
> binaries (for Mac and 64-bit Ubuntu) here:
> https://source.openplans.org/hg/communityalmanac/file/91c0b9627992/communityalmanac/lib/spatialite

Are you sure about that, since I installed Spatialite libs on my system,
all I do is:

from pysqlite2 import dbapi2 as sqlite3

class spatialtest():
'''Spatial database'''
def __init__(self, db):
#load an existing database
#pysqlite connection
self.con = sqlite3.connect(db)

# Load the spatialite extension of sqlite
self.con.enable_load_extension(True)
self.con.execute("select load_extension('libspatialite.so')")
self.con.enable_load_extension(False)

def close(self):
'''Close database connection'''
self.con.close()

Standard python 2.5

Alex

[Douglas Mayle]

If you look at the current version of pysqlite, in setup.cfg ( http://oss.itsystementwicklung.de/trac/pysqlite/browser/setup.cfg
):

define=SQLITE_OMIT_LOAD_EXTENSION

This line means that enable_load_extension is not included by
default. Maybe it was enabled by default in an older version, or you
built it yourself, but if you try to easy_install pysqlite, it doesn't
have this function...

[Alex Mandel]

Ah, I'm using the stock version in python 2.5, the line you reference
was only added to pysqlite2 source 2 months ago. Hope it doesn't screw
up my apps down the line for it to be off by default.

Alex

[Douglas Mayle]

You can't be using the stock version of pysqlite2 that ships with
python 2.5, because it's not available as pysqlite2 (it's available
directly as sqlite3). As of 2.5.4 (and also checked against 2.5.1 and
2.5.2), enable_load_extension isn't available in python's stock
sqlite3 module. If you can find out which version of pysqlite2 you're
using, it might be easier to specify the older version...

Doug

[Luca Mandolesi]

Thank you very musch. I'm not a programmer and compile pysqlite it's very difficult for me. 

By the way, I don't understand how you pass data to Qgis.

[Alex Mandel]

Luca Mandolesi wrote:
> Thank you very musch. I'm not a programmer and compile pysqlite it's very
> difficult for me. By the way, I don't understand how you pass data to Qgis.
>

QGIS 1.1 can read Spatialite tables directly, and have the same
programming interface as other vector layers including Postgis.
In fact spatialite compiliation is included in the new version, so if
you're only working within QGIS & Python just using the new version
should do it for you.

Alex
FYI 1.1 should be available today.

[Luca Mandolesi]

QGIS 1.1 can read Spatialite tables directly, and have the same

programming interface as other vector layers including Postgis.
In fact spatialite compiliation is included in the new version, so if
you're only working within QGIS & Python just using the new version
should do it for you.

I know it, but what I don't find is the name of the  python class or method that load data from sqlite into qgis.

In this method:

layer = QgsVectorLayer(uri.uri(), "My Layer label", "postgres")

uri.uri() do a query into Postgres as the connection call.

uri = QgsDataSourceURI()

uri.setConnection("localhost", "5432", "mydb", "myuser", "mypass")

So, I know how make a query into sqlite/spatialite db through sqlalchemy, but I don't know how pass data to QgsVectorLayer.

[Alex Mandel]

Ah, for this question it's probably a good idea to ask on the
qgis-developer <qgis-de...@lists.osgeo.org>, which I've copied on
this email.

Alex

[Luca Mandolesi]

Ok, I've just posted there!!! Thanks a lot!!!

Bye bye!