Addon:SQLite Export Import
This is a Third-party Addon. Please use carefully on data that is backed up, and help make it better by reporting any comments or problems to the author, or issues to the bug tracker |
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
Contents
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
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. |
Indexed data
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]
See also
GEPS 010: SQL Backendwithdrawn 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
- 12620 : Include type descriptions in SQLite Export addon
- 12765 : Include name_group table in SQLite Export
- 12766 : Add table of constants to SQLite Export
Gramps community support Discourse forum:
- SQLite export clarification? - Dec 2022
- Extracting constants from python code - Dec 2022