SQLite

Published 7 months ago: June 25, 2020


SQLite tip snippets

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()

Christoffer Lybekk

Developer under development

Explore more articles with similar tags

SQLiteDatabaseCheatsheet

Article stats


36words

5sentences

5paragraphs

1 minread time