Create new FlintPro residue SQLite database

Including 3 tables in the database.

# Read in the Mullion FlintPro sqlite db

con = sqlite3.connect(“Simplified_RothC_DB.sqlite”)

cur = con.cursor()

df = pd.read_sql_query(‘SELECT * FROM soil_inputs;’, con)

# df = pd.read_sql_query(‘SELECT * FROM soil_inputs;’, con)

# close the connection

con.close()

df

# make a loop to make a dataframe (with 432 rows) that has 77 new dataframes appended (with admin_id increasing by 1 for each dataframe)

# create an empty dataframe

df_final = pd.DataFrame(columns = [‘admin_id’, ‘year’, ‘month’, ‘plant_residue’, ‘manure’,’soil_cover’])

# run for loop

for item in range(0,77):

df_item = df[‘admin_id’].replace(1, item + 1)

df_plus_1 = pd.DataFrame([df_item, df.year, df.month, df.plant_residue, df.manure, df.soil_cover]).transpose()

#append the dataframes

df_final = df_final.append(df_plus_1)

# should be 432 * 77 rows

print(f’test = length / 432 — correct code will have 77, this code gives:’, len(df_final)/432)

print(‘final number divided by 432’, len(df_final)/432)

df_final [‘plant_residue’] = 0.05443

df_final [‘manure’] = 0.04

df_final

# export file back to SQLite FlintPro folder

engine = create_engine(‘sqlite:///BODO_77_paddocks_Reg_values_2207.db’, echo=True)

sqlite_connection = engine.connect()

# Save dataframe to a SQLlite

df_final.to_sql(‘soil_inputs’, sqlite_connection, if_exists=’append’) # if_exists = ‘append’ or ‘fail’

sqlite_connection.close()

#################### now read and join the other 2 tables.

# — template — — Create a SQL connection to our SQLite database

con = sqlite3.connect(“Simplified_RothC_DB.sqlite”)

cur = con.cursor()

# select one of the tables

df = pd.read_sql_query(‘SELECT * FROM “project_potential”;’, con) # 6-core-sublayer_data, 0-sites_data

# close the connection

con.close()

df

engine = create_engine(‘sqlite:///BODO_77_paddocks_Reg_values_2207.db’, echo=True)

sqlite_connection = engine.connect()

# Save dataframe to a SQLlite

df.to_sql(‘project_potential’, sqlite_connection, if_exists=’append’) # if_exists = ‘append’ or ‘fail’

sqlite_connection.close()

####################### now read and join the other 2 tables.

# — template — — Create a SQL connection to our SQLite database

con = sqlite3.connect(“Simplified_RothC_DB.sqlite”)

cur = con.cursor()

# select one of the tables

df = pd.read_sql_query(‘SELECT * FROM “soil_type”;’, con) # 6-core-sublayer_data, 0-sites_data

# close the connection

con.close()

df

engine = create_engine(‘sqlite:///BODO_77_paddocks_Reg_values_2207.db’, echo=True)

sqlite_connection = engine.connect()

# Save dataframe to a SQLlite

df.to_sql(‘soil_type’, sqlite_connection, if_exists=’append’) # if_exists = ‘append’ or ‘fail’

sqlite_connection.close()