Repository: ClearcodeHQ/pytest-mysql (language: python)

Issue:
PyMYSQL support
### What action do you want to perform

We use PyMYSQL instead of mysqlclient. Is there a way to make mysqlclient optional as a dependency?

Also, would it be possible to add support in unit tests?

### What are the results


### What are the expected results


Discussion / hints:
1. Technically speaking, you can use both in your project, unless there's some issue that prevents you from installing mysqlclient as well?

The client library you use for code does not has to be the same test library uses to set up fixture data and read results. I usually encourage to not mix clients/sessions/connections between fixture/test code and the code being tested.

2. What do you mean support in unit tests?
The current repo that I've been plumped into depends on PyMySQL and our unittests use sqlite, so I've been looking to potentially migrate to using MySQL for our test suite.

> I usually encourage to not mix clients/sessions/connections between fixture/test code and the code being tested.

Makes sense. Not sure if that's viable in my case (and honestly, not sure why lol), though internally it looks to be because we bar c extensions.

> What do you mean support in unit tests?

tl;dr ignore the comment: sorry, after forking the repo, I discovered that the package actually depended on mysqlclient directly, which I didn't know about when writing the first sentence. Had mysqlclient not actually be directly depended on by the package's code, I assumed the test matrix could easily just add pymysql in. Turns out, that's false.
Technically speaking it would be possible, I had, for the time being, such a layer for pytest-postgresql, but there were only variants of psycopg2, which had same API.

Seeing as both libraries are in the same pymysql gothub org, I wonder if I shouldn't replace mysqlclient with pymysql. Especially that it's test library, not production, so speed is not that much of an issue, pymysql seems to be more popular 🤔 

Fix patch:
diff --git a/Pipfile b/Pipfile
index 123badd..bde19a7 100644
--- a/Pipfile
+++ b/Pipfile
@@ -7,8 +7,8 @@ name = "pypi"
 pytest = "==7.4.3"
 port-for = "==0.7.2"
 mirakuru = "==2.5.2"
-mysqlclient = "==2.2.1"
 packaging = "==23.2"
+pymysql = "==1.1.0"
 
 [dev-packages]
 towncrier = "==23.11.0"
@@ -19,5 +19,5 @@ mock = "==5.1.0"
 coverage = "==7.3.4"
 tbump = "==6.11.0"
 mypy = "==1.7.1"
-types-mysqlclient = "==2.2.0.1"
 ruff = "==0.1.8"
+types-pymysql = "==1.1.0.20240425"
diff --git a/newsfragments/491.break.rst b/newsfragments/491.break.rst
new file mode 100644
index 0000000..02d48c1
--- /dev/null
+++ b/newsfragments/491.break.rst
@@ -0,0 +1,5 @@
+Replace mysqlclient with pymysql library.
+
+Installation of mysqlclient became more and more problematic on macosx which in turn proved to be hard to maintain on github-actions.
+
+PyMySQL is mostly API compatible so pytest-mysql usage is just changing import location with one exception for poorly documented client fixture closeup.
\ No newline at end of file


Test patch:
diff --git a/pytest_mysql/factories/client.py b/pytest_mysql/factories/client.py
index 0c915f8..f663927 100644
--- a/pytest_mysql/factories/client.py
+++ b/pytest_mysql/factories/client.py
@@ -18,10 +18,9 @@
 """Client fixture factory for MySQL database."""
 from typing import Any, Callable, Dict, Generator, Optional, Union
 
-import MySQLdb
 import pytest
 from _pytest.fixtures import FixtureRequest
-from MySQLdb import Connection, OperationalError, ProgrammingError
+from pymysql import Connection, OperationalError, ProgrammingError
 
 from pytest_mysql.config import get_config
 from pytest_mysql.exceptions import DatabaseExists
@@ -57,9 +56,9 @@ def mysql(
     :rtype: func
     """
 
-    def _connect(connect_kwargs: dict, query_str: str, mysql_db: str) -> MySQLdb.Connection:
+    def _connect(connect_kwargs: Dict[str, Any], query_str: str, mysql_db: str) -> Connection:
         """Apply given query to a  given MySQLdb connection."""
-        mysql_conn: MySQLdb.Connection = Connection(**connect_kwargs)
+        mysql_conn = Connection(**connect_kwargs)
         try:
             mysql_conn.query(query_str)
         except ProgrammingError as e:
@@ -77,7 +76,7 @@ def _connect(connect_kwargs: dict, query_str: str, mysql_db: str) -> MySQLdb.Con
     @pytest.fixture
     def mysql_fixture(
         request: FixtureRequest,
-    ) -> Generator[MySQLdb.Connection, None, None]:
+    ) -> Generator[Connection, None, None]:
         """Client fixture for MySQL server.
 
         #. Get config.
@@ -102,7 +101,7 @@ def mysql_fixture(
         mysql_passwd = passwd or config["passwd"]
         mysql_db = dbname or config["dbname"]
 
-        connection_kwargs: Dict[str, Union[str, int]] = {
+        connection_kwargs: Dict[str, Any] = {
             "host": process.host,
             "user": mysql_user,
             "passwd": mysql_passwd,
@@ -118,7 +117,7 @@ def mysql_fixture(
             f"DEFAULT COLLATE {collation}"
         )
         try:
-            mysql_conn: MySQLdb.Connection = _connect(connection_kwargs, query_str, mysql_db)
+            mysql_conn: Connection = _connect(connection_kwargs, query_str, mysql_db)
         except OperationalError:
             # Fallback to mysql connection with root user
             connection_kwargs["user"] = "root"
@@ -130,11 +129,12 @@ def mysql_fixture(
         if not mysql_conn.open:
             mysql_conn = Connection(**connection_kwargs)
         try:
-            mysql_conn.store_result()  # type: ignore
+            with mysql_conn.cursor() as cursor:
+                cursor.fetchall()
         except Exception as e:
             print(str(e))
         query_drop_database = f"DROP DATABASE IF EXISTS `{mysql_db}`"
         mysql_conn.query(query_drop_database)
-        mysql_conn.close()  # type: ignore
+        mysql_conn.close()
 
     return mysql_fixture
diff --git a/tests/test_mysql.py b/tests/test_mysql.py
index acd2639..177e629 100644
--- a/tests/test_mysql.py
+++ b/tests/test_mysql.py
@@ -1,5 +1,5 @@
 """Actual tests for pytest-mysql."""
-from MySQLdb import Connection
+from pymysql import Connection
 
 from pytest_mysql.executor import MySQLExecutor
 
@@ -16,7 +16,7 @@ def test_mysql(mysql: Connection) -> None:
     """Check first, basic client fixture factory."""
     cursor = mysql.cursor()
     cursor.execute(QUERY)
-    mysql.commit()  # type: ignore
+    mysql.commit()
     cursor.close()
 
 
@@ -29,12 +29,12 @@ def test_mysql_newfixture(mysql: Connection, mysql2: Connection) -> None:
     """More complext test with several mysql_processes."""
     cursor = mysql.cursor()
     cursor.execute(QUERY)
-    mysql.commit()  # type: ignore
+    mysql.commit()
     cursor.close()
 
     cursor = mysql2.cursor()
     cursor.execute(QUERY)
-    mysql2.commit()  # type: ignore
+    mysql2.commit()
     cursor.close()
 
 
diff --git a/tests/test_mysqlnoproc.py b/tests/test_mysqlnoproc.py
index 739b5b0..c1bf26e 100644
--- a/tests/test_mysqlnoproc.py
+++ b/tests/test_mysqlnoproc.py
@@ -1,5 +1,5 @@
 """MySQL tests that do not start mysql server."""
-from MySQLdb import Connection
+from pymysql import Connection
 
 from pytest_mysql import factories
 from tests.test_mysql import QUERY
@@ -12,7 +12,7 @@ def test_mysql_noproc(mysqlnoproc_client: Connection) -> None:
     """Check if noproc fixture connects to the running mysql instance."""
     cursor = mysqlnoproc_client.cursor()
     cursor.execute(QUERY)
-    mysqlnoproc_client.commit()  # type: ignore
+    mysqlnoproc_client.commit()
     cursor.close()
 
 
@@ -25,6 +25,6 @@ def test_mysql_noproc_closing_connection_not_throwing_exception(
     """
     cursor = mysqlnoproc_client.cursor()
     cursor.execute(QUERY)
-    mysqlnoproc_client.commit()  # type: ignore
+    mysqlnoproc_client.commit()
     cursor.close()
-    mysqlnoproc_client.close()  # type: ignore
+    mysqlnoproc_client.close()