Python Database Cursors (& MySQL)

[Olaf van der Spek]

Ik heb ervaring met de C en PHP interfaces voor MySQL maar struikel nu een beetje over de Python interface en dan met name over cursors.

Wat is een cursor nu precies en wat heb ik er aan?
Ik heb bijna het idee dat het cursor concept in Python gewoon niet goed is ontworpen. Ik had verwacht dat execute iets van een result terug zou geven maar de link loopt via de cursor.

Python:

import mysql.connector

cnx = mysql.connector.connect()

# Only this particular cursor will buffer results
cursor = cnx.cursor(buffered=True)

# All cursors created from cnx2 will be buffered by default
cnx2 = mysql.connector.connect(buffered=True)

cursor.execute("select id, latitude, longitude from ...")
for (id, lat, lon) in cursor :
    cursor.execute("update ...")

Kan dit als cursor unbuffered is?
Kan het als cursor buffered is of heb ik dan een tweede cursor nodig?

Of is het handiger iets anders dan mysql.connector te gebruiken?

[ Voor 3% gewijzigd door Olaf van der Spek op 02-11-2017 12:19 ]

[The Eagle]

Wikipedia: Cursor (databases)
Wat jij er aan hebt is dat de db een complete resultset teruggeeft ipv een rij voor rij output (sql standaard). Ergo: db ff laten stampen en de gehele outputsetin een array plempen voor verdere verwerking.

Of, voor bewerking, een snellere verwerking van de set ipv losse updates per rij.

Ik ken de mysql implementatie van een cursor niet maar ik verwacht dat die buffer setting een stukje geheugen is dat in de vonnector gereserveerd wordt voor de teruggave van de resultset.

Dus de vraag is: wat probeer je te doen (opvragen of bewerken) en om hoeveel rijen gaat het? Bij veel rijen heb je wellicht iets aan een cursor. But your mileage may vary.

[Olaf van der Spek]

Het verschil tussen buffered en unbuffered ken ik, beiden zijn natuurlijk ook beschikbaar in de C interface maar daar wordt de term cursor niet gebruikt.
Een cursor lijkt een soort pre-allocated resultset maar daardoor is de interface volgens mij veel complexer dan nodig.

[gekkie]

In theorie voldoen de meeste python DB modules aan de Python Database API Specification
Grote kans dus dat je met een python abstractie van het concept database cursor zit te werken.

[The Eagle]

Olaf van der Spek schreef op zaterdag 4 november 2017 @ 11:00:
Het verschil tussen buffered en unbuffered ken ik, beiden zijn natuurlijk ook beschikbaar in de C interface maar daar wordt de term cursor niet gebruikt.
Een cursor lijkt een soort pre-allocated resultset maar daardoor is de interface volgens mij veel complexer dan nodig.

Dat zou best kunnen. Op DB niveau moet je een cursor ook definiëren qua opbouw en statement (en nog wat dingen) voor je hem kunt gebruiken. Dus als je dat vanuit Python doet, dan zal dat ook nodig zijn verwcaht ik (ken python alleen in basis). Voordeel is dat je heel snel door grote hoeveelheden data heen kunt, nadeel is een stukje complexiteit. Dus nogmaals, gaat het om enkele regels, gebruik gewoon een standaard sql statement. Moet je op 10k+ rijen tegelijk aan de gang met DML, dan is een cursor ws handig.

Bedenk je ook dat een DBMS onderhuids vaak ook cursors gebruikt voor het ophalen van data (explain plans anyone?) Voor DML ligt dat echter complexer omdat het DBMS niet zo makkelijk kan interpreteren wat jij als programmeur exact wilt. Dus dan moet je een cursor zelf definieren

Waar ik het persoonlijk vaak bij gebruikte was DML op grote datasets waarbij de hoeveelheid dermate groot was dat het DBMS uit zijn undo / redo kon lopen. Dan moet je tussentijds commits gaan geven en das iets wat met een standaard SQL statement niet zo makkelijk kan.

[Olaf van der Spek]

The Eagle schreef op zaterdag 4 november 2017 @ 12:25:
Dus nogmaals, gaat het om enkele regels, gebruik gewoon een standaard sql statement.

Zonder cursor kun je volgens mij ook geen standaard statement uitvoeren..
De vraag is echt Python-specifiek.

Ik denk dat het 'antwoord' eigenlijk https://www.sqlalchemy.org/ is, een laag bovenop DBAPI.

[ Voor 14% gewijzigd door Olaf van der Spek op 07-11-2017 14:54 ]

[drm]

Mijn inschatting: Het feit dat je altijd een cursor moet gebruiken is dat de SQL Library niet zelf een beslissing kan nemen over wanneer iets wel of niet resultaten terug gaat geven zonder de SQL te gaan interpreteren.

Het feit dat je uberhaupt een cursor moet gebruiken is dat het jou de controle geeft over wanneer de data precies daadwerkelijk het geheugen ingeladen moet gaan worden, zonder daar een library-brede of een implementatie-brede beslissing over te nemen. De ene keer is het ene handig, de andere keer het andere.

Verder is er natuurlijk niets op tegen je eigen wrappertje eromheen te schrijven, maar wat je dat dan precies oplevert behalve gewoon nog een extra laagje kun je je ook afvragen.

[gekkie]

Maar ik snap eigenlijk nog steeds niet wat je probleem nou eigenlijk is ?

Als ik het wel lees op bijvb: https://dev.mysql.com/doc...orial-cursorbuffered.html Krijg je kennelijk met buffered een iterator terug. Lijkt me dat je daarmee wel je cursor referentie naar een cursor object hebt opgegeven, omdat het nu verwijst naar een list van de resultset.
En dat de code die jij had staan derhalve niet zal werken.

Wat dan het voordeel van dat buffered is ten opzichte van:

Python:

cursor.execute("select id, latitude, longitude from ...")
results = cursor.fetchall()
for id, lat, lon in results :
    cursor.execute("update ...")

ontgaat me dan een beetje (behalve 1 regel besparing die je bij deze constructie dan weer kwijt ben om een nieuwe cursor object te verkrijgen).

Zelf werk ik eigenlijk vooral met Postgres en gebruik ik eigenlijk altijd een realdict cursor die me een list van dicts terug geeft als resultaat, is de volgorde ook niet meer zo van belang.

[ Voor 10% gewijzigd door gekkie op 07-11-2017 17:08 ]

[Olaf van der Spek]

gekkie schreef op dinsdag 7 november 2017 @ 17:07:
ontgaat me dan een beetje (behalve 1 regel besparing die je bij deze constructie dan weer kwijt ben om een nieuwe cursor object te verkrijgen).

Ik ben gewend (in C++) de resultset niet te kopiëren (zoals met fetchall() ). Liefst zou ik het volgende kunnen schrijven:

Python:

for id, lat, lon in db.execute("select id, latitude, longitude from ...") :

Zit je ook niet met die results var.

[gekkie]

Olaf van der Spek schreef op dinsdag 28 november 2017 @ 13:22:
[...]
Ik ben gewend (in C++) de resultset niet te kopiëren (zoals met fetchall() ). Liefst zou ik het volgende kunnen schrijven:

Python:

for id, lat, lon in db.execute("select id, latitude, longitude from ...") :

Zit je ook niet met die results var.

Hangt er een beetje heel erg vanaf hoe groot je result set is.

Als die erg groot is kun je overwegen een server-side-cursor nemen waarbij de result set bij de DB blijft en je client er doorheen kan itereren. Maar goed is deels ook het verplaatsen van het probleem aangezien nu de DB de result set in memory gaat houden (of een temp table op disk).

(en bedenk dat in python datastructuren best zwaar kunnen zijn, maar wel zowat alles een referentie/pointer is en je zelden een kopie krijgt als je er niet expliciet om vraagt)

[Olaf van der Spek]

gekkie schreef op dinsdag 28 november 2017 @ 13:40:
Hangt er een beetje heel erg vanaf hoe groot je result set is.

In principe zou deze syntax zowel met als zonder buffering moeten kunnen werken.. execute kan een cursor / result set handler retourneren en dan zijn we klaar.

[gekkie]

Olaf van der Spek schreef op dinsdag 28 november 2017 @ 13:51:
[...]

In principe zou deze syntax zowel met als zonder buffering moeten kunnen werken.. execute kan een cursor / result set handler retourneren en dan zijn we klaar.

Lijkt ook te kunnen volgens de DB-API 2.0 pep-0249 als de DB specifieke driver/api de volgende extensions ondersteunt:

Cursor.next()
Return the next row from the currently executing SQL statement using the same semantics as .fetchone().
A StopIteration exception is raised when the result set is exhausted for Python versions 2.2 and later.
Previous versions don't have the StopIteration exception and so the method should raise an IndexError
instead.

Warning Message: "DB-API extension cursor.next() used"

Cursor.__iter__()
Return self to make cursors compatible to the iteration protocol [8].
Warning Message: "DB-API extension cursor.__iter__() used"

[Olaf van der Spek]

gekkie schreef op dinsdag 28 november 2017 @ 14:09:
[...]

Lijkt ook te kunnen volgens de DB-API 2.0 pep-0249 als de DB specifieke driver/api de volgende extensions ondersteunt:

[...]

Ik bedoel de code als in Olaf van der Spek in "Python Database Cursors (& MySQL)"
Dat kan gewoon niet met het huidige API design. Met https://www.sqlalchemy.org/ waarschijnlijk wel.

[gekkie]

Olaf van der Spek schreef op dinsdag 28 november 2017 @ 15:40:
[...]

Ik bedoel de code als in Olaf van der Spek in "Python Database Cursors (& MySQL)"
Dat kan gewoon niet met het huidige API design. Met https://www.sqlalchemy.org/ waarschijnlijk wel.

Nee omdat het dus op voorhand niet duidelijk is wat je zou willen als return op je execute.
Wil jij een iterator, een fetchone, een fetchmany, een serverside cursor iterator.
Maar om de API dan niet goed ontworpen te noemen ...

[Olaf van der Spek]

gekkie schreef op dinsdag 28 november 2017 @ 19:04:
[...]

Nee omdat het dus op voorhand niet duidelijk is wat je zou willen als return op je execute.
Wil jij een iterator, een fetchone, een fetchmany, een serverside cursor iterator.
Maar om de API dan niet goed ontworpen te noemen ...

Nee?
fetchone en fetchmany lijken mij simpel te implementeren via een iterator. Een serverside cursor geef je al aan via buffered=True.

[gekkie]

Olaf van der Spek schreef op dinsdag 28 november 2017 @ 19:28:
[...]
Nee?
fetchone en fetchmany lijken mij simpel te implementeren via een iterator. Een serverside cursor geef je al aan via buffered=True.

Dan hou ik de opties over: dien een PEP in (ga klagen waar het potentieel zin heeft, al zal niemand het willen wijzigen dus succes), maak je eigen abstractie (fix het voor jezelf_ , of blijf bij C++ wat immers doet wat je wil (of ontwijk het).