Re: pymssql - execute loads all results into memory!
On Oct 20, 3:38 pm, Tim Golden [EMAIL PROTECTED] wrote:
Eric Wertman wrote:
I am trying to use pymssql, and have an issue where by the execute
(not the fetch) is appearing to load all records into memory.
if I execute
con = pymssql.connect(...)
cur = con.cursor()
cur.execute(sql)
rec = cur.fetchone()
if I put in a query which returns a lot of records into sql then the
execute never returns, pythons memory usage slowly ballons till the
machine cant give anymore. If I put a sql query returning only few
rows, then it works fine.
So I am not sure why an execute would feel the need to load all rows,
but its seriously crippling. Does anyone know if this is a bug or
something I can turn off
I ran into this myself. After some digging I discovered that what you
are after is a server-side cursor that isn't implemented yet in
pymssql. There is one in MySQLdb, but it's not the default behavior.
Regardless of your usage (fetchone vs fetchmany), the result set is
held client side. AFAIK the only workaround is to keep your result
set small (enough). If you use fetchmany and iterate over it
directly, it may keep your memory usage down, I can't remember if
that worked. I definitely tried making a generator with it, that did
not help.
... or just switch to pyodbc, for example, which behaves
perfectly well with this snippet against a table of 24 million
rows:
code
import pyodbc
conn = [
Driver={SQL Server},
Server=SVR17,
Database=TDI,
TrustedConnection=Yes
]
db = pyodbc.connect (;.join (conn))
q = db.cursor ()
q.execute (SELECT * FROM revenue) # 24 million rows
q.fetchone ()
q.close ()
/code
TJG
Thanks for the responses, I am astounded any db api tool doesnt
support cursors! pymssql is mostly useless, I will switch to an odbc
interface.
--
http://mail.python.org/mailman/listinfo/python-list
pymssql - execute loads all results into memory!
Hi I am trying to use pymssql, and have an issue where by the execute (not the fetch) is appearing to load all records into memory. if I execute con = pymssql.connect(...) cur = con.cursor() cur.execute(sql) rec = cur.fetchone() if I put in a query which returns a lot of records into sql then the execute never returns, pythons memory usage slowly ballons till the machine cant give anymore. If I put a sql query returning only few rows, then it works fine. So I am not sure why an execute would feel the need to load all rows, but its seriously crippling. Does anyone know if this is a bug or something I can turn off thanks, -- http://mail.python.org/mailman/listinfo/python-list
Re: FTP (ftplib) output capture
Hi
Thanks for the reply.
I will send it in when I am done :-)
Simon Forman wrote:
ChaosKCW wrote:
Hi
Has anyone caputerd the output from the std ftp lib? It seems a bit
annoying that everything is printed to stdout. It means incorporating
this into any real program is a problem. It would have been much better
if they used the std logging module and hooked up a console logger for
the feault ftp application. Alas it isnt that way.
Capturing stdout like this??? :
import sys
sys.stdout = open('bb', 'w)
But I want to re-route it to the logging module not a file , so do I
need to write a steam object?
Thanks,
ftplib pre-dates the standard logging system by a bit.
I think ftplib only prints stuff if you set its (the FTP class
instances') debug level to greater than 0.
If you really want to replace sys.stdout with something that passes the
data to a logger, then something like the following (untested) class
should do it:
class FileLog:
def __init__(self, log):
self.log = log
def write(self, data):
self.log.debug(data)
The best solution might be to subclass or rewrite FTP to really do what
you want. I believe one of the goals of Python-3000 is better
integration of the standard library with the new-ish standard logging
system, so if you do a good job send it in. ;-)
Peace,
~Simon
--
http://mail.python.org/mailman/listinfo/python-list
FTP (ftplib) output capture
Hi
Has anyone caputerd the output from the std ftp lib? It seems a bit
annoying that everything is printed to stdout. It means incorporating
this into any real program is a problem. It would have been much better
if they used the std logging module and hooked up a console logger for
the feault ftp application. Alas it isnt that way.
Capturing stdout like this??? :
import sys
sys.stdout = open('bb', 'w)
But I want to re-route it to the logging module not a file , so do I
need to write a steam object?
Thanks,
--
http://mail.python.org/mailman/listinfo/python-list
Re: Convertion of Unicode to ASCII NIGHTMARE
Roger Binns wrote: No. APSW converts it *to* Unicode. SQLite only accepts Unicode so a Unicode string has to be supplied. If you supply a non-Unicode string then conversion has to happen. APSW asks Python to supply the string in Unicode. If Python can't do that (eg it doesn't know the encoding) then you get an error. If what you say is true, I have to ask why I get a converstion error which states it cant convert to ASCII, not it cant convert to UNICODE? Ok if SQLite uses unicode internally why do you need to ignore everything greater than 127, I never said that. I said that a special case is made so that if the string you supply only contains ASCII characters (ie =127) then the ASCII string is converted to Unicode. (In fact it is valid UTF-8 hence the shortcut). the ascii table (256 bit one) fits into unicode just fine as far as I recall? No, ASCII characters have defined Unicode codepoints. The ASCII character number just happens to be the same as the Unicode codepoints. But there are only 127 ASCII characters. Or did I miss the boat here ? For bytes greater than 127, what character set is used? There are hundreds of character sets that define those characters. You have to tell the computer which one to use. See the Unicode article referenced above. Yes I know there are a million extended ASCII charaters sets, which happen to the bane of all existence. Most computers deal in bytes nativly and the 7 bit coding still causes problems to this day. But since the error I get is a converstion error to ASCII, not from ASCII, I am willing to accept loss of information. You cant code unicode into ascii without loss of information or two charcater codes. In my mind, somewhere inside the cursor.execute function, it converts to ascii. I say this because of the error msg recieved. So I am missing how a function which supposedly converts evereythin to unicode lands up doing an ascii converstion ? -- http://mail.python.org/mailman/listinfo/python-list
Re: Convertion of Unicode to ASCII NIGHTMARE
There's an Oracle environment variable that appears to make a difference: NLS_CHARSET, perhaps - it's been a while since I've had to deal with Oracle, and I'm not looking for another adventure into Oracle's hideous documentation to find out. That is an EVIL setting which should not be used. The NLS_CHARSET environment variable causes so many headaches its not worth playing with it at all. -- http://mail.python.org/mailman/listinfo/python-list
Re: Convertion of Unicode to ASCII NIGHTMARE
When python tries to concatenate a byte string and a unicode string, it assumes that the byte string is encoded ascii and tries to convert from encoded ascii to unicode. It calls ascii decoder to do the decoding. If decoding fails you see message from ascii decoder about the error. Serge Ok I get it now. Sorry for the slowness. I have to say as a lover of python for its simplicity and clarity, the charatcer set thing has been harder than I would have liked to figure out. Thanks for all the help. -- http://mail.python.org/mailman/listinfo/python-list
Re: Convertion of Unicode to ASCII NIGHTMARE
Hi
Thanks for all the posts. I am still digesting it all but here are my
initial comments.
Don't. You can't. Those characters don't exist in the ASCII character set.
SQLite 3.0 deals with UTF-8 encoded SQL statements, though.
http://www.sqlite.org/version3.html
As mentioned by the next poster, there is, its supposed to be encode
with the 'ignore' option. Thus you lose data, but thats just dandy with
me. As for SQLite supporting unicode, it probably does, but something
on the python side (probabyl in apsw) converts it to ascii at some
point before its handed to SQLite.
The .encode() method returns a new value; it does not change an object inplace.
sql = sql.encode('utf-8')
Ah yes, big bistake on my part :-/
He is using apsw. apsw correctly handles unicode. In fact it won't
accept a str with bytes 127 as they will be an unknown encoding and
SQLite only uses Unicode internally. It does have a blob type
using buffer for situations where binary data needs to be stored.
pysqlite's mishandling of Unicode is one of the things that drove
me to writing apsw in the first place.
Ok if SQLite uses unicode internally why do you need to ignore
everything greater than 127, the ascii table (256 bit one) fits into
unicode just fine as far as I recall? Or did I miss the boat here ?
Thanks,
--
http://mail.python.org/mailman/listinfo/python-list
Convertion of Unicode to ASCII NIGHTMARE
Hi
I am reading from an oracle database using cx_Oracle. I am writing to a
SQLite database using apsw.
The oracle database is returning utf-8 characters for euopean item
names, ie special charcaters from an ASCII perspective.
I get the following error:
SQLiteCur.execute(sql, row)
UnicodeDecodeError: 'ascii' codec can't decode byte 0xdc in position 12:
ordinal not in range(128)
I have googled for serval days now and still cant get it to encode to
ascii.
I encode the SQL as follows:
sql = insert into %s values %s % (SQLiteTable, paramstr)
sql.encode('ascii', 'ignore')
I then code each of the row values returned from Oracle like this:
row = map(encodestr, row)
SQLiteCur.execute(sql, row)
where encodestr is as follows:
def encodestr(item):
if isinstance(item, types.StringTypes):
return unicodedata.normalize('NFKD', unicode(item, 'utf-8',
'ignore')).encode('ASCII', 'ignore')
else:
return item
I have tried a thousand of similiar functions to the above,
permitations of the above from various google searches. But I still get
the above exception on the line:
SQLiteCur.execute(sql, row)
and the exception is reslated to the data in one field.
Int the end I resorted to using oracles convert function in the SQL
statement but would like to understand why this is happening and why
its so hard to convert the string in python. I have read many
complaints about this from other people some of whom have written
custom stripping routines. I havent tried a custom routine yet, cause I
think it should be possilble in python.
Thanks,
--
http://mail.python.org/mailman/listinfo/python-list
Grabbing a object from the current code block using a callable statement?
Hi
Is it possible to grab get an object returned from a string and a
callable ? e.g
I pass in a key value pair:
def somemethod(adict = {'new name for object': 'code to reutrn
obejct'}):
object = .
for key, value in adict.items():
if callable(value):
somedict[key] = value()
else:
somedict[key] = value
such that
somedict = {'new name for object': object}
??
Thanks,
--
http://mail.python.org/mailman/listinfo/python-list
Import statements for timeit module
Hi
I was wondering if someone could help with the import statements needed
to use the timeit module in the following code. I need to access the
cur object.
Thanks,
import cx_Oracle
import timeit
def VerifyTagIntegrity(con, TableOwner):
cur = con.cursor()
sql = 'select (select count(*) from %s.f4111) as F4111_COUNT,
(select count(*) from %s.f4111_tag) as TAG_COUNT from dual;' %
(TableOwner, TableOwner)
print SQL: %s % (sql)
timer = timeit.Timer('cur.execute(sql)', 'from __main__ import
cur')
print timer.timeit()
--
http://mail.python.org/mailman/listinfo/python-list
Re: Import statements for timeit module
So timeit is mostly useless then ? -- http://mail.python.org/mailman/listinfo/python-list
Geneator/Iterator Nesting Problem - Any Ideas? 2.4
Hi Using Python 2.4 I am trying to procduce a generator which will return the results of dbi SQL statement using fetchmany for performance. So instead of fetching one record for each call, I will fetch batches of X (eg 100) and yeild each record in turn. For reasons of pure asthetics and my own learning I wanted it to look like this: /B def resultsetbatchgen(cursor, size=100): for results in (recordbatch for recordbatch in cursor.fetchmany(size)): for rec in results: yield rec /B Problem is this this gives spurious results. To understand the problem if I excute the following in the console I get the correct results: /B cur.execute(QUERY WITH MOER THAN 1000 records) recs = (recordbatch for recordbatch in cur.fetchmany(1000)) sum(map(lambda x: 1, (rec for rec in recs))) 1000 /B This is PERFECT! 1000 is what I expected. but now lets nest this inside another for construct like so /B cur.execute(QUERY WITH MOER THAN 1000 records) for results in (recordbatch for recordbatch in cur.fetchmany(1000)): ... print sum(map(lambda x: 1, (rec for rec in results))) 76 76 76 76 76 76 76 76 ... /B Now it thinks each batch size is 76 ... ? This completly wrong, and baffling. The commands are exactly the same as far as I can tell, the only difference is that it is now nested wihtin another for loop? Any help would be greatly aprpeciated. PS a working but far less elegant version of this below but I would like to understand why teh above doesnt work and doesnt work consitantly: def resultsetbatch(cursor, size=100): results = cursor.fetchmany(size) while results []: for rec in results: yield rec results = cursor.fetchmany(size) -- http://mail.python.org/mailman/listinfo/python-list
Re: Geneator/Iterator Nesting Problem - Any Ideas? 2.4
Hi Thanks this was very helpfull. Your final solution seems like the best (most elegant). I was trying to avoid the ugly while loops with breaks and this final one certainly does that. Thanks for your help . -- http://mail.python.org/mailman/listinfo/python-list
