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