116

I have a few SQLite database files. I want to know the database file version (if the database was created with sqlite2 or sqlite3 or any other main/sub version) not the SQLite library or driver or user_version or schema_version.

user4157124
  • 2,809
  • 13
  • 27
  • 42
Srikanth S
  • 1,717
  • 5
  • 16
  • 21

11 Answers11

176

You can write this command in any sqlite explorer which will give the sqlite version

select sqlite_version();
David Arenburg
  • 91,361
  • 17
  • 137
  • 196
saurabh
  • 2,463
  • 1
  • 18
  • 8
105

You can get version number of a database file by the Magic Header String:

  • sqlite2 ==> first 48 bytes
  • sqlite3 ==> first 16 bytes
$ head -c 48 file2.db
** This file contains an SQLite 2.1 database **

$ head -c 16 file3.db
SQLite format 3

The easier way is using the file command:

$ file file2.db
file2.db: SQLite 2.x database

$ file file3.db
file3.db: SQLite 3.x database
Andrey Semakin
  • 2,032
  • 1
  • 23
  • 50
kev
  • 155,172
  • 47
  • 273
  • 272
39

Get user_version

Run SQL: PRAGMA user_version;

Get schema_version:

Run SQL: PRAGMA schema_version;

When create database file (.db), user_version can be set by user.

Joshua Pinter
  • 45,245
  • 23
  • 243
  • 245
galian
  • 832
  • 6
  • 12
24

The correct answer from version 3 of sqlite program is:

sqlite3 --version
MSD561
  • 512
  • 5
  • 16
  • 8
    This is the version of the sqlite3 program, not the version of the database created by SQLite. –  Mar 25 '19 at 19:49
4

You can extract the information from the header file. It will require you to open the database file 'by hand' but I don't know if there is an API function to get this information.

Pablo Santa Cruz
  • 176,835
  • 32
  • 241
  • 292
2

You have to open the python shell then write these steps:

import sqlite3

sqlite3.sqlite_version
Techgeeks1
  • 556
  • 1
  • 4
  • 18
  • 2
    This gives you the sqlite3 version used by Python. – John Go-Soco Apr 29 '19 at 10:48
  • May not be correct for some users, but I should point out that in my case, using Django on Ubuntu 18.04, it actually was the only way that worked. Trying "sqlite3 --version" at the command shell, oddly, would claim that sqlite3 was not installed, whereas doing it inside python told me what version django/python was using, which is what I was looking for. – rossdavidh Jun 13 '19 at 19:17
1

Check manual file

sqlite3.version The version number of this module, as a string. This is not the version of the SQLite library.

sqlite3.version_info The version number of this module, as a tuple of integers. This is not the version of the SQLite library.

sqlite3.sqlite_version The version number of the run-time SQLite library, as a string.

sqlite3.sqlite_version_info The version number of the run-time SQLite library, as a tuple of integers.

341152 Chyi
  • 11
  • 3
0

I found this to be the easiest method to determine the version of sqlite. Run the Python IDLE Shell, then do the following:

>>> import sqlite3
>>> sqlite3.version
'2.6.0'

In my case it was 2.6.0. Hope this helps... Mark

David Arenburg
  • 91,361
  • 17
  • 137
  • 196
Mark
  • 81
  • 6
  • 2
    fyi, to run the Python IDLE Shell, simply type in `python` in the terminal, you'll then see `>>>` – thehme Dec 05 '15 at 04:54
  • 14
    This gives the version of the module, not the SQLite version. To do so, use `sqlite3.sqlite_version`. https://docs.python.org/3.5/library/sqlite3.html#module-functions-and-constants – Bertrand Bordage Jun 21 '16 at 11:40
0

In command prompt, if you type sqlite3 and hit enter, it will automatically give the version number at the very first.

sqlite3

enter image description here

Merin Nakarmi
  • 3,148
  • 3
  • 35
  • 42
0
import sqlite3

conn = sqlite3.connect('example.db')

cursor = conn.cursor()

cursor.execute('SELECT SQLITE_VERSION()')

data = cursor.fetchone()
print(f"SQLite version: {data[0]}")
Suraj Rao
  • 29,388
  • 11
  • 94
  • 103
md shihab uddin
  • 1
  • 1
  • Thank you for your interest in contributing to the Stack Overflow community. This question already has quite a few answers—including one that has been extensively validated by the community. Are you certain your approach hasn’t been given previously? **If so, it would be useful to explain how your approach is different, under what circumstances your approach might be preferred, and/or why you think the previous answers aren’t sufficient.** Can you kindly [edit] your answer to offer an explanation? – Jeremy Caney Aug 14 '23 at 00:58
-1

If you have a data connection to it in Visual Studio, you can right click on the data base in Server Explorer, select properties, and the version will be shown in the properties window, (under Version, surprisingly). You might need to left click the database first to open it.

George Williams
  • 147
  • 1
  • 10