Load Esri file geodatabase feature classes into SQLite spatial table

gdb_to_sqlite.py

from __future__ import print_function

import sys
print(sys.version)
print(sys.executable)

import os
import sqlite3
import arcpy
from time import strftime

import time
from functools import wraps

def report_time(func):
    '''Decorator reporting the execution time'''
    @wraps(func)
    def wrapper(*args, **kwargs):
        start = time.time()
        result = func(*args, **kwargs)
        end = time.time()
        print(func.__name__, round(end-start,3))
        return result
    return wrapper

#----------------------------------------------------------------------
def enable_spatial(conn):
    #allow loading extensions
    conn.enable_load_extension(True)

    #loading the ST_Geometry dll to support Esri ST_Geometry geometry type
    dll_path = r'c:\Program Files (x86)\ArcGIS\Desktop10.4\DatabaseSupport\SQLite\Windows32\stgeometry_sqlite.dll'
    run('''SELECT load_extension('{dll_path}','SDE_SQL_funcs_init');'''.format(dll_path=dll_path))
    run('''SELECT CreateOGCTables();''')
    conn.commit()
    return conn

#----------------------------------------------------------------------
def run(SQL=None):
    return conn.execute(SQL).fetchall()

@report_time
#----------------------------------------------------------------------
def fc_to_sql(conn,source_fc):
    '''load a gdb feature class into a sqlite table with ST_Geometry type'''
    conn = enable_spatial(conn)

    #getting info about the source feature class
    source_epsg = arcpy.Describe(source_fc).spatialReference.factoryCode
    source_basename = arcpy.Describe(source_fc).baseName
    source_shapetype = arcpy.Describe(source_fc).shapeType
    source_oid = arcpy.Describe(source_fc).OIDFieldName

    #map field types from arcpy.ListFields() to sqlite, based on
    #http://desktop.arcgis.com/en/arcmap/10.3/manage-data/databases/dbms-data-types-supported.htm#ESRI_SECTION1_E1310ADFB340464485BA2D2D167C9AE4
    arcgis_sqlite_types_mappings = {'Date':'realdate','Double':'float64','Single':'float64',
                                    'Integer':'int32','SmallInteger':'int16','String':'text',
                                    'OID':'int32'}

    arcgis_sqlite_geometry_mappings = {'Polyline':'MultiLinestring','Point':'Point',
                                       'Multipoint':'Multipoint','Polygon':'MultiPolygon'}

    geometry_columns = ('shape','shape_area','shape_length')

    #use SQL to create a table
    columns = ['{} {}'.format(field.name,arcgis_sqlite_types_mappings[field.type])
               for field in arcpy.ListFields(source_fc) if field.name.lower() not in
               geometry_columns]

    #creating the table (with all columns except the geometry column)
    run('''CREATE TABLE {table} ({columns});'''.format(table=source_basename,
                                                       columns=','.join(columns)))

    #adding the Shape column
    shape_type = arcgis_sqlite_geometry_mappings[source_shapetype]
    sql_add_geom_col = '''SELECT AddGeometryColumn(null,'{table}','Shape',{epsg_code},'{shape_type}','xy','null');'''
    run(sql_add_geom_col.format(table=source_basename,
                                epsg_code=source_epsg,
                                shape_type=shape_type))

    #getting a list of column names to store the data
    data_columns = [str(field.name) for field in arcpy.ListFields(source_fc)
                    if field.name.lower() not in geometry_columns]

    #creating a list of data rows (all attributes plus WKT of Shape)
    rows = (r for r in arcpy.da.SearchCursor(source_fc,data_columns+['SHAPE@WKT']))

    #insert attribute values into the SQL table
    shape_type='st_{}'.format(arcgis_sqlite_geometry_mappings[source_shapetype])
    insert_values = ','.join(['?' for i in xrange(len(data_columns))] +
                             ['{shape_type}(?,{epsg})'.format(shape_type=shape_type,
                                                              epsg=source_epsg)])

    sql_insert_rows = '''Insert into {table_name} ({columns}) values ({insert_values})'''
    conn.executemany(sql_insert_rows.format(table_name=source_basename,
                                            columns=','.join(data_columns+['Shape']),
                                            insert_values=insert_values),rows)
    conn.commit()
    return conn
#----------------------------------------------------------------------

#create new sqlite database file
conn = sqlite3.connect(r'C:\GIS\_sqlites\SpatialDb{}.sqlite'.format(strftime("%Y%m%d_%H%M%S")))

#run loading data on feature classes of various size
test_fcs = [r'C:\GIS\Data.gdb\FeaturesCount1K',
            r'C:\GIS\Data.gdb\FeaturesCount10K',
            r'C:\GIS\Data.gdb\FeaturesCount100K',
            r'C:\GIS\Data.gdb\FeaturesCount1000K']

for fc in test_fcs:
    #load the fc into sqlite
    print(fc.split('\\')[-1])
    conn = fc_to_sql(conn, fc)
    #get some spatial data from tables
    print(run('''select {OID},ST_ASTEXT(SHAPE) from {table} LIMIT 1'''.format(OID=arcpy.Describe(fc).OIDFieldName,
                                                                              table=arcpy.Describe(fc).baseName)))
#FeaturesCount1K
#fc_to_sql 3.387
#[(1, u'POINT ( 25.698913574 174.868286133)')]
#FeaturesCount10K
#fc_to_sql 1.239
#[(1, u'POINT ( 160.473083496 199.991882324)')]
#FeaturesCount100K
#fc_to_sql 4.116
#[(1, u'POINT ( 20.019287109 194.078674316)')]
#FeaturesCount1000K
#fc_to_sql 32.519
#[(1, u'POINT ( 136.532104492 70.4584960940001)')]