def drop_db(): """删除数据库""" database_uri = os.path.dirname(app.config['SQLALCHEMY_DATABASE_URI']) database_name = app.config['DATABASE_NAME'] engine = db.create_engine(database_uri) engine.execute("DROP DATABASE %s" % database_name)
def test_db_existence(): """ Check whether the __init__ created a db and user class table. """ db = SQLAlchemy() engine = db.create_engine(Config.SQLALCHEMY_DATABASE_URI, {}) inspect = db.inspect(engine) assert (inspect.has_table("user"))
def create_database(): database_uri_without_db_name = Config.SQLALCHEMY_DATABASE_URI[ :Config.SQLALCHEMY_DATABASE_URI.rfind('/') ] with db.create_engine(database_uri_without_db_name).connect() as conn: conn.execution_options( isolation_level='AUTOCOMMIT' ).execute('create database %s owner postgres'%(Config.DB_NAME))
def create_db(): """创建数据库""" database_uri = os.path.dirname(app.config['SQLALCHEMY_DATABASE_URI']) database_name = app.config['DATABASE_NAME'] engine = db.create_engine(database_uri) engine.execute( "CREATE DATABASE IF NOT EXISTS `%s` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci" % database_name)
def create_table(): engine = db.create_engine(app.config.get('SQLALCHEMY_DATABASE_URI'), {}) metadata = db.MetaData(bind=engine) metadata.reflect(bind=engine) time = db.Table('time', metadata, db.Column('id', db.Integer, primary_key=True), db.Column('time', db.DateTime), extend_existing=True) time.create(engine) return 'created table!'
def stats(): # extract info day_index = len(models.Game.query.filter_by(id=current_user.id).all()) current_par = models.Parameter.query.filter_by( session_code=current_user.session_code).all()[-1] # redirect consistent pace users if current_par.consistent_pace: return redirect(url_for('pace_loading')) # protect from new user visiting this page before make any decision if day_index == 0: return redirect(url_for('day_start')) # db to df db_connect = db.create_engine( app.config['SQLALCHEMY_DATABASE_URI']).connect() if current_par.game_type == 2 or current_par.game_type == 4: resdf = pd.read_sql_query( '''SELECT day_index,norder,ndemand,nsold,nlost FROM game WHERE id = {}'''.format(current_user.id), db_connect) resdf = resdf.rename( columns={ 'day_index': 'Day #', 'norder': 'Order Placed', 'ndemand': 'Customer Demand', 'nsold': 'Sold', 'nlost': 'Lost Sales' }) else: resdf = pd.read_sql_query( '''SELECT day_index,norder,ndemand,nsold,nlost,profit,total_profit FROM game WHERE id = {}'''.format(current_user.id), db_connect) resdf = resdf.rename( columns={ 'day_index': 'Day #', 'norder': 'Order Placed', 'ndemand': 'Customer Demand', 'nsold': 'Sold', 'nlost': 'Lost Sales', 'profit': 'Profit', 'total_profit': 'Total Profit' }) db_connect.close() html = resdf.to_html(index=False) plot = resdf.show_plot_stats_html(resdf, day_index) return render_template('stats.html', html=html, plot=plot, day_index=day_index)
def hello_db(): stmt = "SELECT VERSION()" # Return a string that indicates the MySQL server version #stmt = "SELECT CONNECTION_ID()" # Return the connection ID (thread ID) for the connection #stmt = "SELECT CURRENT_USER()" # The authenticated user name and host name #stmt = "SELECT DATABASE()" # Return the default (current) database name stmt = "SHOW TABLES" # Return list of non-temporary tables in current database #stmt = "show create database %s;" % current_app.config['MYSQL_DB'] #stmt = "show grants for %s;" % current_app.config['MYSQL_USER'] #stmt = "select * from item" #stmt = "select * from user" #stmt = "select * from user join item on user.id=item.owner_id" #stmt = "select * from item_user" #stmt = "SELECT i.id AS item_id, i.keyname, u.id AS user_id, u.keyname FROM item i JOIN item_user iu ON i.id = iu.item_id JOIN user u ON iu.user_id = u.id" # users, cnt_items #stmt = "SELECT u.keyname, COUNT(i.id) AS cnt_items FROM user u JOIN item_user iu ON u.id = iu.user_id JOIN item i ON i.id = iu.item_id GROUP BY u.keyname" # items, cnt_users #stmt = "SELECT i.keyname, COUNT(u.id) AS cnt_users FROM item i JOIN item_user iu ON i.id = iu.item_id JOIN user u ON u.id = iu.user_id GROUP BY i.keyname" #import pdb; pdb.set_trace() eng = db.create_engine(current_app.config['SQLALCHEMY_DATABASE_URI']) con = eng.connect() rs = con.execute(db.text(stmt)) con.close() cols = rs.keys() rows = rs.fetchall() result = '<b>' + stmt + '</b>' result += '<br/>| ' for col in cols: result += '<b>' + str(col) + '</b> | ' for row in rows: result += '<br/>| ' for i, col in enumerate(row): result += '%s | ' % col return result
from flask_migrate import Migrate, MigrateCommand from app import app, db from config import BaseConfig def create_database(): database_uri_without_db_name = BaseConfig.SQLALCHEMY_DATABASE_URI[: BaseConfig . SQLALCHEMY_DATABASE_URI .rfind( '/')] with db.create_engine(database_uri_without_db_name, {}).connect() as conn: conn.execution_options(isolation_level='AUTOCOMMIT').execute( 'create database family owner postgres') try: db.create_engine(BaseConfig.SQLALCHEMY_DATABASE_URI, {}).connect() except Exception as e: print(e) create_database() manager = Manager(app) migrate = Migrate(app, db) manager.add_command('db', MigrateCommand) if __name__ == '__main__': manager.run()
from flask_script import Manager from flask_migrate import Migrate, MigrateCommand from app import app, db from config import Config def create_database(): database_uri_without_db_name = Config.SQLALCHEMY_DATABASE_URI[ :Config.SQLALCHEMY_DATABASE_URI.rfind('/') ] with db.create_engine(database_uri_without_db_name).connect() as conn: conn.execution_options( isolation_level='AUTOCOMMIT' ).execute('create database %s owner postgres'%(Config.DB_NAME)) try: db.create_engine(Config.SQLALCHEMY_DATABASE_URI).connect() except Exception as e: print(e) create_database() manager = Manager(app) migrate = Migrate(app, db) manager.add_command('db', MigrateCommand) if __name__ == '__main__': manager.run()