# Connecting to adhoc SQLite database from Jython? **URL:** \ **Category:** Ignition\ **Created:** [November 3, 2021, 9:37pm UTC](https://forum.inductiveautomation.com/t/connecting-to-adhoc-sqlite-database-from-jython/52698 "2021-11-03T21:37:01Z")\ **Posts on this page:** 15\ **Page:** 1
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [November 3, 2021, 9:37pm UTC](https://forum.inductiveautomation.com/t/connecting-to-adhoc-sqlite-database-from-jython/52698/1 "2021-11-03T21:37:01Z")
Has anyone successfully connected to an adhoc SQLite database before in Jython? I would normally use the sqlite3 library but it’s C-based. I only want to connect to it briefly to pull out some config. If you must know, I want to connect to the Ignition config.idb to export some tables to JSON format so that I can document configuration and also compare config between gateway, so I want to get in and out. Otherwise, would creating a connection to it via the gateway config cause issues? I would keep it disabled until I needed it after which I would disabled it again. ---
**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\ **Post date:** [November 3, 2021, 10:29pm UTC](https://forum.inductiveautomation.com/t/connecting-to-adhoc-sqlite-database-from-jython/52698/2 "2021-11-03T22:29:02Z")
You can’t make additional connections to the config.idb (open the file) while Ignition is running. ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [November 3, 2021, 10:33pm UTC](https://forum.inductiveautomation.com/t/connecting-to-adhoc-sqlite-database-from-jython/52698/3 "2021-11-03T22:33:01Z")
Well that sounds slightly prohibitive then for what I’m doing 🙂 Also I’m sure I knew that already… I’ll have to go to plan B and copy the db or use a gateway backup’s copy instead and I’ll connect to it via the gateway config method. Cheers ---
**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\ **Post date:** [November 3, 2021, 10:37pm UTC](https://forum.inductiveautomation.com/t/connecting-to-adhoc-sqlite-database-from-jython/52698/4 "2021-11-03T22:37:24Z")
Use the SDK APIs. If you can get a `GatewayContext`, you can call `GatewayContext::getLocalDBInterface` and then run queries on it. Danger zone. [https://files.inductiveautomation.com/sdk/javadoc/ignition81/8.1.11/com/inductiveautomation/ignition/gateway/localdb/DBInterface.html#runQuery(java.lang.String)](https://files.inductiveautomation.com/sdk/javadoc/ignition81/8.1.11/com/inductiveautomation/ignition/gateway/localdb/DBInterface.html#runQuery(java.lang.String)) ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [November 3, 2021, 10:43pm UTC](https://forum.inductiveautomation.com/t/connecting-to-adhoc-sqlite-database-from-jython/52698/5 "2021-11-03T22:43:39Z")
Is there any danger in running SELECT \* queries on a few small tables (SYSPROPS/DEVICESETTINGS/ others, **NOT** any tags tables)? ---
**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\ **Post date:** [November 3, 2021, 10:44pm UTC](https://forum.inductiveautomation.com/t/connecting-to-adhoc-sqlite-database-from-jython/52698/6 "2021-11-03T22:44:09Z")
Should be okay… 😬 ---
**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\ **Post date:** [November 3, 2021, 10:44pm UTC](https://forum.inductiveautomation.com/t/connecting-to-adhoc-sqlite-database-from-jython/52698/7 "2021-11-03T22:44:42Z")
You can also poke around from [http://localhost:8088/web/status/sys.internaldb](http://localhost:8088/web/status/sys.internaldb) ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [November 3, 2021, 10:47pm UTC](https://forum.inductiveautomation.com/t/connecting-to-adhoc-sqlite-database-from-jython/52698/8 "2021-11-03T22:47:01Z")
Cheers, I saw that on the forum a year or so ago but I never actually tried it out. I need to get the results into python though so will be easier to query in script. We have a test environment, so here goes! ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [November 3, 2021, 11:21pm UTC](https://forum.inductiveautomation.com/t/connecting-to-adhoc-sqlite-database-from-jython/52698/9 "2021-11-03T23:21:34Z")
Hmm, I’m getting an error trying to run runQuery: ```auto SQLFeatureNotSupportedException: java.sql.SQLFeatureNotSupportedException ``` ```auto from com.inductiveautomation.ignition.gateway import IgnitionGateway import traceback context = IgnitionGateway.get() dbi = context.getLocalDBInterface() try: rs = dbi.runQuery('SELECT * FROM SYSPROPS') except: err = traceback.format_exc() system.tag.writeBlocking('[.]test', err) ``` I’m running it for testing from a tag change script. Tag is a memory bool tag that I just toggle to execute it ---
**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\ **Post date:** [November 3, 2021, 11:54pm UTC](https://forum.inductiveautomation.com/t/connecting-to-adhoc-sqlite-database-from-jython/52698/10 "2021-11-03T23:54:51Z")
Hmm. I don’t know why that doesn’t work… maybe I lead you down the wrong path 😓 ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [November 4, 2021, 12:06am UTC](https://forum.inductiveautomation.com/t/connecting-to-adhoc-sqlite-database-from-jython/52698/11 "2021-11-04T00:06:43Z")
```python session = IgnitionGateway.get().getPersistenceInterface().getSession() try: session.rawQueryMaps("SELECT * FROM SYSPROPS") finally: session.close() ``` You could try this; see [`SSessionJdbc`](https://files.inductiveautomation.com/sdk/javadoc/ignition81/8.1.11/simpleorm/sessionjdbc/SSessionJdbc.html#rawQueryMaps(java.lang.String,boolean,java.lang.Object...)). Make _very sure_ you `close` the session, or this will hose a running gateway until restarted. ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [November 4, 2021, 12:15am UTC](https://forum.inductiveautomation.com/t/connecting-to-adhoc-sqlite-database-from-jython/52698/12 "2021-11-04T00:15:21Z")
Would a context manager work here to close the session for me? ```auto with session.rawQueryMaps('...') as rs: #stuff pass ``` ? ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [November 4, 2021, 12:21am UTC](https://forum.inductiveautomation.com/t/connecting-to-adhoc-sqlite-database-from-jython/52698/13 "2021-11-04T00:21:49Z")
`session` is an `AutoCloseable`, which in Java means you can use `try-with-resources` for the same basic pattern as a context manager. Unfortunately, I think Jython predates try-with-resources, and there’s no ready-made context manager… although `contextlib.closing` may ‘just work’, since the method is just called `close()`? [https://docs.python.org/2.7/library/contextlib.html#contextlib.closing](https://docs.python.org/2.7/library/contextlib.html#contextlib.closing) So it would be `with contextlib.closing(persistenceInterface.getSession()) as session:`, then the `rawQueryMaps` call inside the block. ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [November 4, 2021, 12:22am UTC](https://forum.inductiveautomation.com/t/connecting-to-adhoc-sqlite-database-from-jython/52698/14 "2021-11-04T00:22:40Z")
This worked, I need to provide the other 2 args though: ```auto session = IgnitionGateway.get().getPersistenceInterface().getSession() try: session.rawQueryMaps("SELECT * FROM SYSPROPS", True, []) # sql, flush, params finally: session.close() ``` Not entirely sure what flush does… ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [November 4, 2021, 12:23am UTC](https://forum.inductiveautomation.com/t/connecting-to-adhoc-sqlite-database-from-jython/52698/15 "2021-11-04T00:23:37Z")
Flush should be pretty much irrelevant to a select…