def getUser(id):
dbconn = getDbConnection()
cursor = dbconn.cursor()
cursor.execute('SELECT id, username, email, name FROM users where id = %s',
(id, ))
result = cursor.fetchone()
return createUserObject(result)
def createUser(user):
dbconn = getDbConnection()
cursor = dbconn.cursor()
postData = validatePostBody(user)
cursor.execute("INSERT INTO users VALUES (%s,%s,%s,%s,%s);", postData)
dbconn.commit()
return createUserObject(postData)
def deleteEvent(eventId):
dbconn = getDbConnection()
cursor = dbconn.cursor()
cursor.execute("DELETE FROM registrations WHERE event_id = %s",
(eventId, ))
cursor.execute("DELETE FROM events WHERE id = %s", (eventId, ))
dbconn.commit()
return {'id': eventId}
def getEvent(eventId):
dbconn = getDbConnection()
cursor = dbconn.cursor()
cursor.execute(
'SELECT events.*, users.id as user_id, users.name as user_name FROM events left join users on (events.author_id = users.id) where events.id = %s',
(eventId, ))
result = cursor.fetchone()
return createEventObject(result)
def getUpcomingEvents():
dbconn = getDbConnection()
cursor = dbconn.cursor()
cursor.execute(
'SELECT events.*, users.id as user_id, users.name as user_name FROM events left join users on (events.author_id = users.id) where time >= now();'
)
result = cursor.fetchall()
return list(map(createEventObject, result))
def getEventRegistrations(eventId):
dbconn = getDbConnection()
cursor = dbconn.cursor()
cursor.execute(
'SELECT users.id, users.username FROM registrations inner join users on (users.id = registrations.user_id) where event_id = %s;',
(eventId, ))
result = cursor.fetchall()
return list(map(createParticipantObject, result))
def getDataFromDatabase(dbConnection=None):
while (dbConnection is None):
companyName = input("What data file do you want to use? \n")
dbConnection = database.getDbConnection(companyName)
if dbConnection is None:
print("No file exists named " + companyName)
data, dates = database.loadDataFromDatabase(dbConnection)
return {'dates': dates, 'data': data, 'dbConnection': dbConnection}
def validateRegistration(eventId, userId):
dbconn = getDbConnection()
cursor = dbconn.cursor()
cursor.execute(
'SELECT count(user_id) FROM registrations where event_id = %s and user_id = %s;',
(
eventId,
userId,
))
result = cursor.fetchone()
return True if result[0] == 0 else False
def loginUser(username, password):
dbconn = getDbConnection()
cursor = dbconn.cursor()
cursor.execute('SELECT id, username, password_salt FROM users where username = %s;', (username,))
id, username, password_salt = cursor.fetchone()
if id is None:
return None
if bcrypt.checkpw(password.encode('utf8'), password_salt.encode('utf8')):
token = jwt.encode({'user_id': id}, os.environ['JWT_SECRET'], algorithm='HS256')
return {'access_token': token.decode('utf8')}
else:
return None
def createEvent(event, userId):
dbconn = getDbConnection()
cursor = dbconn.cursor()
postData = validatePostBody(event, userId)
cursor.execute("INSERT INTO events VALUES (%s,%s,%s,%s,%s,%s,%s,%s);",
postData)
dbconn.commit()
userres = user_service.getUser(userId)
return createEventObject(postData + (
userres['id'],
userres['name'],
), True)
def createEventRegistration(eventId, userId):
validation = validateRegistration(eventId, userId)
if validation is False:
return None
dbconn = getDbConnection()
cursor = dbconn.cursor()
cursor.execute('INSERT INTO registrations VALUES (%s, %s);', (
userId,
eventId,
))
dbconn.commit()
return eventId
def updateEvent(event, userId):
dbconn = getDbConnection()
cursor = dbconn.cursor()
postData = validatePostBody(event, userId, True)
cursor.execute(
"UPDATE events SET id = %s, name = %s, description = %s, registration_start = %s, registration_end = %s, time = %s, max_participants = %s, author_id = %s where id = %s;",
postData + (event['id'], ))
dbconn.commit()
userres = user_service.getUser(userId)
return createEventObject(postData + (
userres['id'],
userres['name'],
), True)
def main(argv):
parser = argparse.ArgumentParser(
description=
'Get the download_file_paths (http mousephenotype uris) from the experiment core and then downloads the images'
)
parser.add_argument(
'-d',
'--rootDestinationDir',
dest='rootDestinationDir',
help='Directory for root of destination to store images')
parser.add_argument('-s',
'--rootSolrUrl',
dest='rootSolrUrl',
help='URL to root of solr index')
parser.add_argument('-H',
'--host',
dest='komp2Host',
help='Hostname for server hosting komp2 db')
parser.add_argument('-p',
'--port',
dest='komp2Port',
help='Port by which to connect to komp2 db')
parser.add_argument('-u',
'--user',
dest='komp2User',
help='Username for connecting to komp2 db')
parser.add_argument('-db',
'--database',
dest='komp2Db',
help='Database to connect to for komp2db')
parser.add_argument('--pass',
dest='komp2Pass',
help='Password for komp2db')
parser.add_argument(
'--profile',
dest='profile',
default='dev',
help='profile from which to read config: dev, prod, live, ...')
args = parser.parse_args()
# Get values from property file and use as defaults that can be overridden
# by command line parameters
try:
pp = OmeroPropertiesParser(args.profile)
omeroProps = pp.getOmeroProps()
except:
omeroProps = {}
rootSolrUrl = args.rootSolrUrl if args.rootSolrUrl <> None else omeroProps[
'solrurl']
komp2Host = args.komp2Host if args.komp2Host <> None else omeroProps[
'komp2host']
komp2Port = args.komp2Port if args.komp2Port <> None else omeroProps[
'komp2port']
komp2db = args.komp2Db if args.komp2Db <> None else omeroProps['komp2db']
komp2User = args.komp2User if args.komp2User <> None else omeroProps[
'komp2user']
komp2Pass = args.komp2Pass if args.komp2Pass <> None else omeroProps[
'komp2pass']
rootDestinationDir = args.rootDestinationDir if args.rootDestinationDir <> None else omeroProps[
'rootdestinationdir']
#note cant split this url over a few lines as puts in newlines into url which doesn't work
#solrQuery="""experiment/select?q=observation_type:image_record&fq=download_file_path:(download_file_path:*bhjlk01.jax.org/images/IMPC_ALZ_001/*%20AND%20!download_file_path:*.mov)&fl=id,download_file_path,phenotyping_center,pipeline_stable_id,procedure_stable_id,datasource_name,parameter_stable_id&wt=json&indent=on&rows=10000000"""
solrQuery = """experiment/select?q=observation_type:image_record&fq=(download_file_path:*mousephenotype.org*%20AND%20!download_file_path:*.mov)&fl=id,download_file_path,phenotyping_center,pipeline_stable_id,procedure_stable_id,datasource_name,parameter_stable_id&wt=json&indent=on&rows=10000000"""
print("running python image copy script for impc images")
print 'rootDestinationDir is "', rootDestinationDir
solrUrl = rootSolrUrl + solrQuery
print 'solrUrl', solrUrl
cnx = getDbConnection(komp2Host, komp2Port, komp2db, komp2User, komp2Pass)
runWithSolrAsDataSource(solrUrl, cnx, rootDestinationDir)
def main(argv):
print "running main method of get_omero_ids - using postgresQL directly!!"
parser = argparse.ArgumentParser(
description='Populate omero_ids into the komp2 image_record_observation table so we can then index them with pure java from the database and solr experiment index. This version uses postgresQl directly'
)
parser.add_argument('-H', '--host', dest='komp2Host',
help='Hostname for server hosting komp2 db'
)
parser.add_argument('-p', '--port', dest='komp2Port',
help='Port by which to connect to komp2 db'
)
parser.add_argument('-u', '--user', dest='komp2User',
help='Username for connecting to komp2 db'
)
parser.add_argument('-db', '--database', dest='komp2Db',
help='Database to connect to for komp2db'
)
parser.add_argument('--pass', dest='komp2Pass',
help='Password for komp2db'
)
parser.add_argument('--omeroDbUser', dest='omeroDbUser',
help='name of the omero postgres database')
parser.add_argument('--omeroDbPass', dest='omeroDbPass',
help='Password for the omero postgress database')
parser.add_argument('--omeroDbName', dest='omeroDbName',
help='Name of the postgres database omero uses')
parser.add_argument('--omeroDbHost', dest='omeroDbHost',
help='Hostname for the server hosting the omero postgres database')
parser.add_argument('--omeroDbPort', dest='omeroDbPort',
help='Port to connect on the postgres server hosting the omero database')
parser.add_argument('--profile', dest='profile', default='dev',
help='Name of profile from which to read config: ' + \
'dev, prod, live, ... Assumed to be present ' + \
'in configfiles/profilename/application.properties'
)
parser.add_argument('--profile-path', dest='profilePath',
help='Explicit path to file from which to read ' + \
'profile e.g. ' + \
'/home/kola/configfiles/dev/application.properties'
)
args = parser.parse_args()
# Get values from property file and use as defaults that can be overridden
# by command line parameters
if args.profilePath is not None:
try:
pp = OmeroPropertiesParser()
omeroProps = pp.getOmeroProps(args.profilePath)
except Exception as e:
print "Could not read application properties file from " + args.profilePath
print "Error was: " + str(e)
return
else:
try:
pp = OmeroPropertiesParser(args.profile)
omeroProps = pp.getOmeroProps()
except Exception as e:
print "Could not read application properties file for profile " + args.profile
print "Error was: " + str(e)
return
komp2Host = args.komp2Host if args.komp2Host<>None else omeroProps['komp2host']
print "setting komp2Host="+komp2Host
komp2Port = args.komp2Port if args.komp2Port<>None else omeroProps['komp2port']
print 'setting komp2Port='+komp2Port
komp2db = args.komp2Db if args.komp2Db<>None else omeroProps['komp2db']
print 'setting komp2db='+komp2db
komp2User = args.komp2User if args.komp2User<>None else omeroProps['komp2user']
komp2Pass = args.komp2Pass if args.komp2Pass<>None else omeroProps['komp2pass']
global loadedCount
loadedCount=0
print "about to run getdb with arguments komp2db="+komp2db
dbConn=getDbConnection(komp2Host, komp2Port, komp2db, komp2User, komp2Pass)
#cnx=getDbConnection(komp2Host, komp2Port, komp2db, komp2User, komp2Pass)
# Get Postgres connection for directly querying omero database
try:
print "Attempting to connect directly to Postgres DB"
omeroDbUser = args.omeroDbUser if args.omeroDbUser is not None else omeroProps['omerodbuser']
omeroDbPass = args.omeroDbPass if args.omeroDbPass is not None else omeroProps['omerodbpass']
omeroDbName = args.omeroDbName if args.omeroDbName is not None else omeroProps['omerodbname']
omeroDbHost = args.omeroDbHost if args.omeroDbHost is not None else omeroProps['omerodbhost']
if args.omeroDbPort is not None:
omeroDbPort = args.omeroDbPort
elif 'omerodbport' in omeroProps:
omeroDbPort = omeroProps['omerodbport']
else:
omeroDbPort = '5432'
psqlConn = psycopg2.connect(database=omeroDbName, user=omeroDbUser,
password=omeroDbPass, host=omeroDbHost,
port=omeroDbPort)
print "Connected to Postgres DB"
except KeyError as e:
print "Could not connect to omero postgres database. Key " + str(e) + \
" not present in omero properties file. Aborting!"
sys.exit()
except Exception as e:
print "Could not connect to omero postgres database. Error: " + str(e)
sys.exit()
getOmeroIdsAndPaths(dbConn, psqlConn)
dbConn.close()
psqlConn.close()
def main(argv):
print "running main method of get_omero_ids - using postgresQL directly!!"
parser = argparse.ArgumentParser(
description=
'Populate omero_ids into the komp2 image_record_observation table so we can then index them with pure java from the database and solr experiment index. This version uses postgresQl directly'
)
parser.add_argument('-H',
'--host',
dest='komp2Host',
help='Hostname for server hosting komp2 db')
parser.add_argument('-p',
'--port',
dest='komp2Port',
help='Port by which to connect to komp2 db')
parser.add_argument('-u',
'--user',
dest='komp2User',
help='Username for connecting to komp2 db')
parser.add_argument('-db',
'--database',
dest='komp2Db',
help='Database to connect to for komp2db')
parser.add_argument('--pass',
dest='komp2Pass',
help='Password for komp2db')
parser.add_argument('--omeroDbUser',
dest='omeroDbUser',
help='name of the omero postgres database')
parser.add_argument('--omeroDbPass',
dest='omeroDbPass',
help='Password for the omero postgress database')
parser.add_argument('--omeroDbName',
dest='omeroDbName',
help='Name of the postgres database omero uses')
parser.add_argument(
'--omeroDbHost',
dest='omeroDbHost',
help='Hostname for the server hosting the omero postgres database')
parser.add_argument(
'--omeroDbPort',
dest='omeroDbPort',
help='Port to connect on the postgres server hosting the omero database'
)
parser.add_argument('--profile', dest='profile', default='dev',
help='Name of profile from which to read config: ' + \
'dev, prod, live, ... Assumed to be present ' + \
'in configfiles/profilename/application.properties'
)
parser.add_argument('--profile-path', dest='profilePath',
help='Explicit path to file from which to read ' + \
'profile e.g. ' + \
'/home/kola/configfiles/dev/application.properties'
)
args = parser.parse_args()
# Get values from property file and use as defaults that can be overridden
# by command line parameters
if args.profilePath is not None:
try:
pp = OmeroPropertiesParser()
omeroProps = pp.getOmeroProps(args.profilePath)
except Exception as e:
print "Could not read application properties file from " + args.profilePath
print "Error was: " + str(e)
return
else:
try:
pp = OmeroPropertiesParser(args.profile)
omeroProps = pp.getOmeroProps()
except Exception as e:
print "Could not read application properties file for profile " + args.profile
print "Error was: " + str(e)
return
komp2Host = args.komp2Host if args.komp2Host <> None else omeroProps[
'komp2host']
print "setting komp2Host=" + komp2Host
komp2Port = args.komp2Port if args.komp2Port <> None else omeroProps[
'komp2port']
print 'setting komp2Port=' + komp2Port
komp2db = args.komp2Db if args.komp2Db <> None else omeroProps['komp2db']
print 'setting komp2db=' + komp2db
komp2User = args.komp2User if args.komp2User <> None else omeroProps[
'komp2user']
komp2Pass = args.komp2Pass if args.komp2Pass <> None else omeroProps[
'komp2pass']
global loadedCount
loadedCount = 0
print "about to run getdb with arguments komp2db=" + komp2db
dbConn = getDbConnection(komp2Host, komp2Port, komp2db, komp2User,
komp2Pass)
#cnx=getDbConnection(komp2Host, komp2Port, komp2db, komp2User, komp2Pass)
# Get Postgres connection for directly querying omero database
try:
print "Attempting to connect directly to Postgres DB"
omeroDbUser = args.omeroDbUser if args.omeroDbUser is not None else omeroProps[
'omerodbuser']
omeroDbPass = args.omeroDbPass if args.omeroDbPass is not None else omeroProps[
'omerodbpass']
omeroDbName = args.omeroDbName if args.omeroDbName is not None else omeroProps[
'omerodbname']
omeroDbHost = args.omeroDbHost if args.omeroDbHost is not None else omeroProps[
'omerodbhost']
if args.omeroDbPort is not None:
omeroDbPort = args.omeroDbPort
elif 'omerodbport' in omeroProps:
omeroDbPort = omeroProps['omerodbport']
else:
omeroDbPort = '5432'
psqlConn = psycopg2.connect(database=omeroDbName,
user=omeroDbUser,
password=omeroDbPass,
host=omeroDbHost,
port=omeroDbPort)
print "Connected to Postgres DB"
except KeyError as e:
print "Could not connect to omero postgres database. Key " + str(e) + \
" not present in omero properties file. Aborting!"
sys.exit()
except Exception as e:
print "Could not connect to omero postgres database. Error: " + str(e)
sys.exit()
getOmeroIdsAndPaths(dbConn, psqlConn)
dbConn.close()
psqlConn.close()