4

I am still a novice in python, but I have the long term goal to create a plugin one day. The first step to this plugin is a test with QGIS python scripts to convert an Access mdb into sqlite db and then to proceed from there.

I found a python script here that will dump an Access mdb into a sqlite db.

The script is normally called from commandline with this patter: python AccessDump.py access.mdb | sqlite3 new.db

I now try to change the script, so i can run it as a QGIS script from the Processing Toolbox, but I really lack the basics so far.

Original script:

#!/usr/bin/env python
#
# AccessDump.py
# A simple script to dump the contents of a Microsoft Access Database.
# It depends upon the mdbtools suite:
#   http://sourceforge.net/projects/mdbtools/

import sys, subprocess, os

DATABASE = sys.argv[1]

# Dump the schema for the DB
subprocess.call(["mdb-schema", DATABASE, "mysql"])

# Get the list of table names with "mdb-tables"
table_names = subprocess.Popen(["mdb-tables", "-1", DATABASE],
                               stdout=subprocess.PIPE).communicate()[0]
tables = table_names.splitlines()

print "BEGIN;" # start a transaction, speeds things up when importing
sys.stdout.flush()

# Dump each table as a CSV file using "mdb-export",
# converting " " in table names to "_" for the CSV filenames.
for table in tables:
    if table != '':
        subprocess.call(["mdb-export", "-I", "mysql", DATABASE, table])

print "COMMIT;" # end the transaction
sys.stdout.flush()

I already understood the basic mechanism to first read out the table schema of the .mdb for each table, and then export tables content into the newly created db. However, I have no clue how to change the script so that I do not need that | sqlite3 new.db - part from the original shell command, but integrate it into the script, respectivly change new.db to be the output variable from the script dialog.

##Database=group
##Access.mdb to SqLite=name
##inputmdb= input file
##outputsqlite = output file
3
  • well, the script heavily depends on the pipe-operator. so i guess instead of sending everything just to stdout via subprocess.call() you need to pipe them back into a variable (take the table names as example) and execute the sql-commands via executescript() from within python. – ymirsson Nov 18 '15 at 13:55
  • @ymirsson Thanks for your comment. As I already stated, I'm a complete beginner with all this. Is there a way to replace those subprocess.call stuff by sth else? The script doesn't have to look like it does now, but just do what it is intended for, but preferably more conveniently through a QGIS Processing script. Sorry, I'm still really blond :) – Bernd V. Nov 18 '15 at 14:22
  • nope. you want to run a shell command and fetch its stdout. no way around the subprocess module in python ;) – ymirsson Nov 18 '15 at 15:12
3

This should work:

##Database=group
##Access.mdb to SqLite=name
##inputmdb= input file
##outputsqlite = output file

import sys, subprocess, os, sqlite3 as sql

mdb_name = inputmdb
sql_name = outputsqlite

try:
    print "\nopening db"
    conn = sql.connect(sql_name)
    curs = conn.cursor()

    print "\ncreating schema"
    cmds = subprocess.Popen(["mdb-schema", mdb_name, "mysql"], stdout=subprocess.PIPE).communicate()[0]
    curs.executescript(cmds)
    conn.commit()

    print "\nfetching table names"
    table_names = subprocess.Popen(["mdb-tables", "-1", mdb_name], stdout=subprocess.PIPE).communicate()[0]
    tables = table_names.splitlines()

    print "\npopulate tables"
    for table in tables:
        if table != '':
            cmds = subprocess.Popen(["mdb-export", "-I", "mysql", mdb_name, table], stdout=subprocess.PIPE).communicate()[0]
            curs.executescript("BEGIN;" + cmds + "COMMIT;")
    conn.commit()
    print "\nhoooray"

except Exception, e:
    print "oh noes:"
    print e
finally:    
    print "\nclosing DB"
    conn.close()
1
  • Thanx for your help! I still had to adopt the script. Instead of sys.argv[1] and [2], I want to use my inputmdb and outputsqlite variables to be able to have it run through the Processing Toolbox. I dare to change your answer to reflect that. – Bernd V. Nov 19 '15 at 18:40

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.