import pandas as pd
import numpy as np

import sqlite3

con = sqlite3.connect('Data/sqlite_dbs/TheOneSource.db')

for year in range(2012, 2025):
    print(f'Getting {year} data from db into dataframe')
    year_data = pd.read_sql(f'SELECT * FROM salaries WHERE year={year}', con)
    print('Done')
    year_data = year_data.drop('index', axis=1)
    year_data = year_data.reset_index(drop = True)
    year_data.index += 1
    print(f'Writing {year} to its own database')
    new_con = sqlite3.connect(f'Data/sqlite_dbs/TheOne.db')
    year_data.to_sql(f'{year}_salaries', con=new_con, if_exists='replace', index=True)
    new_con.close()
    print('Done. Connection closed.')
    print()
con.close()
print('TheOne con closed. Finished')