SQLite examples
Nov 25, 2022 2:41:43 GMT -8
Post by Uncle Buddy on Nov 25, 2022 2:41:43 GMT -8
<drive>:\treebard\etc\open_and_use_sqlite.txt Last Changed 2024-04-16
HOW TO OPEN AND USE SQLITE
For installation problems as in moving to a new computer, see "environment variables old computer dec 31 2020 windows 7"
COMMAND LINE INTERFACE BASIC OVERVIEW HOW TO USE SQLITE: file:///C:/00000PROGRAMMING/SQLITE/SQLITE%20FAQ%20INTRO%20QUICKSTART/cli.html FOR
open DOS command shell by typing cmd in start menu search box and hitting enter
will open to c:/users/username
or find sqlite3.exe (in C:\00000PROGRAMMING\SQLITE\sqlite official unzipped) and double click it
EXAMPLE 0a: check sqlite version
print(sqlite3.sqlite_version)
EXAMPLE 0b: TURN ON/OFF FOREIGN KEYS
FKs not supported till Sqlite version 2?.6.19
FKs are off by default, do this per each connection:
conn = sqlite3.connect('c:/treebard/test_fk.db')
conn.execute('PRAGMA foreign_keys = 1')
# conn.execute('PRAGMA foreign_keys = 0')
cur = conn.cursor()
# In python if this returns no data then your version has no foreign key support:
print(cur.execute('PRAGMA foreign_keys'))
# In shell this returns 0 or 1 for on or off:
PRAGMA foreign_keys;
EXAMPLE 1: open and close a database:
C:\Users\LUTHER>sqlite3 c:/sqlite_code/treebard00012.db
...
sqlite> .exit
EXAMPLE 2: find out what tables and views exist
C:\Users\LUTHER>sqlite3 c:/sqlite_code/treebard00012.db
...
sqlite> .tables
Citation PlacenameChainVWConcat3col
CurrentEntity PlacenameChainVWConcat4col
CurrentEntityVWPlace PlacenameChainVWConcat5col
CurrentEntityVWPlaceBigger PlacenameChainVWConcat6col
CurrentEntityVWPlaceDelete PlacenameChainVWConcat7col
GenderMadeRelationType PlacenameChainVWConcat8col
Name PlacenameChainVWConcat9col
NameConcat RelationType
Person Repository
Place Source
PlacenameChainVW Title
PlacenameChainVW2col citation_SUPERCEDED_20180705
PlacenameChainVW3col concatname_SUPERCEDED_20180705
PlacenameChainVW4col currentity_superceded_20180705
PlacenameChainVW5col name_SUPERCEDED_20180705
PlacenameChainVW6col person_SUPERCEDED_20180705
PlacenameChainVW7col place_superceded_20180705
PlacenameChainVW8col repository_SUPERCEDED_20180705
PlacenameChainVW9col source_SUPERCEDED_20180705
PlacenameChainVWConcat2col title_SUPERCEDED_20180705
EXAMPLE 3: find out what all is in a table
sqlite> .mode column
sqlite> .header on
sqlite> select * from Place;
PlaceID Placename PlaceFKPlaceID PlaceNote
---------- ---------- -------------- ----------
1 Earth
2 reserved
3 Antarctica 1
4 Arctic Oce 1
5 Asia 1
6 Atlantic O 1
7 Australia 1
8 Central Am 1
9 Europe 1
10 Indian Oce 1
11 Middle Eas 1
12 North Amer 1
13 Pacific Oc 1
14 South Amer 1
15 Philippine 5
16 Iceland 4
17 Ireland 9
18 Russia 5
19 Costa Rica 8
20 USA 12
21 Canary Isl 25
22 Mindanao 15
23 Davao del 22
25 Spain 9
26 Little Pan
27 Purok 1A 26
28 Colorado 20
29 Greenland 4
30 Garfield C 28
31 Glenwood S 30
32 334 Park D 31
33 Swimming P 31
34 City Park 31
35 At Sea 10
36 At Sea 4
37 in the bac 48
38 Highway 41 30
39 1313 Mangg 27
40 upstairs 39
41 under the 40
42 in a box 41
43 in a book 42
44 on a bookm 43
45 in a parag 44
46 in a sente 45
47 in a word 46
48 Greyhound 38
49 Hawaii 20
50 Gilligan's 13
51 Africa 1
52 Maduao
EXAMPLE 4: find out what's in a table row
sqlite> select * from Place where PlaceID = 10;
PlaceID Placename PlaceFKPlaceID PlaceNote
---------- ------------ -------------- ----------
10 Indian Ocean 1
EXAMPLE 5: set column width of results table (0 is a default and other integers are character counts)
sqlite> .width 0 15 0 0
sqlite> select * from Place where PlaceID = 12;
PlaceID Placename PlaceFKPlaceID PlaceNote
---------- --------------- -------------- ----------
12 North America 1
EXAMPLE 6: how to find out how a table was created
sqlite> .schema Place
CREATE TABLE Place (PlaceID INTEGER PRIMARY KEY, Placename TEXT, PlaceFKPlaceID
INTEGER REFERENCES Place(PlaceID), PlaceNote TEXT);
EXAMPLE 7: find out what's in selected columns
sqlite> select Placename, PlaceFKPlaceID from Place where PlaceID = 15;
Placename PlaceFKPlaceID
----------- ---------------
Philippines 5
EXAMPLE 8: create a table
sqlite> CREATE TABLE Source (SrcTypID INTEGER PRIMARY KEY, SourceType TEXT, Date DATE, SourceNote TEXT);
EXAMPLE 9: find out what columns are in a table
sqlite> pragma table_info(Place);
cid name type notnull dflt_value pk
---------- --------------- ---------- ---------- ---------- ----------
0 PlaceID INTEGER 0 1
1 Placename TEXT 0 0
2 PlaceFKPlaceID INTEGER 0 0
3 PlaceNote TEXT 0 0
EXAMPLE 10: insert a new row into a table (use NULL where a PK will be placed by sqlite or there's an error)
sqlite> INSERT INTO SourceType VALUES (NULL, 'Family Tree');
sqlite> select * from SourceType;
1|Death Certificate
2|Marriage License
3|Census
4|Burial Record
5|Military Record
6|Family Bible
7|Birth Certificate
8|Headstone
9|Family Tree
EXAMPLE 11: copy and recreate a table (instead of dropping it and retyping everything)
BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(a,b);
INSERT INTO t1_backup SELECT a,b FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a,b);
INSERT INTO t1 SELECT a,b FROM t1_backup;
DROP TABLE t1_backup;
COMMIT;
EXAMPLE 12a: select query inner join on two columns (I couldn't get INNER JOIN syntax to work but this worked):
SELECT person_display_name, gender, birthdate
FROM person, current AS ma, current AS pa
WHERE mother_fk_person = ma.current_fk_person
OR father_fk_person = pa.current_fk_person
ORDER by birthdate
EXAMPLE 12b: (this time the INNER JOIN syntax worked)
SELECT landmark_types,
landmark_date,
landmark_fk_place,
particulars,
age,
landmark_note
FROM landmark
INNER JOIN current
INNER JOIN landmark_type
ON landmark.landmark_fk_person = current.current_fk_person
AND landmark_type.landmark_type_id = landmark.landmark_fk_type
ORDER BY landmark_date
EXAMPLE 13: add column to existing table
sqlite> ALTER TABLE person ADD COLUMN living BOOLEAN;
EXAMPLE 14: create a foreign key column 2 ways--direct and deferred
CREATE TABLE stuff (
stuff_id INTEGER PRIMARY KEY AUTOINCREMENT,
xyz INTEGER,
abc INTEGER,
mno INTEGER REFERENCES other_stuff (other_stuff_id) ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY(abc) REFERENCES more_stuff(more_stuff_id) ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (xyz) REFERENCES right_stuff(right_stuff_id) ON DELETE RESTRICT ON UPDATE CASCADE);
EXAMPLE 15: insert multiple rows at once
list_o_tups = []
for item in new_list:
item = (item,)
list_o_tups.append(item)
conn = sqlite3.connect('c:/blahblahblah.db')
cur = conn.cursor()
cur.executemany('''
INSERT INTO recent_place (recent_place_id, recent_places)
VALUES (NULL, ?)''',
list_o_tups)
conn.commit()
cur.close()
conn.close()
EXAMPLE 16: rename table and add primary key constraint
# NOTE: Starting with sqlite 3.25.0 the old way no longer works. The old way was to rename the table, create a new one with the old table's schema, and insert select * from the old table to the new, then drop the old table. Now, the references to the old table will be kept and nothing will work. So you have to do it the new way, which is to make the new table table_new using the old table's schema, insert select * from old to new (adding default/missing values as appropriate), drop the old, and rename the new table to the old table's name. From the internet: stackoverflow.com/questions/57253045/sqlite-error-no-such-table-main-table-name-exists
copy the schema gotten this way:
.schema table_name
BEGIN TRANSACTION;
CREATE TABLE table_name_new (...paste the old schema)
INSERT INTO table_name_new SELECT * FROM table_name;
DROP table_name;
ALTER TABLE table_name_new RENAME TO table_name;
# old way; don't do this anymore
ALTER TABLE cities RENAME TO old_cities;
CREATE TABLE cities (
id INTEGER NOT NULL PRIMARY KEY,
name TEXT NOT NULL);
INSERT INTO cities
SELECT * FROM old_cities;
DROP TABLE old_cities;
COMMIT;
EXAMPLE 17: automatically insert default value in case of NULL
CREATE TABLE book (
book_id INTEGER PRIMARY KEY,
book_name TEXT NOT NULL,
price INTEGER DEFAULT 100);
"The DEFAULT clause specifies a default value to use for the column if no value is explicitly provided by the user when doing an INSERT. If there is no explicit DEFAULT clause attached to a column definition, then the default value of the column is NULL. An explicit DEFAULT clause may specify that the default value is NULL, a string constant, a blob constant, a signed-number, or any constant expression enclosed in parentheses. A default value may also be one of the special case-independent keywords CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. For the purposes of the DEFAULT clause, an expression is considered constant if it contains no sub-queries, column or table references, bound parameters, or string literals enclosed in double-quotes instead of single-quotes." (https://www.sqlite.org/lang_createtable.html)
EXAMPLE 18: UNIQUE constraint
The following statement creates a new table named contacts with a UNIQUE constraint defined for the email column:
CREATE TABLE contacts(
contact_id INTEGER PRIMARY KEY,
first_name TEXT,
last_name TEXT,
email TEXT NOT NULL UNIQUE);
The following example inserts a new row into the contacts table:
INSERT INTO contacts(first_name,last_name,email)
VALUES ('John','Doe','john.doe@gmail.com');
EXAMPLE 19: give column of results table an alias heading
sqlite> select claim_types as mumboJumbo from claim_type;
mumboJumbo
----------
birth
marriage
offspring
death
burial
occupation
imprisonme
deportatio
invention
EXAMPLE 20: LEFT JOINS need the column with the most stuff in it
(or most relevant stuff in it) on the
left of the first LEFT JOIN keyword while the columns with possible nulls
are to the right of that. The order of what's on left of right of ON's
equal sign doesn't matter.
EXAMPLE 21: Get id of last autoincrement id created...
... for all tables:
SELECT * from SQLITE_SEQUENCE;
name|seq
current|1
name|65
name_part|8
source|5
role|15
claims_notes|5
claims_roles|6
claims_findings|5
findings_notes|10
findings_roles|18
citation|6
images_entities|60
image|44
date_per_tree|1
date_test|3
note|9
event_type|18
claim|8
kin_type|14
findings_persons|17
name_type|17
place|45
finding|30
person|20
... for a particular table:
SELECT seq FROM SQLITE_SEQUENCE WHERE name = 'person';
seq
20
(this table only exists for AUTOINCREMENT and the purpose of AUTOINCREMENT is to guarantee the uniqueness of an ID through the life of the database, which means deleted PKs will not be re-used. For the better way--since AUTOINCREMENT uses more resources and is slower--see last_insert_rowid (example 39)
EXAMPLE 22: copy_table_from_old_database_to_new_sqlite
C:\Users\Lutherman>sqlite3 c:/treebard_gps/data/settings/global_config.db
SQLite version 3.34.0 2020-12-01 16:14:00
Enter ".help" for usage hints.
sqlite> .tables
closing_state date_global folder place
color_scheme date_per_user format
sqlite> .output table_dump.sql
sqlite> .dump color_scheme
sqlite> .dump format
sqlite> .dump folder
sqlite> .quit
C:\Users\Lutherman>sqlite3 c:/treebard_gps/data/sample_tree/sample_tree.tbd
SQLite version 3.34.0 2020-12-01 16:14:00
Enter ".help" for usage hints.
sqlite> .tables
citation date_global findings_persons note
claim date_per_tree findings_roles person
claims_findings date_per_user image place
claims_notes date_test images_entities role_type
claims_roles event_type kin_type source
closing_state finding name
current findings_notes name_type
sqlite> .read table_dump.sql
sqlite> .tables
citation date_global findings_roles note
claim date_per_tree folder person
claims_findings date_per_user format place
claims_notes date_test image role_type
claims_roles event_type images_entities source
closing_state finding kin_type
color_scheme findings_notes name
current findings_persons name_type
EXAMPLE 23: update multiple values in one row simultaneously
date = user_values[0][1]
place_id = user_values[1][1]
particulars = user_values[2][1]
age = user_values[3][1]
cur.execute(
'''
UPDATE finding
SET date = ?,
particulars = ?,
age = ?,
person_id = ?
WHERE finding_id = ?
''',
(date, particulars, age, current_person, self.finding_id))
conn.commit()
EXAMPLE 24: sort results table by a column
SELECT event_type_id, event_types, couple
FROM event_type
WHERE hidden != 1
ORDER BY event_types
EXAMPLE 25: query against values in a list when the list is constant
SELECT * from some_table where some_id in (2,3)
EXAMPLE 26: query against values in a list when the list is a variable
First Example names the string:
args = [2, 3]
sql = "SELECT * from some_table where some_id in ({seq})".format(
seq = ','.join(['?'] * len(args)))
cursor.execute(sql, args)
Second Example just uses the string:
cur.execute("SELECT notes FROM finding WHERE finding_id IN ({})".format(
','.join('?' * couple_event_ids[1])),
couple_event_ids[0])
EXAMPLE 27: query against list values as well as extra values
qlen = len(marital_event_types)
marital_event_types.insert(0, self.current_person)
marital_event_types.insert(0, self.current_person)
sql = '''
SELECT findings_persons_id, findings_persons.finding_id, person_id1, kin_type_id1, person_id2, kin_type_id2
FROM findings_persons
JOIN finding
ON finding.finding_id = findings_persons.finding_id
WHERE (person_id1 = ? OR person_id2 = ?)
AND event_type_id in ({})
'''.format(",".join(["?"] * qlen))
cur.execute(sql, marital_event_types)
EXAMPLE 28: get unique distinct results, no duplicates
SELECT DISTINCT finding_id
FROM findings_roles
WHERE finding_id in (23, 19, 42, 45, 2)
EXAMPLE 29: count result rows with non-null value in a column
SELECT COUNT(findings_roles_id)
FROM findings_roles
WHERE finding_id = ?
EXAMPLE 30: insert selected columns from one table to another:
There's no parentheses after SELECT
INSERT INTO place_new (place_id, places, latitude, longitude, cartesian_coordinates, township, range, section, legal_subdivision) SELECT place_id, places, latitude, longitude, cartesian_coordinates, township, range, section, legal_subdivision FROM place;
EXAMPLE 31: select greatest id from a table:
SELECT MAX(place_id) FROM place;
EXAMPLE 32: select non-unique values (E.G. finding_id):
(2 examples)
SELECT finding_places_id, finding_id
FROM finding_places
WHERE finding_id IN
(SELECT finding_id
FROM finding_places
GROUP BY finding_id HAVING COUNT(*) > 1);
SELECT * FROM (SELECT place_id, place_name_id, place_names FROM place_name WHERE place_names IN (SELECT place_names FROM place_name GROUP BY place_names HAVING COUNT(*) > 1)) ORDER BY place_names;
EXAMPLE 33: add database text version to git version control
SEE ALSO EXAMPLE 46 FOR A SIMPLER EXAMPLE
(the text file created by dump is pure SQL ie all the CREATE TABLE and INSERT etc needed to reconstruct the table from scratch. Don't try to track binary files such as .db with git, I forget why because I repressed that traumatic event)
stackoverflow.com/questions/17830882/whats-the-correct-way-to-deal-with-databases-in-git
While git (just like most other version control systems) supports tracking binary files like databases, it only does it best for text files. In other words, you should never use version control system to track constantly changing binary database files (unless they are created once and almost never change).
One popular method to still track databases in git is to track text database dumps. For example, SQLite database could be dumped into *.sql file using sqlite3 utility (subcommand .dump). However, even when using dumps, it is only appropriate to track template databases which do not change very often, and create binary database from such dumps using scripts as part of standard deployment.
www.sqlitetutorial.net/sqlite-dump/
(above link also explains how to dump specific tables)
FIRST OPEN THE DATABASE:
C:\sqlite>sqlite3 c:/path_to_database/db.db
SQLite version 3.13.0 2016-05-18 10:57:30
Enter ".help" for usage hints.
sqlite>
TO DUMP TO SCREEN:
sqlite> .output
sqlite> .dump
.exit
TO DUMP TO FILE:
sqlite> .output c:/path_to_dump/db.sql
sqlite> .dump
.exit
TO DUMP SCHEMA ONLY TO FILE:
sqlite> .output c:/path_to_database/db_structure.sql
sqlite> .schema
.exit
stackoverflow.com/questions/311691/importing-a-sqlite3-dump-back-into-the-database
TO UNDUMP FROM TEXT TO BINARY DO THIS FROM TERMINAL (not inside sqlite tool):
sqlite3 my_database.sqlite < dumpfile.sql
www.sqlite.org/cli.html
Converting An Entire Database To A Text File
Use the ".dump" command to convert the entire contents of a database into a single UTF-8 text file. This file can be converted back into a database by piping it back into sqlite3.
A good way to make an archival copy of a database is this:
$ sqlite3 ex1 .dump | gzip -c >ex1.dump.gz
This generates a file named ex1.dump.gz that contains everything you need to reconstruct the database at a later time, or on another machine. To reconstruct the database, just type:
$ zcat ex1.dump.gz | sqlite3 ex2
The text format is pure SQL so you can also use the .dump command to export an SQLite database into other popular SQL database engines. Like this:
$ createdb ex2
$ sqlite3 ex1 .dump | psql ex2
EXAMPLE 34: select values from table 2 which are not in table 1:
ex. 1:
sqlite> select person_id from person where person_id not in (select person_id from images_elements where person_id is not null) and person_id is not null;
PERSON_ID
7
8
448
449
450
451
452
454
455
457
556
557
5581
5582
5583
5584
5622
5716
5717
5718
5720
5721
ex. 2:
sqlite> select person_id from finding where event_type_id = 1 and person_id not in (select person_id from person where person_id in (select person_id from finding where person_id is not null and event_type_id = 1) and person_id is not null);
person_id
81
EXAMPLE 36: Order Results Table By Multiple Columns
This example orders by columns from two different tables.
SELECT person_id, names, name_types, name.name_id, sort_order, used_by
FROM name
JOIN name_type
ON name.name_type_id = name_type.name_type_id
ORDER BY person_id, hierarchy
EXAMPLE 37: SQLite tries to use old values instead of current ones
See show_top_pic() in main.py. Has something to do with manually changing a value in db?
EXAMPLE 38: Attaching a second database and using foreign keys across the two DBs in a JOIN:
# FOR EXAMPLES OF other types of queries SEE join_tables_from_different_databases_sqlite
conn = sqlite3.connect('d:/treebard_gps/data/settings/treebard.db')
conn.execute('PRAGMA foreign_keys = 1')
cur = conn.cursor()
att = 'ATTACH DATABASE ? AS sample'
sample = 'd:/treebard_gps/data/sample_tree/sample_tree.tbd'
cur.execute(att, (sample,))
cur.execute(
'''
SELECT main.event_type.event_types
FROM sample.finding
JOIN main.event_type
ON main.event_type.event_type_id = sample.finding.event_type_id
WHERE sample.finding.finding_id < 10
''')
results = cur.fetchall()
print(results)
# [('birth',), ('religious conversion',), ('occupation',), ('death',), ('marriage',), ('guardianship',), ('birth',), ('birth',), ('wedding',)]
det = "DETACH DATABASE sample"
cur.execute(det)
cur.close()
conn.close()
EXAMPLE 39: last_insert_rowid FOR GETTING LAST INSERTED ID without AUTOINCREMENT
(SEE example 21 for the way that only works with autoincrement but ?doesn't always work?)
(This ex. 39 below still works if there's autoincrement, but this is default and autoincrement is more expensive and slower. Autoincrement is also limiting since it will not re-use deleted primary keys.)
IN THE CONSOLE:
sqlite> .schema test
CREATE TABLE test (id INTEGER PRIMARY KEY, stuff TEXT);
sqlite> INSERT INTO test VALUES (4, 'dog');
sqlite> SELECT last_insert_rowid();
last_insert_rowid()
-------------------
4
sqlite> SELECT seq FROM SQLITE_SEQUENCE WHERE name = 'test';
sqlite> SELECT last_insert_rowid();
last_insert_rowid()
-------------------
4
IN PYTHON:
cur.execute("CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, stuff TEXT)")
conn.commit()
cur.execute("INSERT INTO test VALUES (9, 'test stuff')")
conn.commit()
print(cur.lastrowid)
# output: 9
EXAMPLE 40: clear the screen in the sqlite console with `.shell cls`
(`.shell args`... allows you to execute CMD commands in the SQLite3 shell)
EXAMPLE 41: get a list of columns in a table
import sqlite3
conn = sqlite3.connect("d:/00programming/code_examples_python/sqlite_select_table_names.db")
cur = conn.cursor()
cur.execute("create table if not exists table1 (column1 text, column2 integer)")
conn.commit()
cur.execute("create table if not exists table2 (column3 text, column4 integer)")
conn.commit()
cur.execute("PRAGMA table_info(table1)")
table1 = cur.fetchall()
print(table1)
cur.execute("PRAGMA table_info(table2)")
table2 = cur.fetchall()
print(table2)
cur.close()
conn.close()
tables = [i[1] for i in table2]
print(tables)
OUTPUT:
# cid name type notnull dflt_value pk (HEADERS WOULD BE SHOWN IF DONE IN CONSOLE)
[(0, 'column1', 'text', 0, None, 0), (1, 'column2', 'integer', 0, None, 0)]
[(0, 'column3', 'text', 0, None, 0), (1, 'column4', 'integer', 0, None, 0)]
['column3', 'column4']
EXAMPLE 42: Order by ascending is default so ASC can be omitted; to order by descenDing:
SELECT Name, Price FROM Cars ORDER BY Price DESC;
EXAMPLE 42: Add a UNIQUE constraint to multiple columns
CREATE TABLE IF NOT EXISTS "links_links_new" (links_links_id INTEGER PRIMARY KEY, person_id INTEGER, place_id INTEGER, nested_place_id INTEGER, place_name_id INTEGER, name_id INTEGER, source_id INTEGER, citation_id INTEGER, finding_id INTEGER, assertion_id INTEGER, project_id INTEGER, to_do_id INTEGER, contact_id INTEGER, image_id INTEGER, note_id INTEGER, repository_id INTEGER, report_id INTEGER, chart_id INTEGER, media_id INTEGER, role_type_id INTEGER, note_topic_order INTEGER, main_image BOOLEAN, locator_id INTEGER REFERENCES locator (locator_id), note_topic TEXT DEFAULT null, FOREIGN KEY (person_id) REFERENCES person (person_id), FOREIGN KEY (place_id) REFERENCES place (place_id), FOREIGN KEY (nested_place_id) REFERENCES nested_place (nested_place_id), FOREIGN KEY (place_name_id) REFERENCES place_name (place_name_id), FOREIGN KEY (name_id) REFERENCES name (name_id), FOREIGN KEY (source_id) REFERENCES source (source_id), FOREIGN KEY (citation_id) REFERENCES citation (citation_id), FOREIGN KEY (finding_id) REFERENCES finding (finding_id), FOREIGN KEY (assertion_id) REFERENCES assertion (assertion_id), FOREIGN KEY (project_id) REFERENCES project (project_id), FOREIGN KEY (to_do_id) REFERENCES to_do (to_do_id), FOREIGN KEY (contact_id) REFERENCES contact (contact_id), FOREIGN KEY (image_id) REFERENCES image (image_id), FOREIGN KEY (note_id) REFERENCES note (note_id), FOREIGN KEY (repository_id) REFERENCES repository (repository_id), FOREIGN KEY (report_id) REFERENCES report (report_id), FOREIGN KEY (chart_id) REFERENCES chart (chart_id), FOREIGN KEY (media_id) REFERENCES media (media_id), UNIQUE (note_id, note_topic, person_id), UNIQUE (note_id, note_topic, place_id), UNIQUE (note_id, note_topic, nested_place_id), UNIQUE (note_id, note_topic, place_name_id), UNIQUE (note_id, note_topic, name_id), UNIQUE (note_id, note_topic, source_id), UNIQUE (note_id, note_topic, citation_id), UNIQUE (note_id, note_topic, finding_id), UNIQUE (note_id, note_topic, assertion_id), UNIQUE (note_id, note_topic, project_id), UNIQUE (note_id, note_topic, to_do_id), UNIQUE (note_id, note_topic, contact_id), UNIQUE (note_id, note_topic, image_id), UNIQUE (note_id, note_topic, repository_id), UNIQUE (note_id, note_topic, report_id), UNIQUE (note_id, note_topic, chart_id), UNIQUE (note_id, note_topic, media_id), UNIQUE (note_id, note_topic, locator_id));
EXAMPLE 43: get case insensitive select results:
a) a build it into the table column
CREATE TABLE stuff (topics TEXT COLLATE NOCASE);
b) or use it in the query:
SELECT names FROM things WHERE names = "mIsS THinG" COLLATE NOCASE;
EXAMPLE 44: insert a range of blank records with pk only
data = ((i,) for i in range(1, 203))
cur.executemany("INSERT INTO family (family_id) VALUES (?)", data)
conn.commit()
EXAMPLE 45: Join tables in an update query
UPDATE software
SET purchprice = c.purchprice
FROM (SELECT purchprice FROM softwarecost) AS c
WHERE c.id = software.id;
EXAMPLE 46: Save a new master default blank db schema (all tables) after making changes to one or more table schemas REMEMBER TO DELETE THE OLD default.db and previously exported .sql file first, the second part should be making a .db that doesn't exist. Keep the most recent .sql in data/settings and always do it this way so the most recent .sql is always a valid version of the db schema. Do this every time a change is made and a current export will always be at hand.
THIS IS ALSO how you add a database to a code repo since the .sql is a text file containing all the table schemas, so it doesn't confuse git by being a .db file the way the actual database binary file would do. THIS REPLACES EXAMPLE 33 for the most part which is about the same technique but this explains it simpler.
C:\Users\Lutherman>sqlite3 d:/treebard_gps/data/sample_tree/sample_tree.tbd
SQLite version 3.34.0 2020-12-01 16:14:00
[... make some change in the table schema...]
sqlite> .output d:/treebard_gps/data/settings/default_new_tree.sql
sqlite> .schema
sqlite> .exit
C:\Users\Lutherman>sqlite3 d:/treebard_gps/data/settings/default_new_tree.db < d:/treebard_gps/data/settings/default_new_tree.sql
Error: near line 1: object name reserved for internal use: sqlite_sequence
(I DON'T KNOW what the error is but the import of the schema seemed to work fine.)
HOW TO OPEN AND USE SQLITE
For installation problems as in moving to a new computer, see "environment variables old computer dec 31 2020 windows 7"
COMMAND LINE INTERFACE BASIC OVERVIEW HOW TO USE SQLITE: file:///C:/00000PROGRAMMING/SQLITE/SQLITE%20FAQ%20INTRO%20QUICKSTART/cli.html FOR
open DOS command shell by typing cmd in start menu search box and hitting enter
will open to c:/users/username
or find sqlite3.exe (in C:\00000PROGRAMMING\SQLITE\sqlite official unzipped) and double click it
EXAMPLE 0a: check sqlite version
print(sqlite3.sqlite_version)
EXAMPLE 0b: TURN ON/OFF FOREIGN KEYS
FKs not supported till Sqlite version 2?.6.19
FKs are off by default, do this per each connection:
conn = sqlite3.connect('c:/treebard/test_fk.db')
conn.execute('PRAGMA foreign_keys = 1')
# conn.execute('PRAGMA foreign_keys = 0')
cur = conn.cursor()
# In python if this returns no data then your version has no foreign key support:
print(cur.execute('PRAGMA foreign_keys'))
# In shell this returns 0 or 1 for on or off:
PRAGMA foreign_keys;
EXAMPLE 1: open and close a database:
C:\Users\LUTHER>sqlite3 c:/sqlite_code/treebard00012.db
...
sqlite> .exit
EXAMPLE 2: find out what tables and views exist
C:\Users\LUTHER>sqlite3 c:/sqlite_code/treebard00012.db
...
sqlite> .tables
Citation PlacenameChainVWConcat3col
CurrentEntity PlacenameChainVWConcat4col
CurrentEntityVWPlace PlacenameChainVWConcat5col
CurrentEntityVWPlaceBigger PlacenameChainVWConcat6col
CurrentEntityVWPlaceDelete PlacenameChainVWConcat7col
GenderMadeRelationType PlacenameChainVWConcat8col
Name PlacenameChainVWConcat9col
NameConcat RelationType
Person Repository
Place Source
PlacenameChainVW Title
PlacenameChainVW2col citation_SUPERCEDED_20180705
PlacenameChainVW3col concatname_SUPERCEDED_20180705
PlacenameChainVW4col currentity_superceded_20180705
PlacenameChainVW5col name_SUPERCEDED_20180705
PlacenameChainVW6col person_SUPERCEDED_20180705
PlacenameChainVW7col place_superceded_20180705
PlacenameChainVW8col repository_SUPERCEDED_20180705
PlacenameChainVW9col source_SUPERCEDED_20180705
PlacenameChainVWConcat2col title_SUPERCEDED_20180705
EXAMPLE 3: find out what all is in a table
sqlite> .mode column
sqlite> .header on
sqlite> select * from Place;
PlaceID Placename PlaceFKPlaceID PlaceNote
---------- ---------- -------------- ----------
1 Earth
2 reserved
3 Antarctica 1
4 Arctic Oce 1
5 Asia 1
6 Atlantic O 1
7 Australia 1
8 Central Am 1
9 Europe 1
10 Indian Oce 1
11 Middle Eas 1
12 North Amer 1
13 Pacific Oc 1
14 South Amer 1
15 Philippine 5
16 Iceland 4
17 Ireland 9
18 Russia 5
19 Costa Rica 8
20 USA 12
21 Canary Isl 25
22 Mindanao 15
23 Davao del 22
25 Spain 9
26 Little Pan
27 Purok 1A 26
28 Colorado 20
29 Greenland 4
30 Garfield C 28
31 Glenwood S 30
32 334 Park D 31
33 Swimming P 31
34 City Park 31
35 At Sea 10
36 At Sea 4
37 in the bac 48
38 Highway 41 30
39 1313 Mangg 27
40 upstairs 39
41 under the 40
42 in a box 41
43 in a book 42
44 on a bookm 43
45 in a parag 44
46 in a sente 45
47 in a word 46
48 Greyhound 38
49 Hawaii 20
50 Gilligan's 13
51 Africa 1
52 Maduao
EXAMPLE 4: find out what's in a table row
sqlite> select * from Place where PlaceID = 10;
PlaceID Placename PlaceFKPlaceID PlaceNote
---------- ------------ -------------- ----------
10 Indian Ocean 1
EXAMPLE 5: set column width of results table (0 is a default and other integers are character counts)
sqlite> .width 0 15 0 0
sqlite> select * from Place where PlaceID = 12;
PlaceID Placename PlaceFKPlaceID PlaceNote
---------- --------------- -------------- ----------
12 North America 1
EXAMPLE 6: how to find out how a table was created
sqlite> .schema Place
CREATE TABLE Place (PlaceID INTEGER PRIMARY KEY, Placename TEXT, PlaceFKPlaceID
INTEGER REFERENCES Place(PlaceID), PlaceNote TEXT);
EXAMPLE 7: find out what's in selected columns
sqlite> select Placename, PlaceFKPlaceID from Place where PlaceID = 15;
Placename PlaceFKPlaceID
----------- ---------------
Philippines 5
EXAMPLE 8: create a table
sqlite> CREATE TABLE Source (SrcTypID INTEGER PRIMARY KEY, SourceType TEXT, Date DATE, SourceNote TEXT);
EXAMPLE 9: find out what columns are in a table
sqlite> pragma table_info(Place);
cid name type notnull dflt_value pk
---------- --------------- ---------- ---------- ---------- ----------
0 PlaceID INTEGER 0 1
1 Placename TEXT 0 0
2 PlaceFKPlaceID INTEGER 0 0
3 PlaceNote TEXT 0 0
EXAMPLE 10: insert a new row into a table (use NULL where a PK will be placed by sqlite or there's an error)
sqlite> INSERT INTO SourceType VALUES (NULL, 'Family Tree');
sqlite> select * from SourceType;
1|Death Certificate
2|Marriage License
3|Census
4|Burial Record
5|Military Record
6|Family Bible
7|Birth Certificate
8|Headstone
9|Family Tree
EXAMPLE 11: copy and recreate a table (instead of dropping it and retyping everything)
BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(a,b);
INSERT INTO t1_backup SELECT a,b FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a,b);
INSERT INTO t1 SELECT a,b FROM t1_backup;
DROP TABLE t1_backup;
COMMIT;
EXAMPLE 12a: select query inner join on two columns (I couldn't get INNER JOIN syntax to work but this worked):
SELECT person_display_name, gender, birthdate
FROM person, current AS ma, current AS pa
WHERE mother_fk_person = ma.current_fk_person
OR father_fk_person = pa.current_fk_person
ORDER by birthdate
EXAMPLE 12b: (this time the INNER JOIN syntax worked)
SELECT landmark_types,
landmark_date,
landmark_fk_place,
particulars,
age,
landmark_note
FROM landmark
INNER JOIN current
INNER JOIN landmark_type
ON landmark.landmark_fk_person = current.current_fk_person
AND landmark_type.landmark_type_id = landmark.landmark_fk_type
ORDER BY landmark_date
EXAMPLE 13: add column to existing table
sqlite> ALTER TABLE person ADD COLUMN living BOOLEAN;
EXAMPLE 14: create a foreign key column 2 ways--direct and deferred
CREATE TABLE stuff (
stuff_id INTEGER PRIMARY KEY AUTOINCREMENT,
xyz INTEGER,
abc INTEGER,
mno INTEGER REFERENCES other_stuff (other_stuff_id) ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY(abc) REFERENCES more_stuff(more_stuff_id) ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (xyz) REFERENCES right_stuff(right_stuff_id) ON DELETE RESTRICT ON UPDATE CASCADE);
EXAMPLE 15: insert multiple rows at once
list_o_tups = []
for item in new_list:
item = (item,)
list_o_tups.append(item)
conn = sqlite3.connect('c:/blahblahblah.db')
cur = conn.cursor()
cur.executemany('''
INSERT INTO recent_place (recent_place_id, recent_places)
VALUES (NULL, ?)''',
list_o_tups)
conn.commit()
cur.close()
conn.close()
EXAMPLE 16: rename table and add primary key constraint
# NOTE: Starting with sqlite 3.25.0 the old way no longer works. The old way was to rename the table, create a new one with the old table's schema, and insert select * from the old table to the new, then drop the old table. Now, the references to the old table will be kept and nothing will work. So you have to do it the new way, which is to make the new table table_new using the old table's schema, insert select * from old to new (adding default/missing values as appropriate), drop the old, and rename the new table to the old table's name. From the internet: stackoverflow.com/questions/57253045/sqlite-error-no-such-table-main-table-name-exists
copy the schema gotten this way:
.schema table_name
BEGIN TRANSACTION;
CREATE TABLE table_name_new (...paste the old schema)
INSERT INTO table_name_new SELECT * FROM table_name;
DROP table_name;
ALTER TABLE table_name_new RENAME TO table_name;
# old way; don't do this anymore
ALTER TABLE cities RENAME TO old_cities;
CREATE TABLE cities (
id INTEGER NOT NULL PRIMARY KEY,
name TEXT NOT NULL);
INSERT INTO cities
SELECT * FROM old_cities;
DROP TABLE old_cities;
COMMIT;
EXAMPLE 17: automatically insert default value in case of NULL
CREATE TABLE book (
book_id INTEGER PRIMARY KEY,
book_name TEXT NOT NULL,
price INTEGER DEFAULT 100);
"The DEFAULT clause specifies a default value to use for the column if no value is explicitly provided by the user when doing an INSERT. If there is no explicit DEFAULT clause attached to a column definition, then the default value of the column is NULL. An explicit DEFAULT clause may specify that the default value is NULL, a string constant, a blob constant, a signed-number, or any constant expression enclosed in parentheses. A default value may also be one of the special case-independent keywords CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. For the purposes of the DEFAULT clause, an expression is considered constant if it contains no sub-queries, column or table references, bound parameters, or string literals enclosed in double-quotes instead of single-quotes." (https://www.sqlite.org/lang_createtable.html)
EXAMPLE 18: UNIQUE constraint
The following statement creates a new table named contacts with a UNIQUE constraint defined for the email column:
CREATE TABLE contacts(
contact_id INTEGER PRIMARY KEY,
first_name TEXT,
last_name TEXT,
email TEXT NOT NULL UNIQUE);
The following example inserts a new row into the contacts table:
INSERT INTO contacts(first_name,last_name,email)
VALUES ('John','Doe','john.doe@gmail.com');
EXAMPLE 19: give column of results table an alias heading
sqlite> select claim_types as mumboJumbo from claim_type;
mumboJumbo
----------
birth
marriage
offspring
death
burial
occupation
imprisonme
deportatio
invention
EXAMPLE 20: LEFT JOINS need the column with the most stuff in it
(or most relevant stuff in it) on the
left of the first LEFT JOIN keyword while the columns with possible nulls
are to the right of that. The order of what's on left of right of ON's
equal sign doesn't matter.
EXAMPLE 21: Get id of last autoincrement id created...
... for all tables:
SELECT * from SQLITE_SEQUENCE;
name|seq
current|1
name|65
name_part|8
source|5
role|15
claims_notes|5
claims_roles|6
claims_findings|5
findings_notes|10
findings_roles|18
citation|6
images_entities|60
image|44
date_per_tree|1
date_test|3
note|9
event_type|18
claim|8
kin_type|14
findings_persons|17
name_type|17
place|45
finding|30
person|20
... for a particular table:
SELECT seq FROM SQLITE_SEQUENCE WHERE name = 'person';
seq
20
(this table only exists for AUTOINCREMENT and the purpose of AUTOINCREMENT is to guarantee the uniqueness of an ID through the life of the database, which means deleted PKs will not be re-used. For the better way--since AUTOINCREMENT uses more resources and is slower--see last_insert_rowid (example 39)
EXAMPLE 22: copy_table_from_old_database_to_new_sqlite
C:\Users\Lutherman>sqlite3 c:/treebard_gps/data/settings/global_config.db
SQLite version 3.34.0 2020-12-01 16:14:00
Enter ".help" for usage hints.
sqlite> .tables
closing_state date_global folder place
color_scheme date_per_user format
sqlite> .output table_dump.sql
sqlite> .dump color_scheme
sqlite> .dump format
sqlite> .dump folder
sqlite> .quit
C:\Users\Lutherman>sqlite3 c:/treebard_gps/data/sample_tree/sample_tree.tbd
SQLite version 3.34.0 2020-12-01 16:14:00
Enter ".help" for usage hints.
sqlite> .tables
citation date_global findings_persons note
claim date_per_tree findings_roles person
claims_findings date_per_user image place
claims_notes date_test images_entities role_type
claims_roles event_type kin_type source
closing_state finding name
current findings_notes name_type
sqlite> .read table_dump.sql
sqlite> .tables
citation date_global findings_roles note
claim date_per_tree folder person
claims_findings date_per_user format place
claims_notes date_test image role_type
claims_roles event_type images_entities source
closing_state finding kin_type
color_scheme findings_notes name
current findings_persons name_type
EXAMPLE 23: update multiple values in one row simultaneously
date = user_values[0][1]
place_id = user_values[1][1]
particulars = user_values[2][1]
age = user_values[3][1]
cur.execute(
'''
UPDATE finding
SET date = ?,
particulars = ?,
age = ?,
person_id = ?
WHERE finding_id = ?
''',
(date, particulars, age, current_person, self.finding_id))
conn.commit()
EXAMPLE 24: sort results table by a column
SELECT event_type_id, event_types, couple
FROM event_type
WHERE hidden != 1
ORDER BY event_types
EXAMPLE 25: query against values in a list when the list is constant
SELECT * from some_table where some_id in (2,3)
EXAMPLE 26: query against values in a list when the list is a variable
First Example names the string:
args = [2, 3]
sql = "SELECT * from some_table where some_id in ({seq})".format(
seq = ','.join(['?'] * len(args)))
cursor.execute(sql, args)
Second Example just uses the string:
cur.execute("SELECT notes FROM finding WHERE finding_id IN ({})".format(
','.join('?' * couple_event_ids[1])),
couple_event_ids[0])
EXAMPLE 27: query against list values as well as extra values
qlen = len(marital_event_types)
marital_event_types.insert(0, self.current_person)
marital_event_types.insert(0, self.current_person)
sql = '''
SELECT findings_persons_id, findings_persons.finding_id, person_id1, kin_type_id1, person_id2, kin_type_id2
FROM findings_persons
JOIN finding
ON finding.finding_id = findings_persons.finding_id
WHERE (person_id1 = ? OR person_id2 = ?)
AND event_type_id in ({})
'''.format(",".join(["?"] * qlen))
cur.execute(sql, marital_event_types)
EXAMPLE 28: get unique distinct results, no duplicates
SELECT DISTINCT finding_id
FROM findings_roles
WHERE finding_id in (23, 19, 42, 45, 2)
EXAMPLE 29: count result rows with non-null value in a column
SELECT COUNT(findings_roles_id)
FROM findings_roles
WHERE finding_id = ?
EXAMPLE 30: insert selected columns from one table to another:
There's no parentheses after SELECT
INSERT INTO place_new (place_id, places, latitude, longitude, cartesian_coordinates, township, range, section, legal_subdivision) SELECT place_id, places, latitude, longitude, cartesian_coordinates, township, range, section, legal_subdivision FROM place;
EXAMPLE 31: select greatest id from a table:
SELECT MAX(place_id) FROM place;
EXAMPLE 32: select non-unique values (E.G. finding_id):
(2 examples)
SELECT finding_places_id, finding_id
FROM finding_places
WHERE finding_id IN
(SELECT finding_id
FROM finding_places
GROUP BY finding_id HAVING COUNT(*) > 1);
SELECT * FROM (SELECT place_id, place_name_id, place_names FROM place_name WHERE place_names IN (SELECT place_names FROM place_name GROUP BY place_names HAVING COUNT(*) > 1)) ORDER BY place_names;
EXAMPLE 33: add database text version to git version control
SEE ALSO EXAMPLE 46 FOR A SIMPLER EXAMPLE
(the text file created by dump is pure SQL ie all the CREATE TABLE and INSERT etc needed to reconstruct the table from scratch. Don't try to track binary files such as .db with git, I forget why because I repressed that traumatic event)
stackoverflow.com/questions/17830882/whats-the-correct-way-to-deal-with-databases-in-git
While git (just like most other version control systems) supports tracking binary files like databases, it only does it best for text files. In other words, you should never use version control system to track constantly changing binary database files (unless they are created once and almost never change).
One popular method to still track databases in git is to track text database dumps. For example, SQLite database could be dumped into *.sql file using sqlite3 utility (subcommand .dump). However, even when using dumps, it is only appropriate to track template databases which do not change very often, and create binary database from such dumps using scripts as part of standard deployment.
www.sqlitetutorial.net/sqlite-dump/
(above link also explains how to dump specific tables)
FIRST OPEN THE DATABASE:
C:\sqlite>sqlite3 c:/path_to_database/db.db
SQLite version 3.13.0 2016-05-18 10:57:30
Enter ".help" for usage hints.
sqlite>
TO DUMP TO SCREEN:
sqlite> .output
sqlite> .dump
.exit
TO DUMP TO FILE:
sqlite> .output c:/path_to_dump/db.sql
sqlite> .dump
.exit
TO DUMP SCHEMA ONLY TO FILE:
sqlite> .output c:/path_to_database/db_structure.sql
sqlite> .schema
.exit
stackoverflow.com/questions/311691/importing-a-sqlite3-dump-back-into-the-database
TO UNDUMP FROM TEXT TO BINARY DO THIS FROM TERMINAL (not inside sqlite tool):
sqlite3 my_database.sqlite < dumpfile.sql
www.sqlite.org/cli.html
Converting An Entire Database To A Text File
Use the ".dump" command to convert the entire contents of a database into a single UTF-8 text file. This file can be converted back into a database by piping it back into sqlite3.
A good way to make an archival copy of a database is this:
$ sqlite3 ex1 .dump | gzip -c >ex1.dump.gz
This generates a file named ex1.dump.gz that contains everything you need to reconstruct the database at a later time, or on another machine. To reconstruct the database, just type:
$ zcat ex1.dump.gz | sqlite3 ex2
The text format is pure SQL so you can also use the .dump command to export an SQLite database into other popular SQL database engines. Like this:
$ createdb ex2
$ sqlite3 ex1 .dump | psql ex2
EXAMPLE 34: select values from table 2 which are not in table 1:
ex. 1:
sqlite> select person_id from person where person_id not in (select person_id from images_elements where person_id is not null) and person_id is not null;
PERSON_ID
7
8
448
449
450
451
452
454
455
457
556
557
5581
5582
5583
5584
5622
5716
5717
5718
5720
5721
ex. 2:
sqlite> select person_id from finding where event_type_id = 1 and person_id not in (select person_id from person where person_id in (select person_id from finding where person_id is not null and event_type_id = 1) and person_id is not null);
person_id
81
EXAMPLE 36: Order Results Table By Multiple Columns
This example orders by columns from two different tables.
SELECT person_id, names, name_types, name.name_id, sort_order, used_by
FROM name
JOIN name_type
ON name.name_type_id = name_type.name_type_id
ORDER BY person_id, hierarchy
EXAMPLE 37: SQLite tries to use old values instead of current ones
See show_top_pic() in main.py. Has something to do with manually changing a value in db?
EXAMPLE 38: Attaching a second database and using foreign keys across the two DBs in a JOIN:
# FOR EXAMPLES OF other types of queries SEE join_tables_from_different_databases_sqlite
conn = sqlite3.connect('d:/treebard_gps/data/settings/treebard.db')
conn.execute('PRAGMA foreign_keys = 1')
cur = conn.cursor()
att = 'ATTACH DATABASE ? AS sample'
sample = 'd:/treebard_gps/data/sample_tree/sample_tree.tbd'
cur.execute(att, (sample,))
cur.execute(
'''
SELECT main.event_type.event_types
FROM sample.finding
JOIN main.event_type
ON main.event_type.event_type_id = sample.finding.event_type_id
WHERE sample.finding.finding_id < 10
''')
results = cur.fetchall()
print(results)
# [('birth',), ('religious conversion',), ('occupation',), ('death',), ('marriage',), ('guardianship',), ('birth',), ('birth',), ('wedding',)]
det = "DETACH DATABASE sample"
cur.execute(det)
cur.close()
conn.close()
EXAMPLE 39: last_insert_rowid FOR GETTING LAST INSERTED ID without AUTOINCREMENT
(SEE example 21 for the way that only works with autoincrement but ?doesn't always work?)
(This ex. 39 below still works if there's autoincrement, but this is default and autoincrement is more expensive and slower. Autoincrement is also limiting since it will not re-use deleted primary keys.)
IN THE CONSOLE:
sqlite> .schema test
CREATE TABLE test (id INTEGER PRIMARY KEY, stuff TEXT);
sqlite> INSERT INTO test VALUES (4, 'dog');
sqlite> SELECT last_insert_rowid();
last_insert_rowid()
-------------------
4
sqlite> SELECT seq FROM SQLITE_SEQUENCE WHERE name = 'test';
sqlite> SELECT last_insert_rowid();
last_insert_rowid()
-------------------
4
IN PYTHON:
cur.execute("CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, stuff TEXT)")
conn.commit()
cur.execute("INSERT INTO test VALUES (9, 'test stuff')")
conn.commit()
print(cur.lastrowid)
# output: 9
EXAMPLE 40: clear the screen in the sqlite console with `.shell cls`
(`.shell args`... allows you to execute CMD commands in the SQLite3 shell)
EXAMPLE 41: get a list of columns in a table
import sqlite3
conn = sqlite3.connect("d:/00programming/code_examples_python/sqlite_select_table_names.db")
cur = conn.cursor()
cur.execute("create table if not exists table1 (column1 text, column2 integer)")
conn.commit()
cur.execute("create table if not exists table2 (column3 text, column4 integer)")
conn.commit()
cur.execute("PRAGMA table_info(table1)")
table1 = cur.fetchall()
print(table1)
cur.execute("PRAGMA table_info(table2)")
table2 = cur.fetchall()
print(table2)
cur.close()
conn.close()
tables = [i[1] for i in table2]
print(tables)
OUTPUT:
# cid name type notnull dflt_value pk (HEADERS WOULD BE SHOWN IF DONE IN CONSOLE)
[(0, 'column1', 'text', 0, None, 0), (1, 'column2', 'integer', 0, None, 0)]
[(0, 'column3', 'text', 0, None, 0), (1, 'column4', 'integer', 0, None, 0)]
['column3', 'column4']
EXAMPLE 42: Order by ascending is default so ASC can be omitted; to order by descenDing:
SELECT Name, Price FROM Cars ORDER BY Price DESC;
EXAMPLE 42: Add a UNIQUE constraint to multiple columns
CREATE TABLE IF NOT EXISTS "links_links_new" (links_links_id INTEGER PRIMARY KEY, person_id INTEGER, place_id INTEGER, nested_place_id INTEGER, place_name_id INTEGER, name_id INTEGER, source_id INTEGER, citation_id INTEGER, finding_id INTEGER, assertion_id INTEGER, project_id INTEGER, to_do_id INTEGER, contact_id INTEGER, image_id INTEGER, note_id INTEGER, repository_id INTEGER, report_id INTEGER, chart_id INTEGER, media_id INTEGER, role_type_id INTEGER, note_topic_order INTEGER, main_image BOOLEAN, locator_id INTEGER REFERENCES locator (locator_id), note_topic TEXT DEFAULT null, FOREIGN KEY (person_id) REFERENCES person (person_id), FOREIGN KEY (place_id) REFERENCES place (place_id), FOREIGN KEY (nested_place_id) REFERENCES nested_place (nested_place_id), FOREIGN KEY (place_name_id) REFERENCES place_name (place_name_id), FOREIGN KEY (name_id) REFERENCES name (name_id), FOREIGN KEY (source_id) REFERENCES source (source_id), FOREIGN KEY (citation_id) REFERENCES citation (citation_id), FOREIGN KEY (finding_id) REFERENCES finding (finding_id), FOREIGN KEY (assertion_id) REFERENCES assertion (assertion_id), FOREIGN KEY (project_id) REFERENCES project (project_id), FOREIGN KEY (to_do_id) REFERENCES to_do (to_do_id), FOREIGN KEY (contact_id) REFERENCES contact (contact_id), FOREIGN KEY (image_id) REFERENCES image (image_id), FOREIGN KEY (note_id) REFERENCES note (note_id), FOREIGN KEY (repository_id) REFERENCES repository (repository_id), FOREIGN KEY (report_id) REFERENCES report (report_id), FOREIGN KEY (chart_id) REFERENCES chart (chart_id), FOREIGN KEY (media_id) REFERENCES media (media_id), UNIQUE (note_id, note_topic, person_id), UNIQUE (note_id, note_topic, place_id), UNIQUE (note_id, note_topic, nested_place_id), UNIQUE (note_id, note_topic, place_name_id), UNIQUE (note_id, note_topic, name_id), UNIQUE (note_id, note_topic, source_id), UNIQUE (note_id, note_topic, citation_id), UNIQUE (note_id, note_topic, finding_id), UNIQUE (note_id, note_topic, assertion_id), UNIQUE (note_id, note_topic, project_id), UNIQUE (note_id, note_topic, to_do_id), UNIQUE (note_id, note_topic, contact_id), UNIQUE (note_id, note_topic, image_id), UNIQUE (note_id, note_topic, repository_id), UNIQUE (note_id, note_topic, report_id), UNIQUE (note_id, note_topic, chart_id), UNIQUE (note_id, note_topic, media_id), UNIQUE (note_id, note_topic, locator_id));
EXAMPLE 43: get case insensitive select results:
a) a build it into the table column
CREATE TABLE stuff (topics TEXT COLLATE NOCASE);
b) or use it in the query:
SELECT names FROM things WHERE names = "mIsS THinG" COLLATE NOCASE;
EXAMPLE 44: insert a range of blank records with pk only
data = ((i,) for i in range(1, 203))
cur.executemany("INSERT INTO family (family_id) VALUES (?)", data)
conn.commit()
EXAMPLE 45: Join tables in an update query
UPDATE software
SET purchprice = c.purchprice
FROM (SELECT purchprice FROM softwarecost) AS c
WHERE c.id = software.id;
EXAMPLE 46: Save a new master default blank db schema (all tables) after making changes to one or more table schemas REMEMBER TO DELETE THE OLD default.db and previously exported .sql file first, the second part should be making a .db that doesn't exist. Keep the most recent .sql in data/settings and always do it this way so the most recent .sql is always a valid version of the db schema. Do this every time a change is made and a current export will always be at hand.
THIS IS ALSO how you add a database to a code repo since the .sql is a text file containing all the table schemas, so it doesn't confuse git by being a .db file the way the actual database binary file would do. THIS REPLACES EXAMPLE 33 for the most part which is about the same technique but this explains it simpler.
C:\Users\Lutherman>sqlite3 d:/treebard_gps/data/sample_tree/sample_tree.tbd
SQLite version 3.34.0 2020-12-01 16:14:00
[... make some change
sqlite> .output d:/treebard_gps/data/settings/default_new_tree.sql
sqlite> .schema
sqlite> .exit
C:\Users\Lutherman>sqlite3 d:/treebard_gps/data/settings/default_new_tree.db < d:/treebard_gps/data/settings/default_new_tree.sql
Error: near line 1: object name reserved for internal use: sqlite_sequence
(I DON'T KNOW what the error is but the import of the schema seemed to work fine.)