def setUp(self):
"""
Create the database if not created!
NOTE: this part work with mysql database,
if you use any other database you may consider this part and make the necessary changes
"""
self.client_app = app.test_client()
try:
db.drop_all()
except Exception as e:
from flask_sqlalchemy import sqlalchemy
e = sqlalchemy.create_engine(os.getenv('SQLALCHEMY_DATABASE_URI'))
existing_databases = e.execute("SHOW DATABASES;")
# Results are a list of single item tuples, so unpack each tuple
existing_databases = [d[0] for d in existing_databases]
# Create database if not exists
if os.getenv('DATABASE_NAME_TESTING') not in existing_databases:
e.execute(
f"CREATE DATABASE {os.getenv('DATABASE_NAME_TESTING')}")
# do it again
db.drop_all()
# now create the tables
db.create_all()
# add the default Data
defaultData(app=app, db=db)
def drop_db(self):
# drop the database if root user
engine = sqlalchemy.create_engine(self.db_uri)
conn = engine.connect()
conn.execute("COMMIT")
conn.execute(
"SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE datname = 'counter_test' AND pid <> pg_backend_pid()"
)
conn.close()
if self.db_root_password:
engine = sqlalchemy.create_engine(self.db_uri)
conn = engine.connect()
conn.execute("COMMIT")
conn.execute("DROP DATABASE " + self.db_name)
conn.close()
def drop_db(self):
# дропнем БД под root
if self.db_root_password:
engine = sqlalchemy.create_engine(self.db_uri)
conn = engine.connect()
conn.execute("COMMIT")
conn.execute("DROP DATABASE " + self.db_name)
conn.close()
def drop_db(self):
# drop the database if root user
if self.db_root_password:
engine = sqlalchemy.create_engine(self.db_uri)
conn = engine.connect()
conn.execute("COMMIT")
conn.execute("DROP DATABASE " + self.db_name)
conn.close()
def create_db(self):
# create the database if root user
if self.db_root_password:
engine = sqlalchemy.create_engine(self.db_uri)
conn = engine.connect()
conn.execute("COMMIT")
conn.execute("CREATE DATABASE " + self.db_name)
conn.close()
return self.db_uri + '/' + self.db_name
def create_db(self):
# создадим БД из под root
if self.db_root_password:
engine = sqlalchemy.create_engine(self.db_uri)
conn = engine.connect()
conn.execute("COMMIT")
conn.execute("CREATE DATABASE " + self.db_name)
conn.close()
return self.db_uri + '/' + self.db_name
def setUp(self):
# we need to use the root user
# to be able to create the new database
self.db_username = '******'
self.db_password = os.environ['MYSQL_ROOT_PASSWORD']
self.db_name = os.environ['DATABASE_NAME'] + '_test'
self.db_uri = 'mysql+pymysql://%s:%s@%s' % (self.db_username,
self.db_password, DB_HOST)
engine = sqlalchemy.create_engine(self.db_uri)
conn = engine.connect()
conn.execute("commit")
conn.execute("create database " + self.db_name)
conn.close()
self.app_factory = self.create_app()
self.app = self.app_factory.test_client()
with self.app_factory.app_context():
db.create_all()
def create_database(flask_app):
"""Create a Postgres database instance and a postgis extension.
Probably should not be used outside of the context of flask application
(needs more testing)
"""
engine = sqlalchemy.create_engine(
flask_app.config.get('SQLALCHEMY_DATABASE_URI')
)
# Create database
if not sqlalchemy_utils.database_exists(engine.url):
sqlalchemy_utils.create_database(engine.url)
# Create postgis extension
engine.execute('CREATE EXTENSION IF NOT EXISTS postgis;')
return engine
def connect():
# read connection parameters
params = config()
POSTGRES_URL = params.get("url")
POSTGRES_USER = params.get("user")
POSTGRES_PW = params.get("password")
POSTGRES_DB = params.get("database")
DB_URL = 'postgresql+psycopg2://{user}:{pw}@{url}/{db}'.format(
user=POSTGRES_USER, pw=POSTGRES_PW, url=POSTGRES_URL, db=POSTGRES_DB)
# The return value of create_engine() is our connection object
dbConnection = sqlalchemy.create_engine(DB_URL, client_encoding='utf8')
# We then bind the connection to MetaData()
dbMetaInfo = sqlalchemy.MetaData(bind=dbConnection, reflect=True)
return dbConnection, dbMetaInfo
def check_database():
"""check if the database found. if not , create the database and then create the tables.
NOTE: this part work with mysql database,
if you use any other database you may consider this part and make the necessary changes """
from app.models import db, defaultData
from flask_sqlalchemy import sqlalchemy
import os
e = sqlalchemy.create_engine(os.getenv('SQLALCHEMY_DATABASE_URI'))
existing_databases = e.execute("SHOW DATABASES;")
# Results are a list of single item tuples, so unpack each tuple
existing_databases = [d[0] for d in existing_databases]
# Create database if not exists
if os.getenv('DATABASE_NAME_DEVELOPMENT') not in existing_databases:
e.execute(f"CREATE DATABASE {os.getenv('DATABASE_NAME_DEVELOPMENT')}")
# now create the tables
db.create_all(app=fullApp)
# add the default Data
defaultData(app=fullApp, db=db)
from flask_sqlalchemy import sqlalchemy
import pandas as pd
baglan = sqlalchemy.create_engine('postgresql://*****:*****@localhost:5432/postgres')
baglanti = baglan.connect()
sorgu1 = baglanti.execute('SELECT * FROM typicode.albums LIMIT 10')
sonuc_tablo = pd.DataFrame(sorgu1.fetchall())
print(sonuc_tablo)
@app.route('/api/v1/resources/users', methods=['GET'])
def api_id():
# Check if an ID was provided as part of the URL.
# If ID is provided, assign it to a variable.
# If no ID is provided, display an error in the browser.
if 'id' in request.args:
id = int(request.args['id'])
else:
return "Error: No id field provided. Please specify an id."
# Create an empty list for our results
results = []
# Loop through the data and match results that fit the requested ID.
# IDs are unique, but other fields might return many results
for user in users:
if user['id'] == id:
results.append(user)
# Use the jsonify function from Flask to convert our list of
from flask import Flask
from flask_sqlalchemy import sqlalchemy
from sqlalchemy import *
import os
from dotenv import load_dotenv
project_folder = os.path.expanduser('/home/ubuntu/app')
load_dotenv(os.path.join(project_folder, '.env'))
app = Flask(__name__)
app.testing = True
print(os.environ)
SQLALCHEMY_DATABASE_URI = os.environ.get('SQLALCHEMY_DATABASE_URI')
print(SQLALCHEMY_DATABASE_URI)
engine = sqlalchemy.create_engine(SQLALCHEMY_DATABASE_URI)
@app.route('/')
def hello():
metadata = MetaData()
# Declare a table
table = Table('Employee',
metadata,
Column('EmployeeId', Integer),
Column('EmployeeName', String(16)),
schema="Employees")
# Create all tables
metadata.create_all(engine)
inspector = inspect(engine)
return str(inspector.get_columns('Employee'))
def __init__(self, connection_data):
"""Initialize connection (self.connxn) to Google Cloud SQL database (or proxy).
Arguments:
self
app: Flask application instance
connection_data: dictionary containing variables for database connection
connection_data['username']: username for connection to database
connection_data['password']: password for connection to database
connection_data['database']: database name
connection_data['connection_name']: name of cloud sql instance
Returns: void
"""
# Setup sqlalchemy connection engine for database connection
try:
# Production environment set-up
if connection_data['environment'] == 'prod':
logging.debug(
'QueryTool.__init__(): creating engine in production')
self.db = sqlalchemy.create_engine(
sqlalchemy.engine.url.URL(
drivername='mysql+pymysql',
username=connection_data['username'],
password=connection_data['password'],
database=connection_data['database'],
query={
'unix_socket':
'/cloudsql/{}'.format(
connection_data['connection_name'])
}))
# Development environment set-up
elif connection_data['environment'] == 'dev' or connection_data[
'environment'] == 'local':
logging.debug('QueryTool.__init__(): creating engine in dev')
self.db = sqlalchemy.create_engine(
'mysql+pymysql://{}:{}@{}/{}'.format(
connection_data['username'],
connection_data['password'],
connection_data['connection_name'],
connection_data['database'],
))
# Log any errors encountered for trouble-shooting
except Exception as e:
logging.info(
'QueryTool.__init__(): exception thrown on engine creation. {}'
.format(e))
raise e
# Use engine to connect to database -- either Google Cloud SQL or Google Cloud SQL Proxy
try:
logging.debug('QueryTool.__init__(): connecting to db')
self.connxn = self.db.connect()
# Log any errors encountered for trouble-shooting
except Exception as e:
logging.info(
'QueryTool.__init__(): exception thrown on db connection. {}'.
format(e))
raise e
from project import db, create_app
from project.models import Class_tag
from flask_sqlalchemy import sqlalchemy
#create db.sqlite
app = create_app()
db.create_all(app=app)
#create rows in Class_tag table with labels
labels = ['peronospora', 'oidio', 'botrite', 'mal dell\'esca']
engine = sqlalchemy.create_engine('sqlite:///project/db.sqlite')
engine.connect()
Session = sqlalchemy.orm.sessionmaker(bind=engine)
session = Session()
for cl in labels:
session.add(Class_tag(name=cl))
session.commit()
def tearDown(self):
engine = sqlalchemy.create_engine(self.db_uri)
conn = engine.connect()
conn.execute("commit")
conn.execute("drop database " + self.db_name)
conn.close()
from geoalchemy2 import Geometry # <= not used but must be imported
from flask_sqlalchemy import sqlalchemy
from sqlalchemy.ext.automap import automap_base
from credentials import USERNAME, PASSWORD, HOSTNAME, PORT, DB_NAME
from contextlib import contextmanager
engine = sqlalchemy.create_engine(f"postgresql+psycopg2://{USERNAME}:{PASSWORD}@{HOSTNAME}:{PORT}/{DB_NAME}")
metadata = sqlalchemy.MetaData(engine)
metadata.reflect()
Base = automap_base(metadata=metadata)
Base.prepare(engine)
# All db tables can be queried using the API
Demand = Base.classes.Demand
Supply = Base.classes.Supply
Raw = Base.classes.Raw
Matches = Base.classes.Matches
Auth = Base.classes.Auth
UserLog = Base.classes.UserLog
Contact = Base.classes.Contact
Locations = Base.classes.Locations
Volunteer = Base.classes.Volunteer
@contextmanager
def get_session():
Session = sqlalchemy.orm.sessionmaker(engine)
session = Session()
try:
from app import app
from models.shared import db
from data import create_data
from models import *
if __name__ == "__main__":
# Create database if it doesn't exist already
full_url = app.config["SQLALCHEMY_DATABASE_URI"]
full_url_split = full_url.split("/")
url = "/".join(full_url_split[:-1])
database_name = full_url_split[-1]
engine = sqlalchemy.create_engine(url) # connect to server
create_str = f"CREATE DATABASE IF NOT EXISTS {database_name} ;"
engine.execute(create_str)
engine.execute(f"USE {database_name} ;")
try:
print("Creating database...")
db.init_app(app)
with app.app_context():
db.create_all()
create_data()
print(f"Database {database_name} created successfully!")
except Exception as e:
print("Database creation failed!")
print(e)
"project_id": firebase_project_id,
"private_key_id": firebase_private_key_id,
"private_key": firebase_private_key,
"client_email": firebase_client_email,
"client_id": firebase_client_id,
"auth_uri": firebase_auth_uri,
"token_uri": firebase_token_uri,
"auth_provider_x509_cert_url": firebase_auth_provider_x509_cert_url,
"client_x509_cert_url": client_x509_cert_url
}
firebase_setup = firebase_admin.initialize_app()
# firebase_config = {
# "apiKey" : os.environ.get("firebase_apiKey"),
# "authDomain" : os.environ.get("firebase_authDomain"),
# "databaseURL" : os.environ.get("firebase_databaseURL"),
# "projectId" : os.environ.get("firebase_projectId"),
# "storageBucket" : os.environ.get("firebase_storageBucket"),
# "messagingSenderId" : os.environ.get("firebase_messagingSenderId"),
# "appId" : os.environ.get("firebase_appId")
# }
# initiate = firebase
# auth = firebase.auth()
app = Flask("pyth")
engine = db.create_engine(os.environ.get("db_engine"))
Session = sessionmaker(bind=engine)
session = Session()
# TEAM 0x00
