Thursday, August 22, 2013

Creating a SQlite database via 10.2 ArcPy

At ArcGIS 10.2, there is not support for SQLite database, according to Wikipedia is described as follows:

is a relational database management system contained in a small (~350 KB) C programming library. In contrast to other database management systems, SQLite is not a separate process that is accessed from the client application, but an integral part of it.
To create a SQLite database, use the CreateSQLiteDatabase().
 
import arcpy
# Set local variables
sqlite_database_path = 'C:/Data/Counties.sqlite'
# Execute CreateSQLiteDatabase
arcpy.gp.CreateSQLiteDatabase(sqlite_database_path, "ST_GEOMETRY")

It's not too hard.  All file paths must end in '.sqlite' extension.  The database supports two spatial types: "ST_GEOMETRY" or "SPATIALITE".  ST_GEOMETRY is Esri's storage type, whereas SPATIALITE is the SpatiaLite geometry type.  ST_GEOMETRY is the default geometry storage type.

This database can be fully used with the python sqlite3 module that comes as a default with any python installation.  This means you can create a database using ArcPy, then perform all updates through native SQL instead of update/insert cursors.

Some notes:

  • ":memory:" stored databases are not supported, they must be written to disk
  • SQLite version 3.16.2


Enjoy