3

I managed to import a layer from a MySQL database in QGIS with PyQGIS.

Now I'm looking to import a layer from QGIS into MySQL. I explored several ways :

  1. Using ogr2ogr on the command line, the loading was really very very slow.
  2. Using the MySQL Importer plugin: I looked at the script, it also uses ogr2ogr on the command line, the result was similar.
  3. Using PyQGIS (although I think it uses ogr2ogr too). I encounter the following error:

    Unable to load mysql data provider

My code is the following :

layer = iface.activeLayer()
uri = "dbname='result' host=localhost port=3306 user='root' password='charlotte' key=id type=POINT table=\"result\".\"test\" (geom) sql="
crs = QgsCoordinateReferenceSystem()
error = QgsVectorLayerExporter.exportLayer(layer, uri, "mysql", crs, False)
if error[0] != 0:
    iface.messageBar().pushMessage(u'Error', error[1], Qgis.Critical, 5)

I looked at the documentation which specifies that the method QgsVectorLayerExporter.exportLayerwaits in 3rd parameter the providerKey. I tried several of them in vain.

I use QGIS 3.10.

  • 1
    The question is Why it is so long with ogr2ogr ? What is the volume and type of data ? Did you try to append records in a table or create a new one ? Maybe triggers, indexes in a DB can slow down import. – J. Monticolo Mar 24 at 14:55
  • I agree with you. I'm trying to import 200,000 features (gps data) by creating a new table without triggers. Plus, I'm importing on a local server. – Vincent Bré Mar 24 at 15:22
  • And how long take this import ? Is it a GPX file ? Waypoints ? Trackpoints ? – J. Monticolo Mar 24 at 15:31
  • Trackpoints. About 15 minutes. – Vincent Bré Mar 24 at 15:33
  • The fastest way will be to generate a SQL import script. Take a trackpoint record, make an INSERT script with 200 000 times this trackpoint to see, with MySQL executing this script, how long your local server take ? – J. Monticolo Mar 24 at 16:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.