Python Forum
Get last row of SQL database and update
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Get last row of SQL database and update
#1
First time working with a SQL database...

I have the following database:

+------------+-------------------+--------------+--------------+-----------------+--------------+------------+
| meeting_id | user_id           | meeting_date | meeting_time | meeting_name    | participants | message_id |
+------------+-------------------+--------------+--------------+-----------------+--------------+------------+
| 1          | 95707125543604224 | 10/12/2021   | 8:00         | Interivew       |              | 8953470    |
+------------+-------------------+--------------+--------------+-----------------+--------------+------------+
| 2          | 95707125543604224 | 10/14/2021   | 12:00        | 1 on 1 w/ John  |              | 5270659    |
+------------+-------------------+--------------+--------------+-----------------+--------------+------------+
| 3          | 75123547643604126 | 12/25/2021   | 13:30        | Christmas Lunch |              | 5468943    |
+------------+-------------------+--------------+--------------+-----------------+--------------+------------+
| 4          | 75123547643604126 | 12/28/2021   | 11:00        | Staff Meeting   |              |            |
+------------+-------------------+--------------+--------------+-----------------+--------------+------------+
I need to update the message_id and participants field once users start accepting the meeting invite. I'm using the following code to grab the last row based on the unique, sequential meeting_id field:

last_meeting = db.execute("SELECT TOP 1 * FROM meetingData ORDER BY meeting_id DESC")
I'm grabbing the last row because this will be executed just after the meeting row is created in the database and passed to another function to modify. The output of last_meeting is:

Output:
<aiosqlite.context.Result object at 0x0000021F037C6F40>
My question is how do I use this information now to update the fields for this row?

Thanks in advanced.
Reply
#2
Try this query: last_meeting = db.execute("SELECT * FROM meetingData ORDER BY meeting_id DESC LIMIT 1;")
Reply
#3
You need to fetch the result, e.g.
print(result.fetchone())
At the moment you print the result

Same apply if you use Larz's SQL statement.
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#4
Thanks for the replies. Below is what I've come up with now however I'm getting an error.

    cursor = db.execute(
        "SELECT * FROM meetingData ORDER BY meeting_id DESC LIMIT 1;")

    meeting_entry = await cursor.fetchone()
    print(meeting_entry[0])
Error:
AttributeError: 'Result' object has no attribute 'fetchone'
I get this error with both my original query and Larz's suggestion.
Reply
#5
@Turtle, I overlooked the fact that you use aiosqlite, which you didn't mention explicitly, but was visible in the output. Anyway, according to docs it should work - there is an example.
Anyway, can you show the full traceback and ideally the full minimal reproducible code.

The strange thing is, that assuming db is Connection object, db.execute() should return Cursor object, which has .fetchone() method. However according to error it's Result object.

This should work, tested with sample дb

import aiosqlite
import asyncio

async def main():
    async with aiosqlite.connect('your_db.sqlite') as db:
        cursor = await db.execute("SELECT * FROM meetingData ORDER BY meeting_id DESC LIMIT 1")
        row = await cursor.fetchone()
        print(row)
        
        # rows = await cursor.fetchall()
        # print(rows)

        # async for row in cursor:
        #     print(row)
asyncio.run(main())
Turtle likes this post
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#6
@buran - Thanks for the reply. Apologies for leaving out the library I was using. I'll be sure to include that in the future as in this case, it was an important factor into the issue.

The issue I was having was that I did not await the db.execute(). Once I added in that await statement, it returned and printed as expected.

Thanks again!
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
Question Python + Google Sheet | Best way to update specific cells in a single Update()? Vokofe 1 2,627 Dec-16-2020, 05:26 AM
Last Post: Vokofe
Photo Update database in tkinter shahulvk 3 3,090 Oct-24-2020, 04:48 PM
Last Post: shahulvk
  How to update sql database from csv Prince_Bhatia 0 2,554 Feb-09-2019, 09:15 PM
Last Post: Prince_Bhatia
  add content of database to text widgte with update atlass218 1 2,553 Jan-06-2019, 02:13 AM
Last Post: woooee
  How to update only two colums in database from csv without touching data in other col Prince_Bhatia 0 2,247 Aug-21-2018, 09:13 AM
Last Post: Prince_Bhatia
  problem with select and update the data into the database chris0147 1 3,152 Aug-18-2017, 09:38 PM
Last Post: chris0147
  Search the data to update in a database chris0147 7 6,647 Oct-27-2016, 03:16 PM
Last Post: Ofnuts
  update the data into the database chris0147 3 6,161 Oct-19-2016, 12:12 AM
Last Post: Yoriz

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020