topic Re: Splunk6 Django app -- Importing SQLite in Splunk Dev

Splunk6 Django app -- Importing SQLite

joshuamcqueen — Thu, 31 Oct 2013 22:56:46 GMT

Hey Splunk Gurus,

I'm writing a Splunk 6 app, and need the ability to persist settings. I did a simple mock-up writing/reading JSON to a file, but now I want to use SQLite.

The problem comes when I go to import sqlite in python:

/myapp/django/myapp/views.py:

from django.contrib.auth.decorators import login_required
from splunkdj.decorators.render import render_to
from django.shortcuts import render

# Add various modules that we'll use in this app
import logging
import sqlite3
import sys
import json

The error that I get is: "global name 'sqlite3' is not defined"

Ok, so I try moving the import down into my def, now the exception is "No module named sqlite3"

Inside myapp, I did a "logger.debug(sys.path)" and can see it's pointing to /opt/splunk/lib/python2.7/

After some digging, I realized that there is no "/opt/splunk/lib/python2.7/lib-dynload/_sqlite3.so". I assume this is because Splunk uses a custom-compiled python and omits it. I tried copying a compatible _sqlite3.so but that didn't work.

So what's going on here? All I want to do is create a little table, insert a few rows, and query the settings back out. I know Splunk uses SQLite already -- I just have no idea how to access it.

I started playing around with settings.py:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': 'mydatabase',
    }
}

but could not get that working either. I'm a little new to Django, so at this moment I'm stuck. Can anyone give me a hand?

Thanks!

Re: Splunk6 Django app -- Importing SQLite

LukeMurphey — Fri, 01 Nov 2013 00:35:05 GMT

Splunk includes the Python SQLite libraries but not the C libraries. Thus, you won't be able to use the Python to connect to SQLite, sadly.

Re: Splunk6 Django app -- Importing SQLite

joshuamcqueen — Mon, 28 Sep 2020 15:10:09 GMT

I GOT IT WORKING!!

After about 8 hours of messing around here's what I did:

copy the _sqlite3.so from /usr/lib64/python2.6/lib-dynload/_sqlite3.so to /opt/splunk/lib/python2.7/lib-dynload/
chmod and chown it appropriately
restart Splunk

Volia!

Re: Splunk6 Django app -- Importing SQLite

LukeMurphey — Fri, 01 Nov 2013 01:58:37 GMT

Nice! Let us know once you have been using it for a while. I have heard that the sqlite libs could cause reliability problems but I have never seen direct evidence of this (just hearsay).

Re: Splunk6 Django app -- Importing SQLite

joshuamcqueen — Fri, 01 Nov 2013 14:44:01 GMT

So far it works great -- there are a few got'chas with python syntax, but otherwise working well. The app does basic CRUD with 200+ users. I'll post a full blog post with code sooner-than-later

Re: Splunk6 Django app -- Importing SQLite

mgroves_splunk — Fri, 01 Nov 2013 18:43:52 GMT

Glad to see you got it working, but I wanted to caution against this, for a couple of reasons:

settings.py may be overridden during upgrades, which may remove your database
definition.
The Python libraries directories may also be overridden, which will remove the
DB drivers.
As a general concern, you may have some trouble with a Search Head Pooling scenario, but that
may not be an issue for you.

Overall, I completely understand that this is a great feature to have, but I wanted to be honest that it may not be the best approach at the moment. It is something we are looking at enabling.

Re: Splunk6 Django app -- Importing SQLite

tysonstewart — Wed, 04 Dec 2013 16:00:37 GMT

@joshuamcqueen Are you certain that copying the sqlite3 SO was the only thing you needed to do? I tried it and had no luck whatsoever. I even downloaded the Python 2.7.5 source and compiled it to get the same SO as the Splunk version of Python would expect. No luck.

I'm a little frustrated that we get access to Django, but no access to databases. I guess I'll just write JSON files to the file system.

Re: Splunk6 Django app -- Importing SQLite

joshuamcqueen — Wed, 04 Dec 2013 16:31:34 GMT

@tysonstewart -- Let me go back and double check. I'm almost positive. HOWEVER, I tried to port this app over to a Windows search head and could not get it working.

I have been meaning to do a screencast / blog writeup of this project. If you can wait a week I'll give you a full write up. I'll post it to this thread.

Re: Splunk6 Django app -- Importing SQLite

tysonstewart — Thu, 05 Dec 2013 23:17:31 GMT

@joshuamcqueen I got it working. There was a conflict around character encoding, and I also had to reference the sqlite3 modules in my external Python directory because my Splunk installation is missing the dbapi2 module.

I thought I would mention that I used a module in my app to add to the sys.path so that it could find the sqlite modules, and alter the DATABASES setting so that the default Splunk application doesn't have to be modified. If you want to know more for your blog post, let me know. 🙂

Re: Splunk6 Django app -- Importing SQLite

musskopf — Mon, 23 Jun 2014 22:30:59 GMT

I had some similar experience when trying to use SQLITE3 inside a Python Lookup script.

I tried the copying method from the OS Python (RHEL 6.5) but it was returning some weird results from SQLITE, like partial string and things like that.

My solution was:

1. Install RedHat Package Sqlite3-Dev

2. Download the Python 2.7.7 Source

3. Compile it using "./configure --prefix=/home/splunk/local"

4. Finally copy the "sqlite3" folder and "lib-dynload/_sqlite3.so" from my freshly compiled Python to the Splunk/lib/python2.7 folder

My guess is that the sqlite3 python package from OS was built using python 2.6 and Splunk runs python 2.7 so some incompatibility there caused it.

I think it would work with other modules as well.

Re: Splunk6 Django app -- Importing SQLite

koshyk — Mon, 11 Aug 2014 11:09:47 GMT

@joshuamcqueen, Any chance for you to put as a blog? Also is it not possible to put sqlite files in an app?