SQLite
Published 7 months ago: June 25, 2020
SQLite tip snippets
Table of contents
This cheatsheet is a small collection of handy snippets for quick reference in tricky situations. Feel free to suggest edits, propose additions, complain about something, disagree with content or such and the like. Every feedback is a welcome one.
Copy specific columns from one table and insert into a different table
INSERT INTO destination_table (field1, field2)
SELECT field1,field2
FROM 'source_table';Copy content in one column to another column within the same table
update tableName
set destinationField = sourceField;Warning! Replaces all content in destination fields
Insert UUIDs in empty cells
import sqlite3
import uuid
db = 'database.db'
sql_find_id = ''' SELECT idcolumn,rowid
FROM tablename
WHERE idcolumn IS NULL
LIMIT 1
'''
sql_insert_uuid = ''' UPDATE tablename SET idcolumn=? WHERE rowid=?
'''
sql_find_id_after = ''' SELECT idcolumn,rowid
FROM tablename
WHERE idcolumn=?
'''
conn = sqlite3.connect(db)
c = conn.cursor()
c.execute(sql_find_id)
nullornot = c.fetchone()[0]
while nullornot == None:
try:
c.execute(sql_find_id)
nullornot = c.fetchone()[0]
except:
break
c.execute(sql_find_id)
rowid = c.fetchone()[1]
genuuid = str(uuid.uuid4())
c.execute(sql_insert_uuid, (genuuid,rowid,))
c.execute(sql_find_id_after, (genuuid,))
try:
c.execute(sql_find_id)
nullornot = c.fetchone()[0]
except:
print("All done")
conn.commit()
conn.close()