Difference between revisions of "Addon:SQLite Export Import"

From Gramps
Jump to: navigation, search
(Using export with external programs)
(11 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
{{Third-party plugin}}
 
{{Third-party plugin}}
 +
{{man warn|This pair of addons are used together and do not provide generic SQL support for import from an unknown format.|See: {{bug|11447}}}}
 +
[[File:SQLite Export addon 51.png|thumb|right|450px]]
  
A Gramps [https://github.com/gramps-project/addons-source/blob/master/Sqlite/ SQLite exporter and SQLite importer] has been developed that can export most of the Gramps genealogical data.
+
A Gramps [https://github.com/gramps-project/addons-source/blob/master/Sqlite/ SQLite exporter and SQLite importer] that can export most of the Gramps genealogical data.
  
 
SQL stands for "[https://en.wikipedia.org/wiki/SQL 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
 
SQL stands for "[https://en.wikipedia.org/wiki/SQL 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
Line 20: Line 22:
  
 
==Import SQL==
 
==Import SQL==
You can also import via SQL import.
+
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.'''
 
* '''It is recommended that you create an new Family Tree before import.'''
Line 27: Line 29:
  
 
=Using export with external programs=
 
=Using export with external programs=
After you use the SQL Exporter to export your Gramps data into a file such as <code>''Untitled_1.sql''</code>, you can then use SQL queries like:
+
After you use the SQLite Exporter to export your Gramps data into a file such as <code>''Untitled_1.sql''</code>, you can then use SQL queries using any program that can access your database (postgresql, sqlite, etc). For example, on Linux with sqlite:
 +
 
  
 
<pre>
 
<pre>
Line 52: Line 55:
 
$
 
$
 
</pre>
 
</pre>
 +
 +
 +
 +
<pre>
 +
$ 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
 +
...
 +
</pre>
 +
 +
If you change the data, you can then read it back using the SQLite importer into Gramps to see the changes.
 +
 +
{{man note|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.}}
 +
  
 
== See also ==
 
== See also ==
 
* [[GEPS_010:_Relational_Backend|GEPS 010: SQL Backend]]
 
* [[GEPS_010:_Relational_Backend|GEPS 010: SQL Backend]]
 
* [[Gramps_SQL_Database#Older_version]]
 
* [[Gramps_SQL_Database#Older_version]]
 +
* {{bug|11447}} sqlite3.DatabaseError: file is not a database
 +
* {{bug|9148}} Sqlite export followed by re-import not idempotent
 +
  
 
[[Category:Developers/General]]
 
[[Category:Developers/General]]
 
[[Category:Plugins]]
 
[[Category:Plugins]]

Revision as of 04:20, 19 November 2019

Gramps-notes.png

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
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 Addon/Plugin system is controlled by the Plugin Manager.

Gnome-important.png
This pair of addons are used together and do not provide generic SQL support for import from an unknown format.

See: 11447

SQLite Export addon 51.png

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 & Import addons.

Usage

Export SQL

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 SQL

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.

Gramps-notes.png
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.


See also