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()