def db_downgrade(step):
"""
Downgrades a DB to a previous version as specified with the `step`
:param step: number of downgrades to do
:return: None
"""
to_use = [_.strip('.sql') for _ in migration_files()]
# since it's a downgrade, a reverse of the migration is essential
to_use.reverse()
generate_migration_file()
dbd_query = anosql.from_path(MIGRATION_FILE, 'psycopg2')
try:
count = 0
for _ in to_use:
count += 1
if MySQLScheme.fetch_one(REVISION_EXISTS,
**{"args": {'revision': _}}):
MySQLScheme.commit(getattr(dbd_query, f"downgrade_{_}").sql)
LOGGER.info(f"successful downgrade: {_}")
if count == step:
break
except errors.ProgrammingError:
print("no more downgrade left")
def db_initialise():
"""
Create the migrations folder and DB table if they are non-existent
:return: None
"""
generate_migration_file()
if not MySQLScheme.fetch_one(IS_MIGRATION_TABLE,
**{"args": {'schema': SCHEMA}}):
with open(MIGRATION_FILE, 'r') as init_sql:
data = init_sql.read()
if f"CREATE TABLE IF NOT EXISTS {MIGRATION_TABLE}" not in data:
when = str(int(time.time()))
sql_file = os.path.join(MIGRATION_FOLDER, f"{when}.sql")
with open(sql_file, 'w') as save_sql:
up = MYSQL_MIGRATION_UP.format(f"upgrade-{when}", when,
MIGRATION_TABLE)
down = MYSQL_MIGRATION_DOWN.format(f"downgrade-{when}",
MIGRATION_TABLE)
save_sql.write("\n\n".join([up, down]))
LOGGER.info(f"migration file: "
f"{os.path.join('migrations', sql_file)}")
else:
when = re.findall('[0-9]+', data)[0]
generate_migration_file()
dbi_query = anosql.from_path(MIGRATION_FILE, 'psycopg2')
MySQLScheme.commit(getattr(dbi_query, f"upgrade_{when}").sql)
LOGGER.info(f"initial successful migration: {when}")
def __init__(self):
self.pgconn = psycopg2.connect(PGCONNSTRING)
self.queries = None
for sql in ['sql/album.sql', 'sql/artist.sql', 'sql/genre-topn.sql']:
queries = anosql.from_path(sql, 'psycopg2')
if self.queries:
for qname in queries.available_queries:
self.queries.add_query(qname, getattr(queries, qname))
else:
self.queries = queries
def initdb(data):
click.echo('-> Initializing database...')
db.create_all()
admin_user = User(username='******', name='Administrator')
admin_user.set_password('laevus')
db.session.add(admin_user)
db.session.commit()
sql_queries = anosql.from_path(os.path.join(data, 'schema.sql'), 'psycopg2')
with sqla_raw_conn() as cnx:
sql_queries.create_views(cnx)
click.echo('-> Database initialized.')
def post(self):
observation_dict = self.post_parser.parse_args(strict=True)
insert_query = anosql.from_path(
os.path.join(os.path.dirname(picinceptor.__file__), 'database',
'insert_observation.sql'),
'psycopg2').insert_observation
with psycopg2.connect(host=current_app.config['DB_HOST'],
port=current_app.config.get('DB_PORT', 5432),
user=current_app.config['DB_USER'],
password=current_app.config['DB_PASS'],
dbname=current_app.config['DB_NAME']) as cnx:
insert_query(cnx, **observation_dict)
def db_upgrade():
"""
Runs an upgrade on a DB using the generated `MIGRATION_FILE`
:return: None
"""
generate_migration_file()
dbu_query = anosql.from_path(MIGRATION_FILE, 'psycopg2')
for time_step in [_.strip('.sql') for _ in migration_files()]:
decide = MySQLScheme.fetch_one(REVISION_EXISTS,
**{"args": {'revision': time_step}})
if not decide:
MySQLScheme.commit(getattr(dbu_query, f"upgrade_{time_step}").sql)
LOGGER.info(f"successful migration: {time_step}")
else:
LOGGER.info(f'migration already exists: {time_step}')
def __init__(self, user: str, password: str, host: str, port: int,
database: str):
tables = ["repositories", "contributors", "stats_contributions"]
self.connection = self._create_connection(user, password, host, port,
database)
# init queries
self.queries = None
for table in tables:
queries = anosql.from_path(f"{service.ROOT_DIR}/sql/{table}.sql",
"psycopg2")
if self.queries:
for qname in queries.available_queries:
self.queries.add_query(qname, getattr(queries, qname))
else:
self.queries = queries
# init db
getattr(self.queries, f"create_{table}")(self.connection)
def __enter__(self):
self.conn = sqlite3.connect(self.filename)
# Load functions
functions = inspect.getmembers(self.sqlhelpers, inspect.isfunction)
for name, function in functions:
parameters = inspect.signature(function).parameters
self.conn.create_function(name, len(parameters), function)
# Load queries
queries = anosql.from_path("sql", "sqlite3")
for query in queries.available_queries:
function = getattr(queries, query)
setattr(self, query, functools.partial(function, self.conn))
self.queries[query] = set()
for match in anosql.patterns.var_pattern.finditer(function.sql):
var_name = match.group("var_name")
if var_name:
self.queries[query].add(var_name)
return self
def get(self):
query = anosql.from_path(
os.path.join(os.path.dirname(picinceptor.__file__), 'database',
'select_observations.sql'),
'psycopg2').get_observations
with psycopg2.connect(host=current_app.config['DB_HOST'],
port=current_app.config.get('DB_PORT', 5432),
user=current_app.config['DB_USER'],
password=current_app.config['DB_PASS'],
dbname=current_app.config['DB_NAME']) as cnx:
rows = query(cnx)
# build response
res = {'type': 'FeatureCollection', 'features': []}
features = res['features']
for row in rows:
(obs_id, obs_date, woodpecker_id, breeding_code, habitat,
dominant_tree, has_dead_trees, has_conifers, observer_first_name,
observer_surname, observer_school, geojson) = row
observer_str = '{0} {1}'.format(observer_first_name,
observer_surname).strip()
observer_str = ('{0} [{1}]'.format(observer_str, observer_school)
if observer_school.strip() else
observer_str).strip()
features.append({
'type': 'Feature',
'id': obs_id,
'properties': {
'observationDate': obs_date.strftime('%Y-%m-%d'),
'woodpeckerId': woodpecker_id,
'breedingCode': breeding_code,
'habitat': habitat,
'dominantTree': dominant_tree,
'hasDeadTrees': has_dead_trees,
'hasConifers': has_conifers,
'observer': observer_str
},
'geometry': json.loads(geojson)
})
return res
#!/usr/bin/env python3
from fastapi import FastAPI, File, UploadFile
from starlette.responses import FileResponse, Response
import anosql
import datetime
import json
import shutil
import sqlite3
import tempfile
queries = anosql.from_path('queries.sql', 'sqlite3')
def run_query_on_db(db_path, query_name):
with sqlite3.connect(db_path) as con:
return getattr(queries, query_name)(con)[0][0]
def run_query_on_form(file, query_name):
with tempfile.NamedTemporaryFile() as tf:
shutil.copyfileobj(file.file, tf)
return run_query_on_db(tf.name, query_name)
def generate_filename(extension):
return 'geopap-{}.{}'.format(datetime.date.today(), extension)
app = FastAPI()
def queries():
dir_path = os.path.join(os.path.dirname(os.path.abspath(__file__)),
"blogdb", "sql")
return anosql.from_path(dir_path, "sqlite3")
def add_queries_from_path(self, fn: str):
"""Load queries from a file or directory."""
self._create_fns(sql.from_path(fn, self._db))
def table_queries(self):
return anosql.from_path(f"{SQL_DIR}/create_tables.sql", "psycopg2")
def drop_duplicates_queries(self):
return anosql.from_path(f"{SQL_DIR}/drop_duplicates.sql", "psycopg2")
def get_query(config_path):
query = anosql.from_path(config_path, 'psycopg2')
return query
def insert_queries(self):
return anosql.from_path(f"{SQL_DIR}/insert_values.sql", "psycopg2")
def create_index_queries(self):
return anosql.from_path(f"{SQL_DIR}/create_indexes.sql", "psycopg2")
def __init__(self):
self.connection = self._connect_db()
self.queries = anosql.from_path(
f"{api.ROOT_DIR}/sql/stats_contributions.sql", "psycopg2")
def queries():
dir_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), "blogdb", "sql")
return anosql.from_path(dir_path, "psycopg2")
