= 7.4 – dropped V"> ORDERpostgresql9.0

ORDERpostgresql9.0

postgresql9.0  时间:2021-04-10  阅读:()
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.

RAKsmart秒杀服务器$30/月,洛杉矶/圣何塞/香港/日本站群特价

RAKsmart发布了9月份优惠促销活动,从9月1日~9月30日期间,爆款美国服务器每日限量抢购最低$30.62-$46/月起,洛杉矶/圣何塞/香港/日本站群大量补货特价销售,美国1-10Gbps大带宽不限流量服务器低价热卖等。RAKsmart是一家华人运营的国外主机商,提供的产品包括独立服务器租用和VPS等,可选数据中心包括美国加州圣何塞、洛杉矶、中国香港、韩国、日本、荷兰等国家和地区数据中心(...

piayun(pia云)240元/季起云服务器,香港限时季付活动,cn2线路,4核4G15M

pia云怎么样?pia云是一家2018的开办的国人商家,原名叫哔哔云,目前整合到了魔方云平台上,商家主要销售VPS服务,采用KVM虚拟架构 ,机房有美国洛杉矶、中国香港和深圳地区,洛杉矶为crea机房,三网回程CN2 GIA,带20G防御。目前,Pia云优惠促销,年付全场8折起,香港超极速CN2季付活动,4核4G15M云服务器仅240元/季起,香港CN2、美国三网CN2深圳BGP优质云服务器超高性...

Letbox(35美元/年),美国洛杉矶VPS终身7折

Letbox 云服务商在前面的文章中其实也有多次介绍,这个服务商其实也算是比较老牌的海外服务商,几年前我也一直有使用过他们家的VPS主机,早年那时候低至年付15-35美元左右的VPS算式比较稀缺的。后来由于服务商确实比较多,而且也没有太多的网站需要用到,所以就没有续费,最近这个服务商好像有点活动就躁动的发布希望引起他人注意。这不有看到所谓的家中有喜事,应该是团队中有生宝宝了,所以也有借此来发布一些...

postgresql9.0为你推荐
乐划锁屏乐视手机怎么解除屏幕锁关键字数据库:什么是关键字?嘀动网手机一键通用来干嘛呢?网站检测如何进行网站全面诊断porndao单词prondao的汉语是什么www.44ri.comwww.yydcsjw.comwww.228gg.comwww.a8tb.com这个网站该如何改善javmoo.com找下载JAV软件格式的网站www.zjs.com.cn怎么查询我的平安信用卡寄送情况www.kaspersky.com.cn卡巴斯基中国总部设立在?
怎么注册域名 cn域名注册 重庆vps租用 buyvm 主机 安云加速器 远程登陆工具 台湾谷歌地址 泉州电信 php空间购买 服务器合租 台湾谷歌 如何注册阿里云邮箱 免费邮件服务器 帽子云排名 中国linux lamp的音标 金主 登陆qq空间 空间服务器 更多