def add_hstore(self, dbname, host, port, user, password):
conn_string = "dbname={0} host={1} port={2} user={3} password={4}".format(
dbname, host, port, user, password)
with dbconnect(conn_string) as conn:
cur = conn.cursor()
cur.execute(sql.SQL("CREATE EXTENSION IF NOT EXISTS hstore;"))
conn.commit()
cur.close()
return None
def make_db_schema(self, dbname, host, port, user, password, schema):
conn_string = "dbname={0} host={1} port={2} user={3} password={4}".format(
dbname, host, port, user, password)
with dbconnect(conn_string) as conn:
cur = conn.cursor()
cur.execute(
sql.SQL("CREATE SCHEMA IF NOT EXISTS {};").format(
sql.Identifier(schema)))
conn.commit()
cur.close()
return None
def make_db_extensions(self, dbname, host, port, user, password):
conn_string = "dbname={0} host={1} port={2} user={3} password={4}".format(
dbname, host, port, user, password)
with dbconnect(conn_string) as conn:
cur = conn.cursor()
cur.execute(
sql.SQL(
"CREATE EXTENSION IF NOT EXISTS postgis; CREATE EXTENSION IF NOT EXISTS pgrouting;"
))
conn.commit()
cur.close()
return None
from flask import Flask, request, render_template
from psycopg2 import connect as dbconnect
from os import environ
from datetime import datetime
from time import sleep
app = Flask(__name__)
for x in range(5):
try:
conn = dbconnect(dbname=environ["POSTGRES_DB"],
user=environ["POSTGRES_USER"],
password=environ["POSTGRES_PASSWORD"],
host="postgres")
except:
print("Connexion error, retry in 5 seconds")
sleep(5)
else:
break
@app.route("/", methods=["GET", "POST"])
def index():
cur = conn.cursor()
if request.method == "POST":
cur.execute("INSERT INTO product (name, stock) VALUES (%s, %s);",
(request.form["name"], request.form["stock"]))
cur.execute("SELECT name, stock FROM product")
return render_template("index.html", products=cur.fetchall())
def make_new_database(self, dbname, host, port, user, password):
# Log into maintenance database and create the new DB
# TODO: parameterize maintenance DB (even though it will almost certainly be "postgres")
conn_string = "dbname=postgres host={0} port={1} user={2} password={3}".format(
host, port, user, password)
with dbconnect(conn_string) as conn:
conn.autocommit = True # connection MUST be in autocommit mode to create databases!
cur = conn.cursor()
cur.execute(
sql.SQL("CREATE DATABASE {};").format(sql.Identifier(dbname)))
conn.commit()
cur.close()
# Add the connection to QGIS
settings = QSettings()
settings.setValue(
"PostgreSQL/connections/{0}/allowGeometrylessTables".format(
self.db_credentials["name"]), "false")
settings.setValue(
"PostgreSQL/connections/{0}/authcfg".format(
self.db_credentials["name"]), "")
settings.setValue(
"PostgreSQL/connections/{0}/database".format(
self.db_credentials["name"]), self.db_credentials["dbname"])
settings.setValue(
"PostgreSQL/connections/{0}/dontResolveType".format(
self.db_credentials["name"]), "false")
settings.setValue(
"PostgreSQL/connections/{0}/estimatedMetadata".format(
self.db_credentials["name"]), "false")
settings.setValue(
"PostgreSQL/connections/{0}/geometryColumnsOnly".format(
self.db_credentials["name"]), "false")
settings.setValue(
"PostgreSQL/connections/{0}/host".format(
self.db_credentials["name"]), self.db_credentials["host"])
settings.setValue(
"PostgreSQL/connections/{0}/password".format(
self.db_credentials["name"]), self.db_credentials["password"])
settings.setValue(
"PostgreSQL/connections/{0}/port".format(
self.db_credentials["name"]), self.db_credentials["port"])
settings.setValue(
"PostgreSQL/connections/{0}/publicOnly".format(
self.db_credentials["name"]), "false")
settings.setValue(
"PostgreSQL/connections/{0}/savePassword".format(
self.db_credentials["name"]),
self.db_credentials["save_password"])
settings.setValue(
"PostgreSQL/connections/{0}/saveUsername".format(
self.db_credentials["name"]),
self.db_credentials["save_username"])
settings.setValue(
"PostgreSQL/connections/{0}/service".format(
self.db_credentials["name"]), self.db_credentials["service"])
settings.setValue(
"PostgreSQL/connections/{0}/sslmode".format(
self.db_credentials["name"]), "1")
settings.setValue(
"PostgreSQL/connections/{0}/username".format(
self.db_credentials["name"]), self.db_credentials["user"])
QCoreApplication.processEvents() # refresh browser panel
return None