ΘΕΜΑ: Convert Access Database to SQLite

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# module : mdb_to_sqlite.py
# author : Panos Mavrogiorgos (gmail - pmav99)
# licence : BSD
 
# pylint: disable= C0301
 
"""
A simple script to convert an Access database to sqlite3 database using pyodbc.
 
It just copies the tables and their data not the relationships (primary keys,
foreign keys, etc).:
 
It hasn't been thoroughly tested. Probably won't work for all the data types.
But it should serve as a basis for more complex scripts.
"""
 
from __future__ import division
from __future__ import print_function
from __future__ import unicode_literals
 
import sqlite3
import pyodbc
 
# The path to the databases files. The sqlite database
SQL_FILE = "path\to\sqlite\database.db"
MDB_FILE = "path\to\access\database.mdb"
 
# pyodbc's connection string is diferrent for python x86 and x64. Use the \
# appropriate one. More info here : http://code.google.com/p/pyodbc/issues/detail?id=203
# The string for x86
MDB_STRING = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" + MDB_FILE
# The string for x64
#MDB_STRING = "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + MDB_FILE
 
 
def convert(mdb_string, sql_file):
    """
    Converts an Access database to a SQLite database.
    """
    # Make sqlite connections
    sqlite_connection = sqlite3.connect(sql_file)
    sqlite_cursor = sqlite_connection.cursor()
 
    # Make mdb connections
    mdb_connection = pyodbc.connect(mdb_string, autocommit=False)
    mdb_cursor = mdb_connection.cursor()
    tables = [row.table_name for row in mdb_cursor.tables()]
 
    for table in tables:
        # Access databases, have several internal tables. They all start with the
        # "MSys" prefix. If you need them, just remove the if clause.
        if not table.startswith("MSys"):
            ## Create tables
            columns = [column for column in mdb_cursor.columns(table=table)]
            s = []
            for column in columns:
                # Quoting table names with braces.
                s.append("%s %s(%s)" % ("[" + column.column_name + "]",
                                        column.type_name,
                                        column.column_size))
            creation_string = ("CREATE TABLE [%s] (\n" % table +
                               ",\n".join(s) +
                               "\n);")
            print(creation_string, "\n")
            sqlite_cursor.execute(creation_string)
 
            ## Insert values
            # select everything from the mdb-table
            rows = [row for row in mdb_cursor.execute("SELECT * FROM [%s];" % table)]
            # Check if the table has data. If it doesn't go to the next table, else
            # insert them to the sqlite database.
            try:
                length = len(rows[0])
            except IndexError:
                pass
            else:
                insertion_string = "insert into [%s] values " % table
                insertion_string += "(" + ", ".join(["?" for i in range(length)]) + ")"
                print(insertion_string, "\n")
                sqlite_connection.executemany(insertion_string, rows)
 
    # close databases
    sqlite_connection.commit()
    sqlite_cursor.close()
    mdb_cursor.close()
 
if __name__ == "__main__":
    convert(MDB_STRING, SQL_FILE)