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.
已经有一段时间没有分享阿里云服务商的促销活动,主要原因在于他们以前的促销都仅限新用户,而且我们大部分人都已经有过账户基本上促销活动和我们无缘。即便老用户可选新产品购买,也是比较配置较高的,所以就懒得分享。这不看到有阿里云金秋活动,有不错的促销活动可以允许产品新购。即便我们是老用户,但是比如你没有购买过他们轻量服务器,也是可以享受优惠活动的。这次轻量服务器在金秋活动中力度折扣比较大,2G5M配置年付...
看到群里网友们在讨论由于不清楚的原因,有同学的网站无法访问。他的网站是没有用HTTPS的,直接访问他的HTTP是无法访问的,通过PING测试可以看到解析地址已经比较乱,应该是所谓的DNS污染。其中有网友提到采用HTTPS加密证书试试。因为HTTP和HTTPS走的不是一个端口,之前有网友这样测试过是可以缓解这样的问题。这样通过将网站绑定设置HTTPS之后,是可以打开的,看来网站的80端口出现问题,而...
ucloud云服务器怎么样?ucloud为了扩大云服务器市场份额,给出了超低价云服务器的促销活动,活动仍然是此前的Ucloud全球大促活动页面。目前,ucloud国内云服务器2元/月起;香港云服务器4元/首月;台湾云服务器3元/首月。相当于2-4元就可以试用国内、中国香港、中国台湾这三个地域的云服务器1个月了。ucloud全球大促仅限新用户,国内云服务器个人用户低至56元/年起,香港云服务器也仅8...
postgresql9.0为你推荐
.cn域名cn域名和com域名有什么不同?哪个更好?好在哪里?haole018.com为什么www.haole008.com在我这里打不开啊,是不是haole008换新的地址了?www.se333se.com米奇网www.qvod333.com 看电影的效果好不?www.kaspersky.com.cn卡巴斯基杀毒软件有免费的吗?稳定版的怎么找?baqizi.cc和空姐一起的日子电视剧在线观看 和空姐一起的日子全集在线观看dadi.tvapple TV 功能介绍机器蜘蛛尼尔机械纪元机械蜘蛛怎么过 机械蜘蛛打法攻略解析59ddd.com网站找不到了怎么办啊月风随笔赏月之后的情感?语文随笔200-400字网页源代码什么是网页源代码!打开网页后怎么找?
租服务器 怎样申请域名 联通c套餐 vultr美国与日本 博客主机 魔兽世界台湾服务器 京东商城0元抢购 大容量存储器 shopex主机 安徽双线服务器 万网空间管理 架设邮件服务器 双线空间 免费个人主页 supercache umax 美国主机侦探 .htaccess qq空间打开很慢 游戏服务器 更多