def perftests():
s = 'Source_10030'
o = 'Object_10030'
expid = 6548220209
lim = 10000
cnx = exp.dbconnect()
tablename = o
sql = exp.generateRawSql(tablename)
logtime_data = {}
df = get_dataframe_from_sql(tablename, sql, cnx, log_time=logtime_data, log_name=tablename)
nrows = get_df_count(df, log_time=logtime_data)
csvpath = '/srv/data02/sql_db/sue/tests/'
filename = exp.getFileNamefromTablename(tablename, csvpath)
chunkid = exp.getChunkIDFromTablename(tablename)
sql = exp.generateObjectSql(chunkid, tablename, filename)
logmethodname = 'write_csv_' + tablename
write_csvfile(cnx, sql, tablename, log_time=logtime_data, log_name=logmethodname)
"""
def pandastest():
def main():
# setup
o = 'Object_10030'
s = 'Source_10030'
expid = 6548220209
cnx = exp.dbconnect()
tablename = o
csvpath = '/srv/data02/sql_db/sue/tests/'
if __name__ == "__main__":
# to start python env on windows: & h:/GitHub/jhu-lsst/py/mysql2mssql/env/Scripts/activate.ps1
main()
def export_object(self, chunkid):
exportdir = os.path.join(self.csvpath, self.database, 'Object')
cnx = exp.dbconnect(user=self.user,
password=self.password,
host=self.host,
database=self.database)
stime = datetime.datetime.now()
# check if dir exists
direxists = os.path.isdir(exportdir)
if direxists == False:
try:
os.mkdir(exportdir)
os.chmod(exportdir, 0o777)
print('created dir' + exportdir)
except (OSError):
print("failed to create directory {exportdir}".format(
exportdir=exportdir))
#else:
#print('directory {exportdir} already exists'.format(exportdir=exportdir))
tablename, objfile = exp.generateFileInfo(chunkid, 'Object', exportdir)
# objfile = os.path.join(exportdir, tablename + '.csv')
# check if object file exists
if (os.path.isfile(objfile)) == False:
sql = exp.generateObjectSql(chunkid=chunkid,
tablename=tablename,
dbname=self.database,
filename=objfile)
#write object csv file to directory
otime = exp.executesql(cnx, sql)
print('\t{table} exported in\t{time}'.format(table=tablename,
time=otime))
else:
print('file {file} already exists'.format(file=objfile))
self.skipped.append(objfile)
ftime = datetime.datetime.now()
etime = ftime - stime
def __init__(self, database, basetable=None, debug=False):
self.user = '******'
self.password = '******'
self.host = '127.0.0.1'
self.database = database
self.basetable = basetable
self.csvpath = '/srv/data02/sql_db/sue/csv_exports'
self.object_tables = exp.getTablenamesFromDB(
'Object',
exp.dbconnect(user=self.user,
password=self.password,
host=self.host,
database=self.database))
#self.source_tables = exp.getTablenamesFromDB('Source', exp.dbconnect(user=self.user, password=self.password, host=self.host, database=self.database))
#self.forcedsource_tables = exp.getTablenamesFromDB('ForcedSource', exp.dbconnect(user=self.user, password=self.password, host=self.host, database=self.database))
#self.ofo_tables = exp.getTablenamesFromDB('ObjectFullOverlap', exp.dbconnect(user=self.user, password=self.password, host=self.host, database=self.database))
self.chunk_ids = []
self.skipped = []
self.completed = []
self.export_log = []
self.startime = datetime.datetime.now()
for row in self.object_tables:
table = row[0]
chunkid = exp.getChunkIDFromTablename(table)
self.chunk_ids.append(chunkid)
if debug == True:
self.chunk_ids = self.chunk_ids[:10]
dbdir = os.path.join(self.csvpath, database)
direxists = os.path.isdir(dbdir)
if direxists == False:
try:
os.mkdir(dbdir)
os.chmod(dbdir, 0o777)
print('created dir' + dbdir)
except (OSError):
print("failed to create directory {exportdir}".format(
exportdir=dbdir))
else:
print(
'directory {exportdir} already exists'.format(exportdir=dbdir))
def export_table(self, chunkid):
if self.basetable == None:
print('Error: basetable cannot be null in export_table')
return
basetable = self.basetable
exportdir = os.path.join(self.csvpath, self.database, basetable)
cnx = exp.dbconnect(user=self.user,
password=self.password,
host=self.host,
database=self.database)
stime = datetime.datetime.now()
# check if dir exists
direxists = os.path.isdir(exportdir)
if direxists == False:
try:
os.mkdir(exportdir)
os.chmod(exportdir, 0o777)
print('created dir' + exportdir)
except (OSError):
print("failed to create directory {exportdir}".format(
exportdir=exportdir))
#else:
#print('directory {exportdir} already exists'.format(exportdir=exportdir))
tablename, expfile = exp.generateFileInfo(chunkid, basetable,
exportdir)
# expfile = os.path.join(exportdir, tablename + '.csv')
# check if object file exists
if (os.path.isfile(expfile)) == False:
if (basetable == 'Object'):
sql = exp.generateObjectSql(chunkid=chunkid,
tablename=tablename,
dbname=self.database,
filename=expfile)
elif (basetable == 'Source'):
sql = exp.generateSourceSql(chunkid=chunkid,
tablename=tablename,
dbname=self.database,
filename=expfile)
elif (basetable == 'ForcedSource'):
sql = exp.generateForcedSourceSql(chunkid=chunkid,
tablename=tablename,
dbname=self.database,
filename=expfile)
elif (basetable == 'ObjectFullOverlap'):
sql = exp.generateForcedSourceSql(chunkid=chunkid,
tablename=tablename,
dbname=self.database,
filename=expfile)
else:
print('Base table {basetable} not recognized!'.format(
basetable=basetable))
return
#write object csv file to directory
otime = exp.executesql(cnx, sql)
self.completed.append(expfile)
print('\t{table} exported in\t{time}'.format(table=tablename,
time=otime))
else:
print('file {file} already exists'.format(file=expfile))
self.skipped.append(expfile)
ftime = datetime.datetime.now()
etime = ftime - stime