def run_sql_file(batch_id, sql_filename):
"""Use the mysql command line to run the given SQL script
batch_id - sql_file is associated with this batch
sql_path - path and file of the sql script
queue - run job on this queue
email - who to email when done
returns the RunBatch.BPJob
"""
cwd = os.path.dirname(__file__)
batch = RunBatch.BPBatch.select(batch_id)
run = RunBatch.BPSQLRun.select_by_sql_filename(batch, sql_filename)
if run is None:
sql_path = os.path.join(RunBatch.batch_script_directory(batch),
sql_filename)
cmd = "%s -b %d -i %s"%(os.path.join(cwd, "sql_jobs.py"),
batch_id, sql_path)
run = RunBatch.BPSQLRun.create(batch, sql_filename, cmd)
return RunBatch.run(batch, [run], cwd=cwd)
re_file_name = re.compile("^(.+?)[0-9]+_[0-9]+_(Image|Object).CSV$")
image_files = []
object_files = []
for file_name in os.listdir(my_batch.data_dir):
match = re_file_name.search(file_name)
if match:
if (image_prefix and match.groups(1)[0] == image_prefix
and match.groups(1)[1] == 'Image'):
image_files.append(file_name)
elif (object_prefix and match.groups(1)[0] == object_prefix
and match.groups(1)[1] == 'Object'):
object_files.append(file_name)
batch_script_file = RunBatch.batch_script_file(sql_script)
batch_script_dir = RunBatch.batch_script_directory(my_batch)
if not os.path.isdir(batch_script_dir):
os.makedirs(batch_script_dir)
batch_script_path = RunBatch.batch_script_path(my_batch, sql_script)
sql_script_file = open(batch_script_path, "w")
try:
sql_script_file.writelines(table_lines)
for file_name in image_files:
path_name = os.path.join(my_batch.data_dir, file_name)
sql_script_file.write(
"""SELECT 'Loading %(file_name)s into %(image_table)s';""" %
(globals()))
sql_script_file.write(
"""LOAD DATA LOCAL INFILE '%(path_name)s' REPLACE INTO TABLE %(image_table)s FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';
SHOW WARNINGS;
""" % (globals()))
re_file_name = re.compile("^(.+?)[0-9]+_[0-9]+_(Image|Object).CSV$")
image_files = []
object_files = []
for file_name in os.listdir(my_batch.data_dir):
match = re_file_name.search(file_name)
if match:
if (image_prefix and match.groups(1)[0] == image_prefix
and match.groups(1)[1] == 'Image'):
image_files.append(file_name)
elif (object_prefix and
match.groups(1)[0] == object_prefix and
match.groups(1)[1] == 'Object'):
object_files.append(file_name)
batch_script_file = RunBatch.batch_script_file(sql_script)
batch_script_dir = RunBatch.batch_script_directory(my_batch)
if not os.path.isdir(batch_script_dir):
os.makedirs(batch_script_dir)
batch_script_path = RunBatch.batch_script_path(my_batch, sql_script)
sql_script_file = open(batch_script_path, "w")
try:
sql_script_file.writelines(table_lines)
for file_name in image_files:
path_name = os.path.join(my_batch.data_dir, file_name)
sql_script_file.write("""SELECT 'Loading %(file_name)s into %(image_table)s';"""%(globals()))
sql_script_file.write("""LOAD DATA LOCAL INFILE '%(path_name)s' REPLACE INTO TABLE %(image_table)s FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';
SHOW WARNINGS;
"""%(globals()))
for file_name in object_files:
path_name = os.path.join(my_batch.data_dir, file_name)
sql_script_file.write("""SELECT 'Loading %(file_name)s into %(object_table)s';"""%(globals()))
