ORDERpostgresql9.0

AdvancedaccesstoPostgreSQLfromPythonwithpsycopg2"classic"psycopghomepagePsycopgcharacteristicsLGPLlicenseWrittenmostlyinClibpqwrapperPython2.
4–2.
7PostgreSQL>=7.
4–droppedV2protocolsupportin2.
3ImplementsPythonDB-APIinterfaceconnectionwrapsthesessioncursorholdsaresultLatesthistoryBefore2010:alotofundocumentedfeaturesPy-PGadaptation,SSC,notifies2.
2:asyncsupport2.
3:notifypayload,2PC,hstoreLet'stalkabout.
.
.
TypesadaptationServer-sidecursorsTransactionshandlingAsyncsupportServernotificationsTypesadaptationServer-sidecursorsTransactionshandlingAsyncsupportServernotificationsPythonobjectsadaptationAnadaptermapsPythonobjectsintoSQLsyntaxbuilt-inadaptersforbasicobjects/typesAdaptersareregisteredbytypesincePsycopg2.
3:Liskov-friendlyAdapterexample:XMLfromxml.
etreeimportcElementTreeasETfrompsycopg2.
extensionsimport\adapt,register_adapterclassElementAdapter:def__init__(self,elem):self.
elem=elemdefgetquoted(self):return"%s::xml"\%adapt(ET.
tostring(elem))register_adapter(type(ET.
Element('')),ElementAdapter)Adapterexample:XMLelem=ET.
fromstring("Hello,'xml'!
")printadapt(elem).
getquoted()#'Hello,''xml''!
'::xmlcur.
execute("""INSERTINTOxmltest(xmldata)VALUES(%s);""",(elem,))PostgreSQLtypesadaptationAtypecastermapsPostgreSQLtypesintoPythonobjectsTypecastersareregisteredperoidGlobal,connectionorcursorscopeTypecasterexample:XMLdefcast_xml(value,cur):ifvalueisNone:returnNonereturnET.
fromstring(value)frompsycopg2.
extensionsimport\new_type,register_typeXML=new_type((142,),"XML",cast_xml)register_type(XML)Typecasterexample:XMLcur.
execute("""SELECTxmldataFROMxmltestORDERBYidDESCLIMIT1;""")elem=cur.
fetchone()[0]printelem.
text#Hello,'xml'!
dict-hstoreadaptationhstore:associativearrayofstringsfoo=>bar,baz=>whateverImprovedinPostgreSQL9.
0capacityandindexingAdapternewinPsycopg2.
3candealwithbothpre-9.
0and9.
0PostgreSQLdict-hstoreadaptationpsycopg2.
extras.
register_hstore(cnn)cur.
execute("SELECT'a=>b'::hstore;")printcur.
fetchone()[0]#{'a':'b'}cur.
execute("SELECT%s;",[{'foo':'bar','baz':None}])#SELECThstore(ARRAY[E'foo',E'baz'],#ARRAY[E'bar',NULL])hstore:SOuseful.
.
.
ifIonlycouldremembertheoperatorscur.
execute(#hasakey"select*frompetswheredata%s;",('tail',))cur.
execute(#hasallkeys"select*frompetswheredata&%s;",(['tail','horns'],))cur.
execute(#hasanykey"select*frompetswheredata|%s;",(['wings','fins'],))cur.
execute(#haskeys/values"select*frompetswheredata@>%s;",({'eyes':'red','teeth':'yellow'},))TypesadaptationServer-sidecursorsTransactionshandlingAsyncsupportServernotificationsProblem:outofmemoryIhavethisproblem:cursor.
execute("select*inbig_table")forrecordincursor:whatever(record)Well,itdoesn'twork:"outofmemory"!
Problem:outofmemorycursor.
execute()movesallthedatasettotheclientPGresultstructurecursor.
fetch*()onlymanipulatesclient-sidedataPGresult→PythonobjectsDECLAREtotherescue!
Namedcursorsconnection.
cursor(name)cursor.
execute(sql)→DECLAREnameCURSORFORsqlcursor.
fetchone()→FETCHFORWARD1FROMnamecursor.
fetchmany(n)→FETCHFORWARDnFROMnameNamedcursorIfyouneedtomanipulatemanyrecordsclient-sideBeststrategy:cur=connection.
cursor(name)cur.
execute()cur.
fetchmany(n)ReasonablentohavegoodmemoryusageandnottoomanynetworkrequestsTypesadaptationServer-sidecursorsTransactionshandlingAsyncsupportServernotificationsTransactionshandlingTheconnection"has"thetransactionallitscursorsshareitEveryoperationinatransactionDB-APIrequirementUntil.
commit()or.
rollback()youare"intransaction"badformanyreasonsClosethattransaction!
Peoplearenotoriouslygoodatrememberingboringdetails,aren'ttheyconn.
commit()/conn.
rollback()Useadecorator/contextmanager@with_connectiondefdo_some_job(conn,arg1,arg2):cur=conn.
cursor()#.
.
.
withget_connection()asconn:cur=conn.
cursor()#.
.
.
Goautocommitifyouneedtoconn.
set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)TypesadaptationServer-sidecursorsTransactionshandlingAsyncsupportServernotificationsAsyncinpsycopgAttemptfrompsycopg2,neverworkedcorrectlyconn.
execute(query,args,async=1)Redesigninspring2010,releasedin2.
2ThankstoJanUrbańskiBeingasyncisnowaconnectionpropertypsycopg2.
connect(dsn,async=1)AsynccodepathwellseparatedfromsyncpsycopgandlibpqsyncpsycopgandlibpqsyncAsyncinpsycopgconn.
fileno()Makestheconnectionafile-likeobjectconn.
poll()→[OK|READ|WRITE]poll()knowsthingsCallsthecorrectlibpqfunctionaccordingtotheoperationtobeperformed–connection,query,fetch,notifies.
.
.
andthestateoftheconnectionAllowseasyusagepatterncur.
execute(query,args)while"not_happy":conn.
poll()Asyncexamplecursor.
execute(SQL)while1:state=conn.
poll()ifstate==POLL_OK:breakelifstate==POLL_READ:select([conn.
fileno(elifstate==POLL_WRITE:select([],[conn.
fileno()],[])cursor.
fetchall()psycopgandlibpqasyncAsynchronousaccessFundamentalproblem:DB-APIisblockingcnn=psycopg2.
connect(dsn)cursor.
execute(query,args)cursor.
fetchall()AsyncconnectionshaveadifferentinterfaceSowecan'tuseDjango,SQLAlchemy.
.
.
Completecontrol,buthigherleveltoberedoneSolution#1The"TwistedSolution":whatproblem:o)everythingmustbecallback-basedanywaytxPostgres:asyncpsycopg2inTwistedd=conn.
connect(database=DB_NAME)d.
addCallback(lambdac:c.
execute(SQL))d.
addCallback(lambdac:c.
fetchall())Notice:manyfeaturesmissinginasyncNotransactions,SSC,…CoroutinelibrariesInterpreter-levelcooperativeaka"green"threadsEventlet,gevent,uGreen"Monkeypatch"blockingfunctionstime.
sleep(),socket.
read().
.
.
Cextensionscan'tbepatchedAcolleagueofminewasstrugglingwithpg8000.
.
.
Solution#2:"wait"callbackGloballyregisteredpsycopg2.
extensions.
set_wait_callback(f)Givescontrolbacktotheframeworkwhenit'stimetowaitControlcanbepassedtoadifferentthreadThePythoninterfaceisunchangedLessflexible,butclassicblockingDB-APICustomizedfordifferentcoroutinelibrariesOutsideofpsycopgscope,butcheckpsycogreenExamplewaitcallbackdefeventlet_wait_callback(conn):while1:state=conn.
poll()ifstate==POLL_OK:breakelifstate==POLL_READ:trampoline(conn.
fileno(),read=1)elifstate==POLL_WRITE:trampoline(conn.
fileno(),write=1)psycopgandlibpqgreenTypesadaptationServer-sidecursorsTransactionshandlingAsyncsupportServernotificationsServernotificationsPublish/subscribechannelsPostgreSQLLISTENandNOTIFYAddedpayloadinPostgreSQL9.
0ServernotificationsPayloadsupportfromPsycopg2.
3Receivedonexecute()Receivedonpoll()Theyloveasyncmode!
Notification:pushexampleListenforDBnotifiesandputtheminaqueuedefdblisten(q):cnn=psycopg2.
connect(dsn)cnn.
set_isolation_level(0)cur=cnn.
cursor()cur.
execute("listendata;")while1:trampoline(cnn,read=True)cnn.
poll()whilecnn.
notifies:q.
put(cnn.
notifies.
pop())Notification:pushexampleThanks!
QuestionsThisworkislicensedunderCreativeCommonsAttribution-NonCommercial-ShareAlike3.
0License.