[sqlalchemy] Re: postgres and server_side_cursors
Michael Bayer wrote: ive heard of this one already, which is why the option is turned off. unfortunately I cant reproduce that here, so until someone wants to figure out whats going on with that, not sure what I can do. After some tries, I see that is I wrote (very bad code line): cursor.cursor.fetchone() at line 553 at engine/base.py, all work. with a: print "cur descr bef", cursor.description cursor.cursor.fetchone() print "after", cursor.descriptio cur descr bef >>> None after (('col1', 25, 10, -1, None, None, None), ... end so on Michele smime.p7s Description: S/MIME Cryptographic Signature
[sqlalchemy] Re: postgres and server_side_cursors
Michael Bayer wrote: ive heard of this one already, which is why the option is turned off. unfortunately I cant reproduce that here, so until someone wants to figure out whats going on with that, not sure what I can do. Tell me what I have to do. Here I have debian etch and pg 8.1.(the last deb package) I don't know if it can help, but a: print key, self.props at line 622 (so before the error), show me: 0 {} we do have a ticket where someone commented that the server side cursor will cause things like "rowcount" to not function until the first row is fetched. I also do not observe that behavior on my system...asked the guy to post what cases cause that, big surprise, no response. its like our trac system is an oracle of shadowy myths and rumors. Just tried with a named cursor (conn.cursor("test")) with a "raw" connection (without sa) and I see that the cur.rowcount have the number of fetched records, so: cur.execute("SELECT id FROM a_table_with_100k_rec") cur.fetchone() print cur.rowcount # print 1 cur.execute("SELECT id FROM a_table_with_100k_rec") cur.fetchmany(1000) print cur.rowcount # print 1000 Bye, Michele smime.p7s Description: S/MIME Cryptographic Signature
[sqlalchemy] postgres and server_side_cursors
Hi, I'm trying the "new" server_side_cursors option with pg and I see that I receive an error when I use the server_side_cursors=True and the Table autoload=True. The autoload=True with the server_side_cursors=False work. The server_side_cursors=True specifying the table structure work. (I'm using the last svn version) md_pg = BoundMetaData('postgres://[EMAIL PROTECTED]:5432/book', server_side_cursors=False) book_pg = Table('book', md_pg, autoload=True) book_pg = Table('book', md_pg, autoload=True) File "/usr/lib/python2.4/site-packages/sqlalchemy/schema.py", line 143, in __call__ metadata.get_engine().reflecttable(table) File "/usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py", line 508, in reflecttable self.dialect.reflecttable(conn, table) File "/usr/lib/python2.4/site-packages/sqlalchemy/databases/postgres.py", line 357, in reflecttable for name, format_type, default, notnull, attnum, table_oid in rows: File "/usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py", line 732, in __iter__ yield self.__parent._get_col(self.__row, i) File "/usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py", line 634, in _get_col rec = self._convert_key(key) File "/usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py", line 622, in _convert_key rec = self.props[key] KeyError: 0 smime.p7s Description: S/MIME Cryptographic Signature
[sqlalchemy] Re: [Sqlalchemy-users] named cursor
Michael Bayer wrote: > > On Jan 9, 2007, at 5:24 AM, Michele Petrazzo wrote: >> Because, like what I wrote on the psycopg2 ml (lists.initd.org/ >> pipermail/psycopg/2007-January/005250.html) >> without the "server side cursor", the memory usage on the client >> side is proportionally at the number of records returned by the >> query (on my example 400k records, 60MB ram). I think because the >> driver fetch all the data from the server and leave it available >> with the fetchXXX methods. > > really ? im not sure about psycopg2 specifically, Here, sure! Assuming the I'm on linux and I have "ps" utility locally. The db are on the same machine where I'm making tests. PG are 8.1.5 and psycopg2 2.0.4. This is my test: <-code-> import time import subprocess as SP import psycopg2 NAMED_CURSOR = 1 def get_mem(): #Wait for the memory stabilization time.sleep(0.5) #get the memory usage with ps. output = SP.Popen(["ps","-eo","rss,args"], stdout=SP.PIPE).communicate()[0] for line in output.split('\n'): if not line: continue mem, pr_name = line.split(None,1) #show only the my python exe if not "python " + __file__ in pr_name: continue return mem def create_cur(): if NAMED_CURSOR: return con.cursor("test") else: return con.cursor() def exec_q(q): cur = create_cur() print "empty cursor ", get_mem() cur.execute(q) print "cursor after execute ", get_mem() print "result --", cur.fetchone() print "after fetchone ", get_mem() cur.close() print "cursor closed ", get_mem() print "python and modules ", get_mem() con = psycopg2.connect('user=test password=test dbname=book') print "connection ", get_mem() exec_q("SELECT COUNT(id) FROM book") exec_q("SELECT * FROM book") ## with NAMED_CURSOR = 1 michele:~/tmp$ time python test_sql_mem.py python and modules 3928 connection 4048 empty cursor 4068 cursor after execute 4080 result -- (406500L,) after fetchone 4088 cursor closed 4092 empty cursor 4092 cursor after execute 4092 result -- (6087, 'title', 15, '18', 'Sonzogno', 508126) after fetchone 4092 cursor closed 4092 real0m6.107s user0m0.080s sys 0m0.076s michele:~/tmp$ ## with NAMED_CURSOR = 0 michele:~/tmp$ time python test_sql_mem.py python and modules 3932 connection 4048 empty cursor 4068 cursor after execute 4080 result -- (406500L,) after fetchone 4092 cursor closed 4092 empty cursor 4092 cursor after execute 58440 result -- (6087, 'title', 15, '18', 'Sonzogno', 508126) after fetchone 58452 cursor closed 58452 real0m11.499s user0m0.544s sys 0m0.176s michele:~/tmp$ > but its been my > experience with virtually all database client libraries that as you > fetch rows and discard them, you only pull as much as you fetch into > memory. i.e. if you had a resultset of 4000 rows and just fetched 5 > rows, only 5 rows get pulled over the wire. thats definitely how the > lower level APIs work I'm with you, but my code show other results > and it would be pretty bad if psycopg2 didnt maintain that behavior. I'll send another email to the psycopg2 ml, with this results, and I'll reply you in the near future. Michele --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: [Sqlalchemy-users] named cursor
Michael Bayer wrote: <-cut-> if you are looking to use a named cursor within generated SQL and/or ORM (session) flushes and stuff like that, the API is not really there for that stuff. you can create ResultProxy objects with a cursor, like this: connection = engine.connect() dbapi_conn = connection.connection cursor = dbapi_conn.cursor("test") result = cursor.execute("select * from table") result_proxy = ResultProxy(engine, connection, cursor, None) but thats about as much as you can do. Yes. it's a solution (good) for me <-cut-> so the main question is, what do you need named cursors for ? Because, like what I wrote on the psycopg2 ml (lists.initd.org/pipermail/psycopg/2007-January/005250.html) without the "server side cursor", the memory usage on the client side is proportionally at the number of records returned by the query (on my example 400k records, 60MB ram). I think because the driver fetch all the data from the server and leave it available with the fetchXXX methods. With the other solution, the driver fetch it only then a request occur. So, from here, my question born :) As far as I can read on internet, not all the drivers can use the named cursor feature (pg and oracle yes), but I believe that when it support this feature (also mentioned on the DB-API2 spec.), why not use it? Thanks, Michele smime.p7s Description: S/MIME Cryptographic Signature