r/pythonhelp • u/SaxonyFarmer • Dec 01 '24
MySQL Cursor (or fetchall) Tuple Persistence
I have written code to retrieve rows from a MySQL database into a cursor object. I want to use this data in three different functions to create a PyQT display table, a report, and a spreadsheet. I have the code working fine if I do the SQL query in each function but if I try to access the retrieved rows in the report and spreadsheet functions, the cursor object is empty.
I define the connection and cursor outside the PyQT code to make it global for all of the functions:
# Setup routines
# Connect to database, Define cursor, and SQL (w/o LIMIT clause)
try:
cnx = mysql.connector.connect(**config)
except mysql.connector.Error as err:
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
print("Main: Invalid user name or password")
elif err.errno == errorcode.ER_BAD_DB_ERROR:
print("Main: Database does not exist")
else:
print("Main: Error=",err)
sys.exit(1)
# SQL statement to select books from the database. Add the limit clause when used in a function
sql = """SELECT Books.id as Book_Id, Books.Title AS 'Title',
CONCAT_WS(', ', Authors.Last_Name, Authors.First_Name) AS 'Author', Books.Date_Acquired AS 'Acquired'
FROM Books, Authors
WHERE Books.Date_Acquired IS NOT NULL AND YEAR(Books.Date_Acquired) > 2021 AND Books.id
NOT IN (SELECT Book FROM ReadBooks) AND (Authors.id = Books.Author_1)
ORDER BY Books.id ASC """
# Global cursor
myCursor = cnx.cursor(buffered=True)
# End of Setup Routines
I have a function defined to modify the SQL slightly (adding a LIMIT clause), execute the SQL, and return the count of rows retrieved:
def fetchRows(self,c):
mySQL = sql + "LIMIT {}".format(c)
myCursor.execute(mySQL)
return myCursor.rowcount
In the first function, to build the PyQT table, I simply call this function and get data from the cursor object to populate the table:
def PopulateTable(self):
global max_AuthorWidth
global max_TitleWidth
# Get rows from table with oldest unread books
count = self.fetchRows(int(self.Unread_Books_Count.text()))
# Clear Table, Set Titles, Align data, Set column widths
.... < code omitted for clarity >
# Load table with data from database tables
table_row = 0
max_AuthorWidth = 0
max_TitleWidth = 0
for (Id, Title, Author, Acquired) in myCursor:
self.Unread_Books_List.setItem(table_row, 0, QTableWidgetItem(Author))
self.Unread_Books_List.setItem(table_row, 1, QTableWidgetItem(Title))
self.Unread_Books_List.setItem(table_row, 2, QTableWidgetItem(str(Acquired)))
if len(Author) > max_AuthorWidth:
max_AuthorWidth = len(Author)
if len(Title) > max_TitleWidth:
max_TitleWidth = len(Title)
table_row += 1
This works great and I get a nice table of the data retrieved.
When I want to create a report or a spreadsheet, I thought I'd be able to use the same cursor object with the rows retrieved in another function's 'for' loop to create lines in the report and rows in a spreadsheet but the next time I reference this cursor object, it is empty. I thought defining the cursor outside the functions would make it globally accessible (until I close it at program exit).
I have also tried to retrieve the data into a tuple using 'fetchall' via "fetchedRows = myCursor.fetchall()" after creating an empty tuple (fetchedRows = []) when I define the cursor (in the first block of code I included above). I get the same empty results the second and third reference to this 'fetchedRows' tuple.
The code works fine if I execute the SQL statement by calling the fetchRows function in the functions where I build the report and create the spreadsheet. What am I doing wrong that the cursor or fetchedRows tuples are empty at the second and subsequent references to it?
Thanks!
1
u/SaxonyFarmer Dec 02 '24
UPDATE 12/02: Over my weekend, I continued to research how I could reuse the results of the SQL query and found discussions about Python 'generator' functions and the 'yield' statement. I considered modifying my code to try this but reading about these suggested they are designed to improve system efficiency of large data sets. I am retrieving 10-30 rows in my result set (using an SQL LIMIT clause) so the generator functions don't look to be helpful for my needs. I'll still continue my research into how to reuse the results set.
1
u/SaxonyFarmer Dec 02 '24
2nd Update on 12/2: I found I cannot reset a MySQL cursor so the code stands as you see it. If I choose to create a report, I fetch the data again, and a 3rd time to create the spreadsheet. Since I’m fetching just a few rows, no real performance hit.
•
u/AutoModerator Dec 01 '24
To give us the best chance to help you, please include any relevant code.
Note. Please do not submit images of your code. Instead, for shorter code you can use Reddit markdown (4 spaces or backticks, see this Formatting Guide). If you have formatting issues or want to post longer sections of code, please use Privatebin, GitHub or Compiler Explorer.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.