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.
今天9月10日是教师节,我们今天有没有让孩子带礼物和花送给老师?我们这边不允许带礼物进学校,直接有校长在门口遇到有带礼物的直接拦截下来。今天有看到Friendhosting最近推出了教师节优惠,VPS全场45折,全球多机房可选,有需要的可以看看。Friendhosting是一家成立于2009年的保加利亚主机商,主要提供销售VPS和独立服务器出租业务,数据中心分布在:荷兰、保加利亚、立陶宛、捷克、乌...
Hostio是一家成立于2006年的国外主机商,提供基于KVM架构的VPS主机,AMD EPYC CPU,NVMe硬盘,1-10Gbps带宽,最低月付5欧元起。商家采用自己的网络AS208258,宿主机采用2 x AMD Epyc 7452 32C/64T 2.3Ghz CPU,16*32GB内存,4个Samsung PM983 NVMe SSD,提供IPv4+IPv6。下面列出几款主机配置信息。...
pacificrack发布了7月最新vps优惠,新款促销便宜vps采用的是魔方管理,也就是PR-M系列。提一下有意思的是这次支持Windows server 2003、2008R2、2012R2、2016、2019、Windows 7、Windows 10,当然啦,常规Linux系统是必不可少的!1Gbps带宽、KVM虚拟、纯SSD raid10、自家QN机房洛杉矶数据中心...支持PayPal、...
postgresql9.0为你推荐
哈利波特罗恩升级当爸哈利波特的爸爸妈妈身份2020双十一成绩单2020年河南全县初二期末成绩排名?硬盘的工作原理硬盘的工作原理是?(不要给我网址,我用的手机)商标注册流程及费用商标注册流程及费用?广东GDP破10万亿广东省城市经济排名广东GDP破10万亿想知道广东城市的GDP排名比肩工场比肩之意是什么意思地陷裂口地陷前期会有什么征兆吗?陈嘉垣反黑阿欣是谁演的 扮演者介绍xyq.163.cbg.com梦幻CBG的网站是什么。
Oray域名注册服务商 北京租服务器 双线vps 最便宜虚拟主机 免费动态域名 域名商 国外永久服务器 pccw 香港托管 谷歌香港 新世界电讯 新站长网 国外在线代理 权嘉云 空间合租 vip域名 华为云盘 hostease 云销售系统 镇江高防服务器 更多