8

I am trying to update my SQLite version to 3.24 or above so that a Python app can make use of the new "UPSERT" queries. I have been trying for a few hours to do this with little success; it refuses to update past 3.22.

I have attempted:

  • Using apt to install and reinstall sqlite / libsqlite3-dev (and various versions of this)

  • Downloading packages from launchpad (such as https://launchpad.net/ubuntu/+source/sqlite3/3.26.0-2) and attempting to install them

  • Using Python pip to try and update sqlite3

  • Adding a few PPA repos to try and grab it from there

  • Other various suggestions found from google

What I have not tried:

  • Building SQLite from source (this is a bit of a last resort for me)

Is it possible to install a version of SQLite 3.24+ on Ubuntu 18.04? If so, is the only way to build from source or is there an easy way to pick up a more recent version through apt (or similar)?

  • This question is quite insufficient. Please first take the tour, and read How to Ask to learn how to improve it. Then add details about what you tried and why it failed. – Murphy Sep 16 '19 at 19:49
  • 1
    Thanks for the links, I have improved the question. It is quite a basic one at its core however. – pkiller162 Sep 16 '19 at 20:04
  • 1
    You didn't provide the details what exactly failed your each method you tried, sou you probably won't get useful answers. Regarding the first two: Version 3.22 is the only one available from the official Bionic (18.04) repositories. The Launchpad link you provided points to the build for Disco (19.04), which is incompatible with Bionic due to dependencies (libc6 >= 2.28). – Murphy Sep 18 '19 at 13:21
  • This may be a useful starting point for you: stackoverflow.com/a/55656349/5794048 – Murphy Sep 19 '19 at 12:22
4
+50

Here are some options:

Docker

This is the actual use case for docker. There are no official Docker images with the latest sqlite - but it's easy to make one:

$ mkdir sqlite333 && cd sqlite333
$ cat > Dockerfile
FROM ubuntu:groovy
RUN apt-get update && \
    DEBIAN_FRONTEND=noninteractive apt-get -yq --no-install-recommends install sqlite3 python3 && \
    rm -rf /var/lib/apt/lists/* /tmp/* /var/tmp/* 

WORKDIR /root
ENTRYPOINT python3.8 -c "import sqlite3; print(sqlite3.sqlite_version)"

Press CtrlD to save, then:

$ docker build -t sqlite333 .
$ docker run --rm -ti sqlite333
3.33.0

To run your existing app, just bind mount it:

docker run --rm -ti -v /path/to/myapp:/tmp sqlite333 /tmp/myapp.py

Or you can copy it into the directory with your Dockerfile, then copies of it will be placed in the actual image - but then you need to rebuild the image whenever you make changes.

If there was an official Sqlite image with the right version, and if you've installed and could run Docker, you can use any version of SQLite and any version of anything else, neatly packaged eg. (from https://hub.docker.com/r/nouchka/sqlite3)

docker run --rm -ti -v `pwd`:/tmp nouchka/sqlite3 /tmp/myapp.py

This mounts your current directory inside /tmp in the docker image, and runs the image built from this Dockerfile - which you can trivially modify to also install Python, and to run any command you like.

Front door (community) approach

You can forcibly install .deb's from other distributions - or versions - that use the same C library - I usually search on Distrowatch to find these - but besides the standard C library version, there could be other unexpected dependency problems, so your "quick fix" could turn out to be not-so-quick in the long term, if not downright breaking parts of your OS.

The proper way to do this is to volunteer your time to update the official repo - this way all security and compatibility issues will have the best way to find their way to the right channel, and you stand the best chance of soliciting the help of the community in maintaining things going forward. The starting point would be to contact the current Ubuntu SQLite package maintainer to find out what you can do to help to get it up-to-date. https://launchpad.net/ubuntu/+source/sqlite3 (3.33 is in fact part of the latest Ubuntu, which is why the above Dockerfile piggy-backs on) This initial list showing more than 20 related or dependent packages, alludes to the reason why older versions might be lagging: https://packages.ubuntu.com/search?keywords=sqlite3

Compile from source

Compiling from source is not that much trouble either - if you've got the steps - so lets put SQLite's claim to fame of "small, fast, reliable" to the test, from https://www.sqlite.org/download.html:

$ wget https://www.sqlite.org/2020/sqlite-autoconf-3330000.tar.gz
$ tar -zxvf sqlite-autoconf-3330000.tar.gz
$  cd sqlite-autoconf-3330000/
$ ./configure
$ make -j4

But this is all answering your question as asked. Your real question is:

Can I install SQlite 3.33 for Python under Ubuntu 18.04?

Edit: Python does indeed use the system SQLite - so after compiling it as per above, installing it under /usr/local, you should theoretically be able to tell it to use a specific one with the LD_RUN_PATH environment variable as per below - but it doesn't work:

$ sudo PREFIX=/usr/local make install

Would normally make this use the new SQLite:

$ LD_RUN_PATH=/usr/local/lib python3 

But it seems that there is more to SQLite or Python3, as just this isn't enough - it might work if you install the new version system wide (sudo PREFIX=/usr make install) - but this is not recommended as it which might break compatibility with other system applications and break system dependencies and your OS might overwrite it if it updates - you can reference http://charlesleifer.com/blog/compiling-sqlite-for-use-with-python-applications/ for more detailed instructions, but there should be no need as I've copied the essentials below.

Considering this, the docker method seems to be not only a scalable and maintainable quick-fix but actually the preferred way - as upon closer inspection, it becomes clear that SQLite is actually more tightly integrated with the main Python code base, leaving the only alternative as compiling your own Python package as per below.

The other solution: compiling Python

So what you really want to do is to compile your own copy of Python.

I was surprised how quickly this compiled, even on my underpowered laptop. The guide linked above is quite detailed, so I won't duplicate anything but the main steps here - simplified:

# Follow the steps copied above to compile SQLite 3.33 - and then after the make, do:
$ sudo PREFIX=/usr/local make install

# Then compile Python
$ sudo apt-get build-dep python3.6 # thanks Ubuntu community!
$ git clone --depth=1 https://github.com/python/cpython
$ cd cpython
$ LD_RUN_PATH=/usr/local/lib ./configure CPPFLAGS="-I/usr/local/include/sqlite3" #updated
$ make -s -j4
# sudo PREFIX=/usr/local make install # if you like, which will install it in /usr/local/bin
# but you can just run it directly from the source directory too

This should leave you with a binary version of Python that has SQlite 3.33 support (or whichever version you installed under /usr/local), which you can copy over, or package and copy over, or package into a deb and copy over, or package into your own PPA for distribution and apt installing. Or if someone already did that, you can search for it on Ubuntu launchpad.

Edit: Tested and working. After testing the above without the LD_RUN_PATH environment variable, and noticing that it still used the distribution version (3.22 in this case), it became clear that it's not compiled in - just the path to the library it is supposed to use is compiled in, as it does when compiling it like this. So recompiling Python might not be necessary, just installing a new SQLite system-wide - over the OS packaged version - considering that the packaged version might overwrite it if it ever updates, or that removing the packaged version might break your OS packaging for other packages that depend on it so that's not a great solution.)

(The easy way to learn about all of this is by just installing Slackware or Gentoo, which is how I learned. )

Other options

There might be Flatpak or Snappy options, but nobody has packaged it yet as of this writing.

| improve this answer | |
  • no PPA in "other options"? – jangorecki Oct 6 at 7:12
  • I know it's a long post... It's mentioned in the paragraph after the compilation instructions – Dagelf Oct 21 at 5:21
  • 1
    own PPA is not any useful PPA for me :) PPA is for making installation easy – jangorecki Oct 21 at 7:44
  • Making a PPA is easy. Uploading it to launchpad is the tedious part :-D – Dagelf Oct 26 at 17:33
2

This version of SQLite3 is tied to the official Ubuntu 18.04 as Murphy mentioned. I created a fresh Ubuntu 18.04.5 LTS in Azure and tried several things including installing Python3.7 and Python 3.8 and the SQLite version was the same.

root@test-011:/home/carles# python3.8 -c "import sqlite3; print(sqlite3.sqlite_version)"
3.22.0

I also tried downloading precompiled SQLite binaries for a more recent version, and neither they worked.

I would say, even if you make it work, it would be a pain distributing your program to other Servers. And I would not recommend you to not use the official packages provided by LTS version, as you would not get upgrades and security patches for those.

So I think that the easiest way is either you install a newer version of Ubuntu, or you can run a Docker instance with newer Ubuntu image version and try there the new feature with your program. Building SQLite3 from the sources will not work for you, you would have to build Python3 from the Sources + SQLite3. My advice is: Keep it simple.

| improve this answer | |
1

Upgrade to 20.04 and you get SQLite 3.31 for free*

I'm sure you probably could upgrade SQLite3 (and python3-sqlite) in-place by recompiling them. I would not expect there to be a simple (eg PPA) for this. However, the pain and fuss would likely outweigh the time cost of a system upgrade, or moving a stack to Docker (which is a good solution).

You also get a Python upgrade too... Which is nice if you want 3.8 features.

If you absolutely can't —and I respect that, I've had production systems that need to be left alone— I think you realistically need to start looking at separating the app's Python environment out away from the system's Python so that you can upgrade it with a version you compile yourself. Or let Docker do that for you. This will mean infrastructural changes to your app's Python environment, so definitely test on a backup first.

Do not try to replace the system's Python binaries, even with a similar version bound to a newer version of SQLite3. It will end in pain and misery and likely around 9 films about your perilous fall to the dark side and the ramifications for generations to come. Seriously though, leave /usr/bin/python{,3} alone.

| improve this answer | |
  • I was actually expecting PPA. sqlite is such a commonly used software. I do use PPA for almost all software I need to install without modifying its source and it works perfect. – jangorecki Oct 6 at 7:11
  • @jangorecki My experience with Python PPAs is that they usually fit into the ecosystem they're made for, not providing other versions of dependencies. This is absolutely something you could engineer for yourself but you'd also be responsible for future security updates for stdlib. – Oli Oct 7 at 8:54
  • I am saying about ubuntu/debian PPA, not python – jangorecki Oct 8 at 7:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.