Skip to content

Instantly share code, notes, and snippets.

@rixx
Last active August 29, 2015 14:19
Show Gist options
  • Save rixx/4a33e37e907112da997f to your computer and use it in GitHub Desktop.
Save rixx/4a33e37e907112da997f to your computer and use it in GitHub Desktop.
portalimport
#!/usr/bin/env python2
#-*- coding: utf8 -*-
import sqlite3
IMPORT = True
NEW_SCHEMA = "CREATE TABLE user(serial INTEGER PRIMARY KEY, name TEXT, surname TEXT, nickname TEXT, created timestamp DEFAULT CURRENT_TIMESTAMP, firstValid timestamp DEFAULT NULL, lastValid timestampt DEFAULT NULL, pubkey varchar(8192));"
NEW_DB = 'shackspacekey.sqlite'
OLD_SCHEMA = "CREATE TABLE user (Givenname varchar(64), Surname varchar(64),serialnumber varchar(64),Created timestamp DEFAULT CURRENT_TIMESTAMP,FirstValidDate timestamp,LastValidDate timestamp,PrivateKey varchar(3300),PublicKey varchar(730),Fingerprint varchar(64));"
OLD_DB = ''
def get_db(name):
conn = sqlite3.connect(name)
cur = conn.cursor()
return cur, conn
def data_import():
oldcur, oldconn = get_db(OLD_DB)
newcur, newconn = get_db(NEW_DB)
try:
newcur.execute(NEW_SCHEMA)
except sqlite3.OperationalError:
if raw_input("table exists, continue? [y/n] ").lower() != 'y':
print('aborting')
return
oldcur.execute("SELECT serialnumber, Givenname, Surname, Created, FirstValidDate, PublicKey FROM user")
for user in oldcur.fetchall():
newcur.execute("INSERT INTO user (serial, name, surname, created, firstValid, lastValid, pubkey) VALUES (?, ?, ?, ?, ?, ?, ?)", (user[0], user[1], user[2], user[3], '2023-04-02', user[4], user[5]))
newconn.commit()
newconn.close()
oldconn.close()
def nickchanges():
cur, conn = get_db(NEW_DB)
cur.execute("SELECT serial, name, surname FROM user WHERE nick IS NULL")
for user in cur.fetchall():
print("\nEin shackie namens " + user[1] + ' ' + user[2])
print("\nnick (leer für Vorname): ")
nick = raw_input()
if nick == "":
nick = user[1]
cur.execute("UPDATE user SET nick="+nick+" WHERE serial="+str(user[0]))
print("All users done!")
if __name__ == '__main__':
if IMPORT:
data_import()
else:
nickchanges()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment