def open_connection_and_fetchall(
host=None, user=None, passwd=None, db=None, query=None):
with pymysql_connection(host=host, username=user, password=passwd, database=db) as cur:
cur.execute(query)
results = cur.fetchall()
return results
def fetch_collections_from_edinum():
COLLECTIONS_QUERY = "SELECT id, Name from Collection;"
edinum = settings.EXTERNAL_DATABASES["edinum"]
with pymysql_connection(
host=edinum["HOST"], username=edinum["USER"], password=edinum["PASSWORD"], database=edinum["NAME"]
) as cur:
cur.execute(COLLECTIONS_QUERY)
return cur.fetchall()
def fetch_publisher_journals_from_edinum():
PUBLISHER_JOURNALS_QUERY = """SELECT c.PersonID, t.id as journal_id
FROM edinum.contributionseries cs
JOIN title t ON cs.SeriesID = t.SeriesID
JOIN contribution c ON cs.ContributionID = c.ID
WHERE ContributiontypeID = '3';"""
edinum = settings.EXTERNAL_DATABASES["edinum"]
with pymysql_connection(
host=edinum["HOST"], username=edinum["USER"], password=edinum["PASSWORD"], database=edinum["NAME"]
) as cur:
cur.execute(PUBLISHER_JOURNALS_QUERY)
return cur.fetchall()
def get_user_from_mandragore(username):
MANDRAGORE_USER_QUERY = """
SELECT NomUtilisateur, MotDePasse FROM CompteUtilisateur WHERE NomUtilisateur="{}"
"""
mandragore = settings.EXTERNAL_DATABASES['mandragore']
with pymysql_connection(
host=mandragore['HOST'],
username=mandragore['USER'],
password=mandragore['PASSWORD'],
database=mandragore['NAME']
) as cur:
cur.execute(MANDRAGORE_USER_QUERY.format(username))
users = cur.fetchall()
return users[0]
def update_user_password(username, the_hash):
""" Update the password of the user in mandragore """
MANDRAGORE_UPDATE_QUERY = """
UPDATE CompteUtilisateur set MotDePasse="{}" WHERE NomUtilisateur="{}"
"""
mandragore = settings.EXTERNAL_DATABASES['mandragore']
with pymysql_connection(
host=mandragore['HOST'],
username=mandragore['USER'],
password=mandragore['PASSWORD'],
database=mandragore['NAME']
) as cur:
cur.execute(MANDRAGORE_UPDATE_QUERY.format(
the_hash,
username
))
def fetch_accounts_from_mandragore():
MANDRAGORE_ACCOUNT_QUERY = """
SELECT cu.NomUtilisateur, pc.Adresse, cu.PersonneId, cft.CollectionID FROM
CompteUtilisateur cu,
PersonneCourriel pc,
CompteUtilisateurCollectionFluxTravaux cucft,
CollectionFluxTravaux cft WHERE
cucft.NomUtilisateur = cu.NomUtilisateur AND
pc.PersonneID = cu.PersonneId AND
cucft.CollectionFluxTravauxID = cft.ID;
"""
mandragore = settings.EXTERNAL_DATABASES['mandragore']
with pymysql_connection(
host=mandragore['HOST'],
username=mandragore['USER'],
password=mandragore['PASSWORD'],
database=mandragore['NAME']
) as cur:
cur.execute(MANDRAGORE_ACCOUNT_QUERY)
return cur.fetchall()