Saddle/migrate_filehost.py

54 lines
1.4 KiB
Python
Executable File

#!/usr/bin/env python3
"""
Migrates a filehost sqlite database to Saddle's PostgreSQL database.
"""
import sys
import asyncio
import sqlite3
from datetime import datetime, timezone
import asyncpg
import config
def get_data(database_path):
"""Retrieves data from the filehost database."""
conn = sqlite3.connect(database_path)
conn.row_factory = sqlite3.Row
cur = conn.cursor()
data = cur.execute("SELECT * FROM uploads")
return data
def process_data(data):
"""Processes the data so that it may be inserted into PostgreSQL."""
new_data = []
for record in data:
filename = record['filename']
_id = filename.partition('_')[0]
users = {'iou1name': 1, 'Adalwulf': 2, 'consultx': 3, None: 1}
user_id = users[record['uploaded_by']]
upload_date = datetime.utcfromtimestamp(record['uploaded_date'])
upload_date.replace(tzinfo=timezone.utc)
tup = (user_id, _id, filename, upload_date)
new_data.append(tup)
return new_data
async def insert_data(data):
"""Inserts the processed data into PostgreSQL."""
conn = await asyncpg.connect(**config.db)
await conn.executemany(
"INSERT INTO upload (user_id, id, filename, upload_date) "
"VALUES ($1, $2, $3, $4)",
data)
await conn.close()
if __name__ == '__main__':
database_path = sys.argv[1]
data = get_data(database_path)
data = process_data(data)
asyncio.get_event_loop().run_until_complete(insert_data(data))