aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorBonfaceKilz2021-06-03 21:38:58 +0300
committerBonfaceKilz2021-06-03 21:58:31 +0300
commitd0042a3cd95d164468a69ab17ee0c3adba5ea296 (patch)
tree1e83ff75290488937a7c670c897c0b194bbfaa34
parent6d366086c0361c3f89d066cec78c5a69de9be155 (diff)
downloadgenenetwork3-d0042a3cd95d164468a69ab17ee0c3adba5ea296.tar.gz
Use prepared statements for UPDATE sql function
-rw-r--r--gn3/db/__init__.py16
-rw-r--r--tests/unit/db/test_phenotypes.py10
2 files changed, 13 insertions, 13 deletions
diff --git a/gn3/db/__init__.py b/gn3/db/__init__.py
index 8b6bf73..ce92a7d 100644
--- a/gn3/db/__init__.py
+++ b/gn3/db/__init__.py
@@ -43,18 +43,20 @@ def update(conn: Any,
"""Run an UPDATE on a table"""
if not (any(astuple(data)) and any(astuple(where))):
return None
+ data_ = {k: v for k, v in asdict(data).items()
+ if v is not None and k in TABLEMAP[table]}
+ where_ = {k: v for k, v in asdict(where).items()
+ if v is not None and k in TABLEMAP[table]}
sql = f"UPDATE {table} SET "
sql += ", ".join(f"{TABLEMAP[table].get(k)} "
- f"= '{escape_string(str(v)).decode('utf-8')}'" for
- k, v in asdict(data).items()
- if v is not None and k in TABLEMAP[table])
+ "= %s" for k in data_.keys())
sql += " WHERE "
sql += " AND ".join(f"{TABLEMAP[table].get(k)} = "
- f"'{escape_string(str(v)).decode('utf-8')}'" for
- k, v in asdict(where).items()
- if v is not None and k in TABLEMAP[table])
+ "%s" for k in where_.keys())
with conn.cursor() as cursor:
- cursor.execute(sql)
+ cursor.execute(sql,
+ tuple(data_.values()) + tuple(where_.values()))
+ conn.commit()
return cursor.rowcount
diff --git a/tests/unit/db/test_phenotypes.py b/tests/unit/db/test_phenotypes.py
index fdeca5e..21eb757 100644
--- a/tests/unit/db/test_phenotypes.py
+++ b/tests/unit/db/test_phenotypes.py
@@ -37,12 +37,10 @@ class TestPhenotypes(TestCase):
where=Phenotype(id_=1, owner="Rob")), 1)
cursor.execute.assert_called_once_with(
"UPDATE Phenotype SET "
- "Pre_publication_description = "
- "'Test Pre Pub', "
- "Post_publication_description = "
- "'Test Post Pub', Submitter = 'Rob' "
- "WHERE id = '1' AND Owner = 'Rob'"
- )
+ "Pre_publication_description = %s, "
+ "Post_publication_description = %s, "
+ "Submitter = %s WHERE id = %s AND Owner = %s",
+ ('Test Pre Pub', 'Test Post Pub', 'Rob', 1, 'Rob'))
def test_fetch_phenotype(self):
"""Test that a single phenotype is fetched properly