def createDb(conn, dbName, mayExist=False):
"""
Create database <dbName>.
@param conn Database connection or engine.
@param dbName Database name.
@param mayExist Flag indicating what to do if the database exists.
Raises InvalidDatabaseNameError if database name is invalid.
Raises DatabaseExistsError if the database already exists and mayExist is False.
Raises sqlalchemy exceptions.
Note, it will not connect to that database and it will not make it default.
"""
if dbName is None:
raise InvalidDatabaseNameError("CREATE DATABASE",
"None passed as database name", None)
# consider using create_database from helpers:
# http://sqlalchemy-utils.readthedocs.org/en/latest/database_helpers.html
if conn.engine.url.get_backend_name() == "mysql":
try:
conn.execute("CREATE DATABASE `%s`" % dbName)
except ProgrammingError as e:
if e.orig.args[0] == MySqlErr.ER_DB_CREATE_EXISTS:
if not mayExist:
raise DatabaseExistsError("CREATE DATABASE", dbName,
e.orig)
else:
raise
else:
raise NoSuchModuleError(conn.engine.url.get_backend_name())
def dropTable(conn, tableName, dbName=None, mustExist=True):
"""
Drop table <tableName> in database <dbName>.
@param tableName Table name.
@param dbName Database name.
@param mustExist Flag indicating what to do if the database does not exist.
If <dbName> is not set, the current database name will be used.
Raises NoSuchTableError if the table does not exist and the mustExist flag
is set to True.
Raises sqlalchemy exceptions.
"""
if conn.engine.url.get_backend_name() == "mysql":
dbNameStr = "`%s`." % dbName if dbName is not None else ""
try:
conn.execute("DROP TABLE %s`%s`" % (dbNameStr, tableName))
except DBAPIError as e:
if e.orig.args[0] == MySqlErr.ER_BAD_TABLE_ERROR:
if mustExist:
raise NoSuchTableError(dbNameStr + tableName)
return
raise
else:
raise NoSuchModuleError(conn.engine.url.get_backend_name())
def createTableLike(conn, dbName, tableName, templDb, templTable):
"""
Create table <dbName>.<tableName> like <templDb>.<templTable>
@param conn Database connection or engine.
@param dbName Name of the database where the tables should be created
@param tableName Name of the table to create
@param templDb Name of the database where the template table is
@param templTable Name of the template table
Raises TableExistsError if the to-be-created table already exists.
Raises NoSuchTableError if the template table does not exists.
Raises sqlalchemy exceptions.
"""
if conn.engine.url.get_backend_name() == "mysql":
query = "CREATE TABLE {0}.{1} LIKE {2}.{3}".format(
dbName, tableName, templDb, templTable)
try:
conn.execute(query)
except OperationalError as e:
if e.orig.args[0] == MySqlErr.ER_TABLE_EXISTS_ERROR:
raise TableExistsError("CREATE TABLE LIKE",
dbName + '.' + tableName, e.orig)
raise
except ProgrammingError as e:
if e.orig.args[0] == MySqlErr.ER_NO_SUCH_TABLE:
raise NoSuchTableError("CREATE TABLE LIKE", templTable, e.orig)
raise
else:
raise NoSuchModuleError(conn.engine.url.get_backend_name())
def createTable(conn, tableName, tableSchema, dbName=None, mayExist=False):
"""
Create table <tableName> in database <dbName>.
@param conn Database connection or engine.
@param tableName Table name.
@param tableSchema Table schema starting with opening bracket. Note that it can
NOT contain "--" or ";".
@param dbName Database name.
@param mayExist Flag indicating what to do if the database exists.
If database <dbName> is not set, and "use <database>" was called earlier,
it will use that database.
Raises TableExistsError if the table already exists and mayExist flag
is say to False.
Raises sqlalchemy exceptions.
"""
if conn.engine.url.get_backend_name() == "mysql":
dbNameStr = "`%s`." % dbName if dbName is not None else ""
cmd = "CREATE TABLE %s`%s` %s" % (dbNameStr, tableName, tableSchema)
try:
conn.execute(cmd)
except DBAPIError as e:
if e.orig.args[0] == MySqlErr.ER_NO_DB_ERROR:
raise InvalidDatabaseNameError(cmd, dbNameStr, e.orig)
elif e.orig.args[0] == MySqlErr.ER_TABLE_EXISTS_ERROR:
if mayExist:
return
raise TableExistsError(cmd, dbNameStr + tableName, e.orig)
else:
raise
else:
raise NoSuchModuleError(conn.engine.url.get_backend_name())
def dropDb(conn, dbName, mustExist=True):
"""
Drop database <dbName>.
@param conn Database connection or engine.
@param dbName Database name.
@param mustExist Flag indicating what to do if the database does not exist.
Raises NoSuchDatabaseError if the database does not exist and the
flag mustExist is set to True.
Raises sqlalchemy exceptions.
Disconnect from the database if it is the current database.
"""
if not mustExist and not dbExists(conn, dbName):
return
# consider using create_database from helpers:
# http://sqlalchemy-utils.readthedocs.org/en/latest/database_helpers.html
if conn.engine.url.get_backend_name() == "mysql":
try:
conn.execute("DROP DATABASE `%s`" % dbName)
except DBAPIError as e:
if e.orig.args[0] == MySqlErr.ER_DB_DROP_EXISTS:
raise NoSuchDatabaseError("DROP DATABASE", dbName, e.orig)
else:
raise
else:
raise NoSuchModuleError(conn.engine.url.get_backend_name())
def typeCode2Name(conn, code):
"""
Convert type code to type name, returns None if there is no mapping.
"""
if conn.engine.url.get_backend_name() == "mysql":
for name in dir(FIELD_TYPE):
if getattr(FIELD_TYPE, name) == code:
return name
return None
else:
raise NoSuchModuleError(conn.engine.url.get_backend_name())
def userExists(conn, userName, hostName):
"""
Return True if user <hostName>@<userName> exists, False otherwise.
Raises sqlalchemy exceptions.
"""
if conn.engine.url.get_backend_name() == "mysql":
return conn.execute(
"SELECT COUNT(*) FROM mysql.user WHERE user='******' AND host='%s'" %
(userName, hostName)).scalar() == 1
else:
raise NoSuchModuleError(conn.engine.url.get_backend_name())
def listTables(conn, dbName=None):
"""
Return list of tables in a given database. If dbName is none, it uses
current database.
Raises sqlalchemy exceptions.
"""
if dbName is None:
dbName = conn.engine.url.database
# consider using inspector.get_table_names(). Issue: it needs to connect
# to the database
if conn.engine.url.get_backend_name() == "mysql":
cmd = "SELECT TABLE_NAME FROM information_schema.TABLES "
cmd += "WHERE TABLE_SCHEMA='%s'" % dbName
rows = conn.execute(cmd)
return [x[0] for x in rows]
else:
raise NoSuchModuleError(conn.engine.url.get_backend_name())
def useDb(conn, dbName):
"""
Connect to database <dbName>.
@param conn Database connection or engine.
@param dbName Database name.
Raises NoSuchDatabaseError if the databases does not exists.
Raises sqlalchemy exceptions.
"""
if conn.engine.url.get_backend_name() == "mysql":
try:
conn.execute("USE `%s`" % dbName)
except DBAPIError as e:
if e.orig.args[0] == MySqlErr.ER_BAD_DB_ERROR:
raise NoSuchDatabaseError("USE", dbName, e.orig)
raise
else:
raise NoSuchModuleError(conn.engine.url.get_backend_name())
def createTableFromSchema(conn, schema):
"""
Create database table from given schema.
@param conn Database connection or engine.
@param schema String containing full schema of the table (it can be a dump
containing "CREATE TABLE", "DROP TABLE IF EXISTS", comments, etc.
Raises TableExistsError if the table already exists.
Raises sqlalchemy exceptions.
"""
if conn.engine.url.get_backend_name() == "mysql":
try:
conn.execute(schema)
except OperationalError as exc:
log.error('Exception when creating table: %s', exc)
if exc.orig.args[0] == MySqlErr.ER_TABLE_EXISTS_ERROR:
raise TableExistsError("CREATE TABLE", "<FROM SCHEMA>",
exc.orig)
raise
else:
raise NoSuchModuleError(conn.engine.url.get_backend_name())
def isView(conn, tableName, dbName=None):
"""
Return True if the table <tableName> is a view, False otherwise.
@param tableName Table name.
@param dbName Database name.
@return boolean True if the table is a view. False otherwise.
If <dbName> is not set, the current database name will be used.
Raises sqlalchemy exceptions.
"""
if conn.engine.url.get_backend_name() == "mysql":
dbNameStr = "'%s'" % dbName if dbName is not None else "DATABASE()"
rows = conn.execute("SELECT table_type FROM information_schema.tables "
"WHERE table_schema=%s AND table_name='%s'" %
(dbNameStr, tableName))
row = rows.first()
if not row:
return False
return row[0] == 'VIEW'
else:
raise NoSuchModuleError(conn.engine.url.get_backend_name())
def loadSqlScript(conn, script, dbName=None):
"""
Execute SQL from a given file.
Throws exception in case of any errors.
@param conn Database connection or engine.
@param script File object (object with read() method) or file name.
@param dbName Optional name of the database, if specified then overrides
database used by connection/engine.
"""
url = conn.engine.url
if url.get_backend_name() == "mysql":
# check file, if it has 'read' attribute assume it's a file object,
# otherwise assume it's file name, open it and close later.
cleanup = None
if not hasattr(script, 'read'):
script = open(script)
cleanup = script.close
# write credentials and options to a temporary file, we have to
# close it but will delete it after mysql finishes.
cfg = tempfile.NamedTemporaryFile("w", delete=False)
fname = cfg.name
print("[client]", file=cfg)
print("batch", file=cfg)
print("quick", file=cfg)
# convert to UTF-8 if there are unicode chars, hope mysql can read it
if url.host:
print('host={}'.format(url.host), file=cfg)
if url.port:
print('port={}'.format(url.port), file=cfg)
socket = url.query.get('unix_socket')
if socket:
print('socket="{}"'.format(socket), file=cfg)
if url.username:
print('user="******"'.format(url.username), file=cfg)
if url.password:
print('password="******"'.format(url.password), file=cfg)
if not dbName:
dbName = url.database
if dbName:
print('database={}'.format(dbName), file=cfg)
# not all platforms can read file while it's open
cfg.close()
# build command line, use the options file above
try:
# it will throw on errors
cmd = ['mysql', '--defaults-file=' + fname]
subprocess.check_call(cmd, stdin=script)
finally:
# cleanup - remove file with credentials
os.unlink(fname)
if cleanup is not None:
cleanup()
else:
raise NoSuchModuleError(url.get_backend_name())