Addon:SQLite Export Import

This is a Third-party Addon. The Addon/Plugin system is controlled by the Plugin Manager. Please use carefully on data that is backed up, and help make it better by reporting any issues to the bug tracker. Unless otherwise stated on this page, you can download this addon by following these instructions. Please note that some Addons have prerequisites that need to be installed before they can be used.

This pair of addons are used together and do not provide generic SQL support for import from an unknown format. See: 11447

A Gramps SQLite exporter and SQLite importer that can export most of the Gramps genealogical data.

SQL stands for "Structured Query Language" and is pronounced "sequel" (it is a joke: as it came after QUEL, it is its sequel). SQLite is a common local database format

Installation

Install both the SQLite Export and SQLite Import addons.

Usage

Export SQLite

You can export most of the Gramps genealogical data through an SQL Export using the Export Assistant. This does not export other Gramps data, such as bookmarks, researcher information, etc.

• From the menu select: Family Trees > Export...
• From the Saving your data wizard dialog select Next
• From the Choose the output format wizard dialog select the SQLite Export option and then select Next twice
• From the Select save file wizard dialog either name the file and default location or change it and then select Next
• From the Final confirmation wizard dialog select Apply and then Close

Import SQLite

You can also import the previously exported Gramps genealogical data via SQL import.

• It is recommended that you create an new Family Tree before import.
• From the menu select: Family Trees > Import...
• From the Import Family Tree file dialog selector, select your previously created SQL file (eg: Untitled_1.sql) and then select the Import button

What's new (Gramps 6.1 addon rewrite)

UPDATED The export/import code was rewritten to read and write Gramps' raw named-key data dictionaries instead of unpacking objects by fixed field position. This means new fields Gramps adds in future versions are far less likely to silently break or drop data on export -- unrecognized fields simply aren't copied, rather than crashing or shifting every field after them. As part of this, the FamilySearch sync fields Gramps 6.1 added to each person (familysearch_sync) are now preserved through export/import, along with translated type names, the reordering fix, and the constants/name_group tables described below.

Using export with external programs

After you use the SQLite Exporter to export your Gramps data into a file such as Untitled_1.sql, you can then use SQL queries using any program that can access your database (postgresql, sqlite, etc). For example, on Linux with sqlite:

$ sqlite3 Untitled_1.sql
SQLite version 3.5.9
Enter ".help" for instructions

sqlite> .tables
dates       family      names       people      repository
events      media       notes       places      sources

sqlite> .headers on
.headers on

sqlite> select * from people;
handle|gramps_id|gender|death_ref_index|birth_ref_index|change|marker0|marker1|private
b247d7186567ff472ef|I0000|1|-1|-1|1225135132|-1||0

sqlite> select * from names where surname like "%Smith%";
private|first_name|surname|suffix|title|name_type0|name_type1|prefix|patronymic|group_as|sort_as|display_as|call
0|Test|Smith|||2|||||0|0|

sqlite> .exit
$

$ sqlite3 Untitled_1.sql
SQLite version 3.6.12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
address         event           location        note            repository_ref
attribute       event_ref       markup          person          source
child_ref       family          media           person_ref      source_ref
datamap         lds             media_ref       place           url
date            link            name            repository
sqlite> .headers on
sqlite> .mode columns
sqlite> select * from person;
handle              gid             gender death_ref_handle    birth_ref_handle    change     marker0 marker1  private
------------------  --------------  ------ ------------------- ------------------- ---------- ------- -------- --------
b5dc6d9aa5766513709 I0010           0      b5dc6d9aa07279205ad b5dc6d9aa3e7b41b0f1 1249739601 1       Complete 1
b5dc6d9add708e5ba9e Amle achba      0      b5dc6d9adc539e3085e                     1249739601 2       ToDo     0
b5dc6d9cd9c134a0c39 I0017           1                                              1249739602 -1               0
b5dc6d9dfd3719d4e00 imgoabim Miulka 1      b5dc6d9dfca6a342e45                     1249739603 1       Complete 0
b5dc6d9f38779e2275b I0024           0                          b5dc6d9f37b685b9607 1249739603 -1               0
...

If you change the data, you can then read it back using the SQLite importer into Gramps to see the changes.

NOTE You should only attempt editing textual fields or fields for which you know the meaning. For example, in the above, do not change the handle fields, but you can change one's gender.

Type columns now show translated names, not just numeric codes

UPDATED (Gramps 6.1 addon rewrite) As of the 2026 rewrite of this addon, every *_type1 TEXT column (event type, name type, attribute type, URL type, child relationship, etc.) is populated with the type's translated display name (e.g. "Birth", not just an index), not only for custom types as in earlier versions. A new constants table also documents the meaning of other integer codes that aren't GrampsType values (date calendar/modifier/quality, person gender, citation confidence, LDS ordinance type/status, name display_as/sort_as, note format), and a new name_group table exports Gramps' surname-grouping data. This directly resolves the "Indexed data" issue described below for newly exported files.

Indexed data (older addon versions)

A user reported that their experiments with the SQLite export created records of Events where the Event type is a numeric index. There was no text label of the Event type. They were unable to find the table being referenced.

The events type are referenced in gramps/gen/lib/eventtype.py

The following code will get the string value corresponding to the index:

           event_type = [tup for tup in EventType._DATAMAP
                         if tup[0] == index][0]
           evt_id = event_type[0]
           evt_name = event_type[1]

Reordering (children, marriages, and other lists) now survives round-trip

UPDATED (Gramps 6.1 addon rewrite) Gramps has many places where you can manually reorder a list -- e.g. dragging children into birth order in the Family Editor, or using Edit -> Reorder families to change the order spouses/marriages are shown for a person. Earlier versions of this addon stored every such list (children, family/spouse lists, event references, names, surnames, addresses, media references, associations, attributes, URLs, repository references, notes, tags) in one generic linking table with no explicit ordering column, so round-trip order depended on incidental database behavior rather than being guaranteed. An explicit sequence column was added so any order you set in the Gramps GUI is now guaranteed to survive an export/import round trip.

See also

• GEPS 010: SQL Backend withdrawn in favor of GEPS 032
• GEPS 032: Database Backend API
• Gramps_SQL_Database#Older_version

Gramps bug report MantisBT database:

• 11447 : Sqlite3.DatabaseError: file is not a database
• 9148 : Sqlite export followed by re-import not idempotent -- improved by the Gramps 6.1 addon rewrite (translated type names, constants/name_group tables, and guaranteed list-order round-trip)
• 12620 : Include type descriptions in SQLite Export addon -- fixed by the Gramps 6.1 addon rewrite
• 12765 : Include name_group table in SQLite Export -- fixed by the Gramps 6.1 addon rewrite
• 12766 : Add table of constants to SQLite Export -- fixed by the Gramps 6.1 addon rewrite

Gramps community support Discourse forum:

• SQLite export clarification? - Dec 2022
• Extracting constants from python code - Dec 2022

• Addon:JSON Export Import