Uploaded image for project: 'MariaDB Connector/Python'
  1. MariaDB Connector/Python
  2. CONPY-269

mariadb throws a "cursor closed" when inserting using Pandas API

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 1.1.7
    • 1.1.8
    • DBAPI 2.0
    • None
    • Machine: MacBook (Intel)
      OS: Ventura 13.5.1
      Python: 3.10.13

      (relevant) python packages:
      mariadb==1.1.7
      pandas==2.1.0
    • 3.10.13

    Description

      I'm running into a similar issue as outlined here, which was supposedly fixed in this ticket, however I'm still running into the problem

      relevant code I'm using:

      with self.engine.connect() as connection:
          dataframe.to_sql(name="table_name", con=connection, if_exists='append', index=False)
      

      Attachments

        Activity

          marshallm94 Marshall added a comment -

          I have no idea how tickets get tracked/worked on at this organization, so let me know if you want the stacktrace and I can add it

          marshallm94 Marshall added a comment - I have no idea how tickets get tracked/worked on at this organization, so let me know if you want the stacktrace and I can add it
          georg Georg Richter added a comment -

          No need for a stacktrace, I can reproduce the problem now.

          georg Georg Richter added a comment - No need for a stacktrace, I can reproduce the problem now.
          georg Georg Richter added a comment - - edited

          According to PEP-249:

          .close()
          "Close the cursor now (rather than whenever _del_ is called).
          The cursor will be unusable from this point forward; an Error (or subclass) exception will be raised if any operation is attempted with the cursor."

          When debugging your script, it turns out, that SQLAlchemy calls cursor.close() and afterwards wants to access cursor.rowcount while cursor was already closed.

          I would say this is not a bug in Connector/Python, since it's clearly defined that the cursor become unusable after closing it. PyMySQL seems to ignore it:

          >>> import pymysql
          >>> conn=pymysql.connect(user="root")
          >>> cursor=conn.cursor()
          >>> cursor.execute("select 1 union select 2")
          2
          >>> cursor.close()
          >>> cursor.rowcount
          2
          >>> cursor.execute("select 1 union select 2")
          Traceback (most recent call last):
            File "<stdin>", line 1, in <module>
            File "C:\python\python-3.10\lib\site-packages\pymysql\cursors.py", line 148, in execute
              while self.nextset():
            File "C:\python\python-3.10\lib\site-packages\pymysql\cursors.py", line 98, in nextset
              return self._nextset(False)
            File "C:\python\python-3.10\lib\site-packages\pymysql\cursors.py", line 85, in _nextset
              conn = self._get_db()
            File "C:\python\python-3.10\lib\site-packages\pymysql\cursors.py", line 67, in _get_db
              raise err.ProgrammingError("Cursor closed")
          pymysql.err.ProgrammingError: Cursor closed
          
          

          georg Georg Richter added a comment - - edited According to PEP-249: .close() "Close the cursor now (rather than whenever _ del _ is called). The cursor will be unusable from this point forward; an Error (or subclass) exception will be raised if any operation is attempted with the cursor." When debugging your script, it turns out, that SQLAlchemy calls cursor.close() and afterwards wants to access cursor.rowcount while cursor was already closed. I would say this is not a bug in Connector/Python, since it's clearly defined that the cursor become unusable after closing it. PyMySQL seems to ignore it: >>> import pymysql >>> conn = pymysql.connect(user = "root" ) >>> cursor = conn.cursor() >>> cursor.execute( "select 1 union select 2" ) 2 >>> cursor.close() >>> cursor.rowcount 2 >>> cursor.execute( "select 1 union select 2" ) Traceback (most recent call last): File "<stdin>" , line 1 , in <module> File "C:\python\python-3.10\lib\site-packages\pymysql\cursors.py" , line 148 , in execute while self .nextset(): File "C:\python\python-3.10\lib\site-packages\pymysql\cursors.py" , line 98 , in nextset return self ._nextset( False ) File "C:\python\python-3.10\lib\site-packages\pymysql\cursors.py" , line 85 , in _nextset conn = self ._get_db() File "C:\python\python-3.10\lib\site-packages\pymysql\cursors.py" , line 67 , in _get_db raise err.ProgrammingError( "Cursor closed" ) pymysql.err.ProgrammingError: Cursor closed
          georg Georg Richter added a comment - see also https://github.com/sqlalchemy/sqlalchemy/issues/10396
          georg Georg Richter added a comment -

          It's not really a fix but a workaround for pandas:

          Instead of raising an exception when accessing rowcount property after cursor was closed, rowcount now returns -1.

          This should also be in accordance to PEP-249:
          "The attribute is -1 in case no .execute*() has been performed on the cursor or the rowcount of the last operation is cannot be determined by the interface."

          georg Georg Richter added a comment - It's not really a fix but a workaround for pandas: Instead of raising an exception when accessing rowcount property after cursor was closed, rowcount now returns -1. This should also be in accordance to PEP-249 : "The attribute is -1 in case no .execute*() has been performed on the cursor or the rowcount of the last operation is cannot be determined by the interface."
          marshallm94 Marshall added a comment -

          George - I see the fix version is listed as 1.1.8 - has this been released yet? tried to upgrade via pip but that version isn't listed as available

          marshallm94 Marshall added a comment - George - I see the fix version is listed as 1.1.8 - has this been released yet? tried to upgrade via pip but that version isn't listed as available
          georg Georg Richter added a comment -

          I'm just finishing CONPY-270 and CONPY-271, then I will start packaging 1.1.8.

          georg Georg Richter added a comment - I'm just finishing CONPY-270 and CONPY-271 , then I will start packaging 1.1.8.

          People

            georg Georg Richter
            marshallm94 Marshall
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.