def test_engine_options():
"""Test that SQLClient's engine can be configured with extra options."""
db = SQLClient(engine_options={'echo': True})
assert db.engine.echo is True
db = SQLClient(engine_options={'echo': False})
assert db.engine.echo is False
def test_duplicate_model_class_name():
"""Test that duplicate model class names are supported by SQLClient model
registry.
"""
# Since we're going to shadow the same model name, we need an alias to it
# for testing.
global DupAModel
_DupAModel = DupAModel
class DupAModel(DupModel):
__tablename__ = 'test_dup_dup_a'
id = sa.Column(sa.types.Integer(), primary_key=True)
db = SQLClient(model_class=DupModel)
db.create_all()
assert 'tests.fixtures.DupAModel' in db.models
assert db.models['tests.fixtures.DupAModel'] is _DupAModel
assert 'tests.test_client.DupAModel' in db.models
assert db.models['tests.test_client.DupAModel'] is DupAModel
model1 = DupAModel()
assert db.save(model1) is model1
model2 = _DupAModel()
assert db.save(model2) is model2
del DupAModel
def test_session_options():
"""Test that SQLClient's session can be configured with extra options."""
db = SQLClient(session_options={'autocommit': True})
assert db.session.autocommit is True
db = SQLClient(session_options={'autocommit': False})
assert db.session.autocommit is False
def filedb(tmpdir):
dbpath = str(tmpdir.mkdir(random_alpha()).join('file.db'))
config = {
'SQL_DATABASE_URI': 'sqlite:///{0}'.format(dbpath)
}
_filedb = SQLClient(config, model_class=Model)
_filedb.create_all()
yield _filedb
_filedb.disconnect()
os.remove(dbpath)
def refresh_collector(collector: Collector):
local_db = SQLClient(config, model_class=Model)
while True:
if not local_db.query(Collector).get(collector.id):
print(f'Collector {collector.id} exited')
exit(0)
if collector.type == 'slido':
refresh_slido_with_retry(collector.hash)
elif collector.type == 'youtube':
print('TODO')
time.sleep(5)
def test_reflect(filedb):
"""Test that table metadata can be reflected with an explicit declarative
base model.
"""
rdb = SQLClient(filedb.config)
assert len(rdb.tables) == 0
rdb.reflect()
assert len(rdb.tables) > 0
assert set(rdb.tables.keys()) == set(filedb.tables.keys())
for tablename, table in filedb.tables.items():
assert rdb.tables[tablename].name == table.name
def test_reflect(filedb):
"""Test that table metadata can be reflected with an explicit declarative
base model.
"""
rdb = SQLClient(filedb.config)
assert len(rdb.tables) == 0
rdb.reflect()
assert len(rdb.tables) > 0
assert set(rdb.tables.keys()) == set(filedb.tables.keys())
for tablename, table in filedb.tables.items():
assert rdb.tables[tablename].name == table.name
def test_config_string():
"""Test that a database URI string can be used to configure SQLClient."""
uri = 'sqlite:///test.db'
db = SQLClient(uri)
assert db.config['SQL_DATABASE_URI'] == uri
assert str(db.url) == uri
def test_transaction_autoflush_false(config_autoflush, autoflush, expected):
"""Test that transactions can override default session autoflush."""
db = SQLClient({'SQL_AUTOFLUSH': config_autoflush})
assert db.session.autoflush is config_autoflush
with db.transaction(autoflush=autoflush):
assert db.session.autoflush is expected
# Nested transactions override previous one.
with db.transaction(autoflush=not autoflush):
assert db.session.autoflush is not autoflush
assert db.session.autoflush is expected
assert db.session.autoflush is config_autoflush
def test_transaction_autoflush_false(config_autoflush, autoflush, expected):
"""Test that transactions can override default session autoflush."""
db = SQLClient({'SQL_AUTOFLUSH': config_autoflush})
assert db.session.autoflush is config_autoflush
with db.transaction(autoflush=autoflush):
assert db.session.autoflush is expected
# Nested transactions override previous one.
with db.transaction(autoflush=not autoflush):
assert db.session.autoflush is not autoflush
assert db.session.autoflush is expected
assert db.session.autoflush is config_autoflush
def db():
config = {'SQL_DATABASE_URI': 'sqlite://', 'SQL_ECHO': False}
_db = SQLClient(config, model_class=Model)
_db.create_all()
yield _db
_db.shutdown()
_db.drop_all()
def db():
config = {"SQL_DATABASE_URI": "sqlite://", "SQL_ECHO": False}
_db = SQLClient(config, model_class=Model)
_db.create_all()
yield _db
_db.disconnect()
_db.drop_all()
def get_db_connection():
return SQLClient(
{
'SQL_DATABASE_URI': app_config.database_uri,
'SQL_POOL_RECYCLE': 3600,
'SQL_POOL_PRE_PING': True
},
model_class=Model)
def test_duplicate_model_class_name():
"""Test that duplicate model class names are supported by SQLClient model
registry.
"""
# Since we're going to shadow the same model name, we need an alias to it
# for testing.
global DupAModel
_DupAModel = DupAModel
class DupAModel(DupModel):
__tablename__ = 'test_dup_dup_a'
id = sa.Column(sa.types.Integer(), primary_key=True)
db = SQLClient(model_class=DupModel)
db.create_all()
assert 'tests.fixtures.DupAModel' in db.models
assert db.models['tests.fixtures.DupAModel'] is _DupAModel
assert 'tests.test_client.DupAModel' in db.models
assert db.models['tests.test_client.DupAModel'] is DupAModel
model1 = DupAModel()
assert db.save(model1) is model1
model2 = _DupAModel()
assert db.save(model2) is model2
del DupAModel
def db():
config = {
'SQL_DATABASE_URI': 'sqlite://',
'SQL_ECHO': False
}
_db = SQLClient(config, model_class=Model)
_db.create_all()
yield _db
_db.disconnect()
_db.drop_all()
def filedb(tmpdir):
dbpath = str(tmpdir.mkdir(random_alpha()).join("file.db"))
config = {"SQL_DATABASE_URI": f"sqlite:///{dbpath}"}
_filedb = SQLClient(config, model_class=Model)
_filedb.create_all()
yield _filedb
_filedb.disconnect()
os.remove(dbpath)
def filedb(tmpdir):
dbpath = str(tmpdir.mkdir(random_alpha()).join('file.db'))
config = {'SQL_DATABASE_URI': 'sqlite:///{0}'.format(dbpath)}
_filedb = SQLClient(config, model_class=Model)
_filedb.create_all()
yield _filedb
_filedb.shutdown()
os.remove(dbpath)
def init_app(self, app):
options = {}
if self.model_class:
options['model_class'] = self.model_class
if self.query_class:
options['query_class'] = self.query_class
if self.session_class:
options['session_class'] = self.session_class
options['session_options'] = self.session_options
# Store SQLClient instances on app.extensions so it can be accessed
# through flask.current_app proxy.
app.extensions['sqlservice'] = SQLClient(app.config, **options)
# Ensure that the session is removed on app context teardown so we
# don't leave any sessions open after the request ends.
@app.teardown_appcontext
def shutdown_session(response_or_exc):
self.remove()
return response_or_exc
'SQL_DATABASE_URI':
'mysql+pymysql://root:monkeyxx@localhost:3306/stockData',
'SQL_ISOLATION_LEVEL': 'SERIALIZABLE',
'SQL_ECHO': True,
'SQL_ECHO_POOL': False,
'SQL_CONVERT_UNICODE': True,
'SQL_POOL_SIZE': 5,
'SQL_POOL_TIMEOUT': 30,
'SQL_POOL_RECYCLE': 3600,
'SQL_MAX_OVERFLOW': 10,
'SQL_AUTOCOMMIT': False,
'SQL_AUTOFLUSH': True,
'SQL_EXPIRE_ON_COMMIT': True
}
db = SQLClient(config, model_class=Model)
db.create_all()
data = {'name': 'Jenny', 'email': '*****@*****.**', 'phone': '555-867-5309'}
user = db.User.save(data)
assert user.to_dict() == {
'id': 1,
'name': 'Jenny',
'email': '*****@*****.**',
'phone': '5558675309'
}
assert user is db.User.get(data.id)
assert user is db.User.find_one(id=user.id)
#!/usr/bin/env python3
import logging
import time
from random import random
from sqlservice import SQLClient
from katatennis.db.config import config
from katatennis.db.game import Model, Game
db = SQLClient(config, model_class=Model)
logger = logging.getLogger(__name__)
def run(q):
"""
Simulate a game between 2 players
playerOne or playerTwo will randomly score until there is a winner
"""
print("Worker simulate game starting")
while True:
try:
g = q.get()
print("worker got the game = {}".format(g))
game = db.Game.get(g['id'])
time.sleep(2)
while not game.has_winner():
if random() > 0.5:
import argparse
import sys
import os
sys.path.insert(0, os.path.abspath(os.path.dirname(__file__)+"/.."))
from sqlservice import SQLClient
import config
db = SQLClient(config=config.SQL_VARIABLES)
from models import User
from werkzeug.security import generate_password_hash
def add_user(username, password):
user = User()
user.username = username
user.password = generate_password_hash(password)
db.session.add(user)
db.session.commit()
print('Added')
def del_user(username):
db.query(User).filter(User.username==username).delete()
db.session.commit()
print("Deleted")
parser = argparse.ArgumentParser(description="Add user")
subparser = parser.add_subparsers(title='subcommands', description='valid subcommands', help='sub-command help')
add_parser = subparser.add_parser('add')
#!/usr/bin/env python3
import datetime
import zmq
from flask import Flask, jsonify, render_template, request
from katatennis.db.config import config
from katatennis.db.game import Model, Game, GameSchema
from marshmallow import ValidationError
from sqlalchemy.exc import IntegrityError
from sqlservice import SQLClient
app = Flask(__name__)
db = SQLClient(config, model_class=Model)
with app.test_request_context():
db.create_all()
game_schema = GameSchema()
@app.route('/')
def home():
return render_template('index.html')
@app.route('/games/<int:pk>')
def game(pk):
try:
game = db.Game.get(pk)
from sqlservice import SQLClient
config = {
'SQL_DATABASE_URI': 'sqlite:///data.db',
'SQL_ISOLATION_LEVEL': 'SERIALIZABLE',
'SQL_ECHO': False,
'SQL_ECHO_POOL': False,
'SQL_CONVERT_UNICODE': True,
# 'SQL_POOL_SIZE': 5,
# 'SQL_POOL_TIMEOUT': 30,
# 'SQL_POOL_RECYCLE': 3600,
# 'SQL_MAX_OVERFLOW': 10,
'SQL_AUTOCOMMIT': False,
'SQL_AUTOFLUSH': False,
'SQL_EXPIRE_ON_COMMIT': True
}
db = SQLClient(config, model_class=Model)
youtube = None
def get_recent_liveChatId(stream_id):
request = youtube.liveBroadcasts().list(
part='snippet,contentDetails,status',
broadcastType='all',
maxResults=5,
mine=True)
res = request.execute()
chat_id = None
for r in res['items']:
if r['id'] == stream_id:
cd rds
python3 db_migration.py db migrate
python3 db_migration.py db upgrade
The migration script, which is added to the `migrations` directory, should be reviewed
and can be manually edited before running upgrade.
Note that the migration script requires more privileges than the default nephele\_user has,
so the script needs to be run with the privileged `inituser` instead, which it should do
by default. Both of these users should exist within the database if the
`sql/create_users.sql` script was run after the database was created."""
from flask import Flask
from flask_script import Manager
from flask_migrate import Migrate, MigrateCommand
from sqlservice import SQLClient
from nephele2.rds import rds_config
from nephele2.rds.db_models import Model, UserEmail, UserInfo, User, MachineInfo, Job, Checkpoints, JobType
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = rds_config.INIT_URI
db = SQLClient({'SQL_DATABASE_URI': rds_config.INIT_URI}, model_class=Model)
migrate = Migrate(app, db, compare_type=True)
manager = Manager(app)
manager.add_command('db', MigrateCommand)
if __name__ == '__main__':
manager.run()
