def runTest(self): import pymysql_sa pymysql_sa.make_default_mysql_dialect() from sqlalchemy import create_engine engine = create_engine('mysql://root@localhost/test') self.assertEqual(engine.dialect.name, 'mysql') self.assertEqual(engine.driver, 'pymysql')
def generate_getconn(engine, user, password, host, port, dbname, dirname=None): kwargs = {} if engine == 'mysql': # If using mysql, choose among MySQLdb or pymysql, # Trying to load MySQLdb, and if it fails, trying # to load and register pymysql try: import MySQLdb assert MySQLdb is not None # It can never be: just avoid pyflakes warnings except ImportError: import pymysql_sa pymysql_sa.make_default_mysql_dialect() # In the case of MySQL, we need to activate this flag kwargs['client_flag'] = 2 elif engine == 'sqlite': # By default, sqlite uses a timeout of 5 seconds. Given the # concurrency levels that WebLab-Deusto might achieve with # multiple users in a queue, this might not be enough. We # increase it to a minute and a half to avoid problems with # multiple concurrent users kwargs['timeout'] = 90 if dbname == ':memory:': kwargs['check_same_thread'] = False # Then load the sqlalchemy dialect. In order to do the # equivalent to: # # from sqlalchemy.dialects.mysql import base # dbi = base.dialect.dbapi() # # We import the module itself (sqlalchemy.dialects.mysql) import sqlalchemy.dialects as dialects __import__('sqlalchemy.dialects.%s' % engine) # And once imported, we take the base.dialect.dbapi dbi = getattr(dialects, engine).base.dialect.dbapi() if engine == 'sqlite': def getconn_sqlite(): return dbi.connect(database=get_sqlite_dbname(dbname, dirname), **kwargs) getconn = getconn_sqlite else: def getconn_else(): kwargs.update( dict(user=user, passwd=password, host=host, db=dbname)) if port is not None: kwargs['port'] = port return dbi.connect(**kwargs) getconn = getconn_else return getconn
def generate_getconn(engine, user, password, host, port, dbname, dirname = None): kwargs = {} if engine == 'mysql': # If using mysql, choose among MySQLdb or pymysql, # Trying to load MySQLdb, and if it fails, trying # to load and register pymysql try: import MySQLdb assert MySQLdb is not None # It can never be: just avoid pyflakes warnings except ImportError: import pymysql_sa pymysql_sa.make_default_mysql_dialect() # In the case of MySQL, we need to activate this flag kwargs['client_flag'] = 2 elif engine == 'sqlite': # By default, sqlite uses a timeout of 5 seconds. Given the # concurrency levels that WebLab-Deusto might achieve with # multiple users in a queue, this might not be enough. We # increase it to a minute and a half to avoid problems with # multiple concurrent users kwargs['timeout'] = 90 if dbname == ':memory:': kwargs['check_same_thread'] = False # Then load the sqlalchemy dialect. In order to do the # equivalent to: # # from sqlalchemy.dialects.mysql import base # dbi = base.dialect.dbapi() # # We import the module itself (sqlalchemy.dialects.mysql) import sqlalchemy.dialects as dialects __import__('sqlalchemy.dialects.%s' % engine) # And once imported, we take the base.dialect.dbapi dbi = getattr(dialects, engine).base.dialect.dbapi() if engine == 'sqlite': def getconn_sqlite(): return dbi.connect(database = get_sqlite_dbname(dbname, dirname), **kwargs) getconn = getconn_sqlite else: def getconn_else(): kwargs.update(dict(user = user, passwd = password, host = host, db = dbname)) if port is not None: kwargs['port'] = port return dbi.connect(**kwargs) getconn = getconn_else return getconn
def __init__(self, url): if url.startswith('mysql://'): try: import MySQLdb assert MySQLdb is not None # avoid warnings except ImportError: import pymysql_sa pymysql_sa.make_default_mysql_dialect() self.url = url self.config = Config(os.path.join(self.alembic_path, "alembic.ini")) self.config.set_main_option("script_location", self.alembic_path) self.config.set_main_option("url", self.url) self.config.set_main_option("sqlalchemy.url", self.url)
def deploy_testdb(options): from weblab.admin.deploy import insert_required_initial_data, populate_weblab_tests, generate_create_database import weblab.db.model as Model import weblab.core.coordinator.sql.model as CoordinatorModel import voodoo.sessions.db_lock_data as DbLockData import voodoo.sessions.sqlalchemy_data as SessionSqlalchemyData from sqlalchemy import create_engine try: import MySQLdb dbi = MySQLdb except ImportError: try: import pymysql_sa except ImportError: raise Exception("Neither MySQLdb nor pymysql have been installed. First install them by running 'pip install pymysql' or 'pip install python-mysql'") pymysql_sa.make_default_mysql_dialect() t_initial = time.time() db_dir = 'db' if not os.path.exists(db_dir): os.mkdir(db_dir) db_engine = options.testdb_engine weblab_db_username = options.testdb_user weblab_db_password = options.testdb_passwd weblab_admin_db_username = options.testdb_admin_user weblab_admin_db_password = options.testdb_admin_passwd if db_engine == 'mysql': weblab_test_db_str = 'mysql://%s:%s@localhost/WebLabTests%s' % (weblab_db_username, weblab_db_password,'%s') weblab_coord_db_str = 'mysql://%s:%s@localhost/WebLabCoordination%s' % (weblab_db_username, weblab_db_password, '%s') weblab_sessions_db_str = 'mysql://%s:%s@localhost/WebLabSessions' % (weblab_db_username, weblab_db_password) elif db_engine == 'sqlite': weblab_test_db_str = 'sqlite:///db/WebLabTests%s.db' weblab_coord_db_str = 'sqlite:///db/WebLabCoordination%s.db' weblab_sessions_db_str = 'sqlite:///db/WebLabSessions.db' else: raise Exception("db engine %s not supported" % db_engine) if options.testdb_create_db: create_database = generate_create_database(db_engine) if create_database is None: raise Exception("db engine %s not supported for creating database" % db_engine) t = time.time() error_message = 'Could not create database. This may happen if the admin db credentials are wrong.' create_database(error_message, weblab_admin_db_username, weblab_admin_db_password, "WebLab", weblab_db_username, weblab_db_password, db_dir = db_dir) create_database(error_message, weblab_admin_db_username, weblab_admin_db_password, "WebLabTests", weblab_db_username, weblab_db_password, db_dir = db_dir) create_database(error_message, weblab_admin_db_username, weblab_admin_db_password, "WebLabTests2", weblab_db_username, weblab_db_password, db_dir = db_dir) create_database(error_message, weblab_admin_db_username, weblab_admin_db_password, "WebLabTests3", weblab_db_username, weblab_db_password, db_dir = db_dir) create_database(error_message, weblab_admin_db_username, weblab_admin_db_password, "WebLabCoordination", weblab_db_username, weblab_db_password, db_dir = db_dir) create_database(error_message, weblab_admin_db_username, weblab_admin_db_password, "WebLabCoordination2", weblab_db_username, weblab_db_password, db_dir = db_dir) create_database(error_message, weblab_admin_db_username, weblab_admin_db_password, "WebLabCoordination3", weblab_db_username, weblab_db_password, db_dir = db_dir) create_database(error_message, weblab_admin_db_username, weblab_admin_db_password, "WebLabSessions", weblab_db_username, weblab_db_password, db_dir = db_dir) print "Databases created.\t\t\t\t[done] [%1.2fs]" % (time.time() - t) ##################################################################### # # Populating main database # for tests in ('','2','3'): print "Populating 'WebLabTests%s' database... \t\t" % tests, t = time.time() engine = create_engine(weblab_test_db_str % tests, echo = False) metadata = Model.Base.metadata metadata.drop_all(engine) metadata.create_all(engine) insert_required_initial_data(engine) populate_weblab_tests(engine, tests) print "[done] [%1.2fs]" % (time.time() - t) ##################################################################### # # Populating Coordination database # for coord in ('','2','3'): print "Populating 'WebLabCoordination%s' database...\t" % coord, t = time.time() engine = create_engine(weblab_coord_db_str % coord, echo = False) CoordinatorModel.load() metadata = CoordinatorModel.Base.metadata metadata.drop_all(engine) metadata.create_all(engine) print "[done] [%1.2fs]" % (time.time() - t) ##################################################################### # # Populating Sessions database # print "Populating 'WebLabSessions' database...\t\t", t = time.time() engine = create_engine(weblab_sessions_db_str, echo = False) metadata = DbLockData.SessionLockBase.metadata metadata.drop_all(engine) metadata.create_all(engine) metadata = SessionSqlalchemyData.SessionBase.metadata metadata.drop_all(engine) metadata.create_all(engine) print "[done] [%1.2fs]" % (time.time() - t) print "Total database deployment: \t\t\t[done] [%1.2fs]" % (time.time() - t_initial)
def generate_create_database(engine_str): "Generate a create_database function that creates the database" if engine_str == 'sqlite': import sqlite3 dbi = sqlite3 def create_database_sqlite(admin_username, admin_password, database_name, new_user, new_password, host = "localhost", port = None, db_dir = '.'): fname = os.path.join(db_dir, '%s.db' % database_name) if os.path.exists(fname): os.remove(fname) sqlite3.connect(database = fname).close() return create_database_sqlite elif engine_str == 'mysql': try: import MySQLdb dbi = MySQLdb except ImportError: try: import pymysql_sa except ImportError: raise Exception("Neither MySQLdb nor pymysql have been installed. First install them by running 'pip install pymysql' or 'pip install python-mysql'") pymysql_sa.make_default_mysql_dialect() import pymysql dbi = pymysql def create_database_mysql(error_message, admin_username, admin_password, database_name, new_user, new_password, host = "localhost", port = None, db_dir = '.'): args = { 'DATABASE_NAME' : database_name, 'USER' : new_user, 'PASSWORD' : new_password, } sentence1 = "DROP DATABASE IF EXISTS %(DATABASE_NAME)s;" % args sentence2 = "CREATE DATABASE %(DATABASE_NAME)s;" % args sentence3 = "GRANT ALL ON %(DATABASE_NAME)s.* TO '%(USER)s'@'%%' IDENTIFIED BY '%(PASSWORD)s';" % args sentence4 = "GRANT ALL ON %(DATABASE_NAME)s.* TO '%(USER)s'@'localhost' IDENTIFIED BY '%(PASSWORD)s';" % args sentence5 = "FLUSH PRIVILEGES;" % args try: kwargs = dict(db=database_name, user = admin_username, passwd = admin_password, host = host) if port is not None: kwargs['port'] = port dbi.connect(**kwargs).close() except Exception, e: if e[1].startswith("Unknown database"): sentence1 = "SELECT 1" for sentence in (sentence1, sentence2, sentence3, sentence4, sentence5): try: kwargs = dict(user = admin_username, passwd = admin_password, host = host) if port is not None: kwargs['port'] = port connection = dbi.connect(**kwargs) except dbi.OperationalError: traceback.print_exc() print("", file=sys.stderr) print(" %s" % error_message, file=sys.stderr) print("", file=sys.stderr) sys.exit(-1) else: cursor = connection.cursor() cursor.execute(sentence) connection.commit() connection.close() return create_database_mysql
""" import json import hashlib from sqlalchemy import create_engine from sqlalchemy.orm import scoped_session, sessionmaker from sqlalchemy.ext.declarative import declarative_base from labmanager import app SQLALCHEMY_ENGINE_STR = app.config['SQLALCHEMY_ENGINE_STR'] USE_PYMYSQL = app.config.get('USE_PYMYSQL', False) if USE_PYMYSQL: import pymysql_sa pymysql_sa.make_default_mysql_dialect() engine = create_engine(SQLALCHEMY_ENGINE_STR, convert_unicode=True, pool_recycle=3600) db_session = scoped_session( sessionmaker(autocommit=False, autoflush=False, bind=engine)) Base = declarative_base() Base.query = db_session.query_property() def init_db(drop=False): # import all modules here that might define models so that # they will be registered properly on the metadata. Otherwise # you will have to import them first before calling init_db()
""" import json import hashlib from sqlalchemy import create_engine from sqlalchemy.orm import scoped_session, sessionmaker from sqlalchemy.ext.declarative import declarative_base from labmanager import app SQLALCHEMY_ENGINE_STR = app.config['SQLALCHEMY_ENGINE_STR'] USE_PYMYSQL = app.config.get('USE_PYMYSQL', False) if USE_PYMYSQL: import pymysql_sa pymysql_sa.make_default_mysql_dialect() engine = create_engine(SQLALCHEMY_ENGINE_STR, convert_unicode=True, pool_recycle=3600) db_session = scoped_session(sessionmaker(autocommit=False, autoflush=False, bind=engine)) Base = declarative_base() Base.query = db_session.query_property() def init_db(drop = False): # import all modules here that might define models so that # they will be registered properly on the metadata. Otherwise # you will have to import them first before calling init_db() import labmanager.models assert labmanager.models != None # pyflakes ignore
def deploy_testdb(options): from weblab.admin.deploy import insert_required_initial_data, populate_weblab_tests, generate_create_database import weblab.db.model as Model import weblab.core.coordinator.sql.model as CoordinatorModel import voodoo.sessions.db_lock_data as DbLockData import voodoo.sessions.sqlalchemy_data as SessionSqlalchemyData from sqlalchemy import create_engine try: import MySQLdb dbi = MySQLdb except ImportError: try: import pymysql_sa except ImportError: raise Exception( "Neither MySQLdb nor pymysql have been installed. First install them by running 'pip install pymysql' or 'pip install python-mysql'" ) pymysql_sa.make_default_mysql_dialect() t_initial = time.time() db_dir = 'db' if not os.path.exists(db_dir): os.mkdir(db_dir) db_engine = options.testdb_engine weblab_db_username = options.testdb_user weblab_db_password = options.testdb_passwd weblab_admin_db_username = options.testdb_admin_user weblab_admin_db_password = options.testdb_admin_passwd if db_engine == 'mysql': weblab_test_db_str = 'mysql://%s:%s@localhost/WebLabTests%s' % ( weblab_db_username, weblab_db_password, '%s') weblab_coord_db_str = 'mysql://%s:%s@localhost/WebLabCoordination%s' % ( weblab_db_username, weblab_db_password, '%s') weblab_sessions_db_str = 'mysql://%s:%s@localhost/WebLabSessions' % ( weblab_db_username, weblab_db_password) elif db_engine == 'sqlite': weblab_test_db_str = 'sqlite:///db/WebLabTests%s.db' weblab_coord_db_str = 'sqlite:///db/WebLabCoordination%s.db' weblab_sessions_db_str = 'sqlite:///db/WebLabSessions.db' else: raise Exception("db engine %s not supported" % db_engine) if options.testdb_create_db: create_database = generate_create_database(db_engine) if create_database is None: raise Exception( "db engine %s not supported for creating database" % db_engine) t = time.time() error_message = 'Could not create database. This may happen if the admin db credentials are wrong.' create_database(error_message, weblab_admin_db_username, weblab_admin_db_password, "WebLab", weblab_db_username, weblab_db_password, db_dir=db_dir) create_database(error_message, weblab_admin_db_username, weblab_admin_db_password, "WebLabTests", weblab_db_username, weblab_db_password, db_dir=db_dir) create_database(error_message, weblab_admin_db_username, weblab_admin_db_password, "WebLabTests2", weblab_db_username, weblab_db_password, db_dir=db_dir) create_database(error_message, weblab_admin_db_username, weblab_admin_db_password, "WebLabTests3", weblab_db_username, weblab_db_password, db_dir=db_dir) create_database(error_message, weblab_admin_db_username, weblab_admin_db_password, "WebLabCoordination", weblab_db_username, weblab_db_password, db_dir=db_dir) create_database(error_message, weblab_admin_db_username, weblab_admin_db_password, "WebLabCoordination2", weblab_db_username, weblab_db_password, db_dir=db_dir) create_database(error_message, weblab_admin_db_username, weblab_admin_db_password, "WebLabCoordination3", weblab_db_username, weblab_db_password, db_dir=db_dir) create_database(error_message, weblab_admin_db_username, weblab_admin_db_password, "WebLabSessions", weblab_db_username, weblab_db_password, db_dir=db_dir) print "Databases created.\t\t\t\t[done] [%1.2fs]" % (time.time() - t) ##################################################################### # # Populating main database # for tests in ('', '2', '3'): print "Populating 'WebLabTests%s' database... \t\t" % tests, t = time.time() engine = create_engine(weblab_test_db_str % tests, echo=False) metadata = Model.Base.metadata metadata.drop_all(engine) metadata.create_all(engine) insert_required_initial_data(engine) populate_weblab_tests(engine, tests) print "[done] [%1.2fs]" % (time.time() - t) ##################################################################### # # Populating Coordination database # for coord in ('', '2', '3'): print "Populating 'WebLabCoordination%s' database...\t" % coord, t = time.time() engine = create_engine(weblab_coord_db_str % coord, echo=False) CoordinatorModel.load() metadata = CoordinatorModel.Base.metadata metadata.drop_all(engine) metadata.create_all(engine) print "[done] [%1.2fs]" % (time.time() - t) ##################################################################### # # Populating Sessions database # print "Populating 'WebLabSessions' database...\t\t", t = time.time() engine = create_engine(weblab_sessions_db_str, echo=False) metadata = DbLockData.SessionLockBase.metadata metadata.drop_all(engine) metadata.create_all(engine) metadata = SessionSqlalchemyData.SessionBase.metadata metadata.drop_all(engine) metadata.create_all(engine) print "[done] [%1.2fs]" % (time.time() - t) print "Total database deployment: \t\t\t[done] [%1.2fs]" % (time.time() - t_initial)