I have attached the code doing the write to the database
and a traceback with local variable being display for
each frame.
I am also attaching a run with nothing catching the error
so you can see the simple traceback with the exception.
Here is output from mysql show this has gone a long way
into the file before getting the exception
The code is parsing a XML tree and write records into a database
The records are being buffer and written from the code I have included.
mysql> select count(*) from bids;
+----------+
| count(*) |
+----------+
| 3312460 |
+----------+
1 row in set (0.00 sec)
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
#!/usr/bin/env python
# encoding: utf-8
"""
parsingResultsManager.py
Created by David Zaret on 2007-12-29.
Copyright (c) 2007 Symphono, Inc. All rights reserved.
"""
import operator, os, sys
from fairdeal.model import DBSession, engine
from fairdeal.prosper.parsing import mappings
from sqlalchemy.sql import text, bindparam
from datetime import datetime
from fairdeal.prosper.parsing.mappings import columnMappings, TableForTag
class WriteBuffer(object):
"""
Buffer class for write records into the database
This class buffer records to be written into the database
and writes them when max record count rearched.
Records are also flushed when object is deleted
"""
def __init__(self, table):
print "create write buffer %s" % table
self.records = []
self.table_name = table
self.table = TableForTag(table)
self.bufferMax = mappings.MaxValForSetType(table)
self.bufferMax = 4
self.statement = replace(self.table, columnMappings[self.table.name])
def write(self, record):
self.records.append(record)
if len(self.records) >= self.bufferMax:
self.flush()
def flush(self):
if not self.records:
return
print "flushing %s records: %s" % (self.table_name, len(self.records))
conn = engine.connect()
conn.execute(self.statement, self.records)
self.records = []
def replace(table, map):
statement = 'REPLACE INTO %s(%s) VALUES (%s)' % (table.name,
','.join(table.c[key].name for key in table.c.keys()),
','.join(":" + map.get(table.c[key].name, table.c[key].name)
for key in table.c.keys()))
print statement
return text(statement, bindparams=[bindparam(key, type_=table.c[key].type)
for key in table.c.keys()])
**
** FAIRDEAL PYTHON SCRIPT LOGGER - LOG BEGIN **
**
opening file for parsing...
starting parsing...
element start tag ProsperDataExport... we don't care, skipping
create write buffer Bids
REPLACE INTO
bids(LastUpdated,Exchange,Amount,ID,ListingID,ListingStatus,MemberID,MinimumRate,ParticipationAmount,Status)
VALUES
(:LastUpdated,:Exchange,:Amount,:Key,:ListingKey,:ListingStatus,:MemberKey,:MinimumRate,:ParticipationAmount,:Status)
starting parsing of a new set: Bids
flushing Bids records: 4
....
Frame write in
/home/mike/src/optalytic/trunk/src/python/FD/fairdeal/prosper/parsing/parsingResultsManager.py
at line 40
record = Bids
self =
<fairdeal.prosper.parsing.parsingResultsManager.WriteBuffer object at 0xfb8690>
Frame flush in
/home/mike/src/optalytic/trunk/src/python/FD/fairdeal/prosper/parsing/parsingResultsManager.py
at line 48
self =
<fairdeal.prosper.parsing.parsingResultsManager.WriteBuffer object at 0xfb8690>
conn = <sqlalchemy.engine.base.Connection object at
0x104c8d0>
Frame execute in
/usr/lib/python2.5/site-packages/SQLAlchemy-0.4.3-py2.5.egg/sqlalchemy/engine/base.py
at line 846
c = <class
'sqlalchemy.sql.expression.ClauseElement'>
object = REPLACE INTO
bids(LastUpdated,Exchange,Amount,ID,ListingID,ListingStatus,MemberID,MinimumRate,ParticipationAmount,Status)
VALUES
(:LastUpdated,:Exchange,:Amount,:Key,:ListingKey,:ListingStatus,:MemberKey,:MinimumRate,:ParticipationAmount,:Status)
params = {}
multiparams = ([{u'Status': u'Winning', u'CreationDate':
u'2008-01-02T23:38:04', 'Exchange': 1, u'MemberKey':
u'91703368259788903A032A0', u'ListingKey': u'71B734097662498815DDA82',
u'Amount': u'50.00', u'ParticipationAmount': u'50.00', u'ModifiedDate':
u'2008-01-02T23:38:04', u'Key': u'039735130831903594E5AB9', u'ListingStatus':
u'Active'}, {u'Status': u'Winning', u'CreationDate': u'2008-01-02T23:38:05',
'Exchange': 1, u'MemberKey': u'53613375019731860A69F04', u'ListingKey':
u'71B734097662498815DDA82', u'Amount': u'50.00', u'ParticipationAmount':
u'50.00', u'ModifiedDate': u'2008-01-02T23:38:05', u'Key':
u'1E7E3512685856476475D26', u'ListingStatus': u'Active'}, {u'Status':
u'Winning', u'CreationDate': u'2008-01-02T23:38:06', 'Exchange': 1,
u'MemberKey': u'63133403509656034D3A100', u'ListingKey':
u'71B734097662498815DDA82', u'Amount': u'50.00', u'ParticipationAmount':
u'50.00', u'ModifiedDate': u'2008-01-02T23:38:06', u'Key':
u'D2B235137737315033CD9AB', u'ListingStatus': u'Active'}, u'Bids'],)
self = <sqlalchemy.engine.base.Connection object at
0x104c8d0>
Frame execute_clauseelement in
/usr/lib/python2.5/site-packages/SQLAlchemy-0.4.3-py2.5.egg/sqlalchemy/engine/base.py
at line 897
keys = [u'Status', u'CreationDate', 'Exchange',
u'MemberKey', u'ListingKey', u'Amount', u'ParticipationAmount',
u'ModifiedDate', u'Key', u'ListingStatus']
multiparams = ([{u'Status': u'Winning', u'CreationDate':
u'2008-01-02T23:38:04', 'Exchange': 1, u'MemberKey':
u'91703368259788903A032A0', u'ListingKey': u'71B734097662498815DDA82',
u'Amount': u'50.00', u'ParticipationAmount': u'50.00', u'ModifiedDate':
u'2008-01-02T23:38:04', u'Key': u'039735130831903594E5AB9', u'ListingStatus':
u'Active'}, {u'Status': u'Winning', u'CreationDate': u'2008-01-02T23:38:05',
'Exchange': 1, u'MemberKey': u'53613375019731860A69F04', u'ListingKey':
u'71B734097662498815DDA82', u'Amount': u'50.00', u'ParticipationAmount':
u'50.00', u'ModifiedDate': u'2008-01-02T23:38:05', u'Key':
u'1E7E3512685856476475D26', u'ListingStatus': u'Active'}, {u'Status':
u'Winning', u'CreationDate': u'2008-01-02T23:38:06', 'Exchange': 1,
u'MemberKey': u'63133403509656034D3A100', u'ListingKey':
u'71B734097662498815DDA82', u'Amount': u'50.00', u'ParticipationAmount':
u'50.00', u'ModifiedDate': u'2008-01-02T23:38:06', u'Key':
u'D2B235137737315033CD9AB', u'ListingStatus': u'Active'}, u'Bids'],)
params = [{u'Status': u'Winning', u'CreationDate':
u'2008-01-02T23:38:04', 'Exchange': 1, u'MemberKey':
u'91703368259788903A032A0', u'ListingKey': u'71B734097662498815DDA82',
u'Amount': u'50.00', u'ParticipationAmount': u'50.00', u'ModifiedDate':
u'2008-01-02T23:38:04', u'Key': u'039735130831903594E5AB9', u'ListingStatus':
u'Active'}, {u'Status': u'Winning', u'CreationDate': u'2008-01-02T23:38:05',
'Exchange': 1, u'MemberKey': u'53613375019731860A69F04', u'ListingKey':
u'71B734097662498815DDA82', u'Amount': u'50.00', u'ParticipationAmount':
u'50.00', u'ModifiedDate': u'2008-01-02T23:38:05', u'Key':
u'1E7E3512685856476475D26', u'ListingStatus': u'Active'}, {u'Status':
u'Winning', u'CreationDate': u'2008-01-02T23:38:06', 'Exchange': 1,
u'MemberKey': u'63133403509656034D3A100', u'ListingKey':
u'71B734097662498815DDA82', u'Amount': u'50.00', u'ParticipationAmount':
u'50.00', u'ModifiedDate': u'2008-01-02T23:38:06', u'Key':
u'D2B235137737315033CD9AB', u'ListingStatus': u'Active'}, u'Bids']
elem = REPLACE INTO
bids(LastUpdated,Exchange,Amount,ID,ListingID,ListingStatus,MemberID,MinimumRate,ParticipationAmount,Status)
VALUES
(:LastUpdated,:Exchange,:Amount,:Key,:ListingKey,:ListingStatus,:MemberKey,:MinimumRate,:ParticipationAmount,:Status)
self = <sqlalchemy.engine.base.Connection object at
0x104c8d0>
Frame _execute_compiled in
/usr/lib/python2.5/site-packages/SQLAlchemy-0.4.3-py2.5.egg/sqlalchemy/engine/base.py
at line 906
compiled = REPLACE INTO
bids(LastUpdated,Exchange,Amount,ID,ListingID,ListingStatus,MemberID,MinimumRate,ParticipationAmount,Status)
VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
multiparams = None
params = None
self = <sqlalchemy.engine.base.Connection object at
0x104c8d0>
distilled_params = [{u'Status': u'Winning', u'CreationDate':
u'2008-01-02T23:38:04', 'Exchange': 1, u'MemberKey':
u'91703368259788903A032A0', u'ListingKey': u'71B734097662498815DDA82',
u'Amount': u'50.00', u'ParticipationAmount': u'50.00', u'ModifiedDate':
u'2008-01-02T23:38:04', u'Key': u'039735130831903594E5AB9', u'ListingStatus':
u'Active'}, {u'Status': u'Winning', u'CreationDate': u'2008-01-02T23:38:05',
'Exchange': 1, u'MemberKey': u'53613375019731860A69F04', u'ListingKey':
u'71B734097662498815DDA82', u'Amount': u'50.00', u'ParticipationAmount':
u'50.00', u'ModifiedDate': u'2008-01-02T23:38:05', u'Key':
u'1E7E3512685856476475D26', u'ListingStatus': u'Active'}, {u'Status':
u'Winning', u'CreationDate': u'2008-01-02T23:38:06', 'Exchange': 1,
u'MemberKey': u'63133403509656034D3A100', u'ListingKey':
u'71B734097662498815DDA82', u'Amount': u'50.00', u'ParticipationAmount':
u'50.00', u'ModifiedDate': u'2008-01-02T23:38:06', u'Key':
u'D2B235137737315033CD9AB', u'ListingStatus': u'Active'}, u'Bids']
Frame __create_execution_context in
/usr/lib/python2.5/site-packages/SQLAlchemy-0.4.3-py2.5.egg/sqlalchemy/engine/base.py
at line 953
self = <sqlalchemy.engine.base.Connection object at
0x104c8d0>
e = 'in <string>' requires string as left operand
kwargs = {'compiled':
<sqlalchemy.databases.mysql.MySQLCompiler object at 0x104c610>, 'parameters':
[{u'Status': u'Winning', u'CreationDate': u'2008-01-02T23:38:04', 'Exchange':
1, u'MemberKey': u'91703368259788903A032A0', u'ListingKey':
u'71B734097662498815DDA82', u'Amount': u'50.00', u'ParticipationAmount':
u'50.00', u'ModifiedDate': u'2008-01-02T23:38:04', u'Key':
u'039735130831903594E5AB9', u'ListingStatus': u'Active'}, {u'Status':
u'Winning', u'CreationDate': u'2008-01-02T23:38:05', 'Exchange': 1,
u'MemberKey': u'53613375019731860A69F04', u'ListingKey':
u'71B734097662498815DDA82', u'Amount': u'50.00', u'ParticipationAmount':
u'50.00', u'ModifiedDate': u'2008-01-02T23:38:05', u'Key':
u'1E7E3512685856476475D26', u'ListingStatus': u'Active'}, {u'Status':
u'Winning', u'CreationDate': u'2008-01-02T23:38:06', 'Exchange': 1,
u'MemberKey': u'63133403509656034D3A100', u'ListingKey':
u'71B734097662498815DDA82', u'Amount': u'50.00', u'ParticipationAmount':
u'50.00', u'ModifiedDate': u'2008-01-02T23:38:06', u'Key':
u'D2B235137737315033CD9AB', u'ListingStatus': u'Active'}, u'Bids']}
Frame create_execution_context in
/usr/lib/python2.5/site-packages/SQLAlchemy-0.4.3-py2.5.egg/sqlalchemy/databases/mysql.py
at line 1464
connection = <sqlalchemy.engine.base.Connection object at
0x104c8d0>
self = <sqlalchemy.databases.mysql.MySQLDialect object
at 0xc82d10>
kwargs = {'compiled':
<sqlalchemy.databases.mysql.MySQLCompiler object at 0x104c610>, 'parameters':
[{u'Status': u'Winning', u'CreationDate': u'2008-01-02T23:38:04', 'Exchange':
1, u'MemberKey': u'91703368259788903A032A0', u'ListingKey':
u'71B734097662498815DDA82', u'Amount': u'50.00', u'ParticipationAmount':
u'50.00', u'ModifiedDate': u'2008-01-02T23:38:04', u'Key':
u'039735130831903594E5AB9', u'ListingStatus': u'Active'}, {u'Status':
u'Winning', u'CreationDate': u'2008-01-02T23:38:05', 'Exchange': 1,
u'MemberKey': u'53613375019731860A69F04', u'ListingKey':
u'71B734097662498815DDA82', u'Amount': u'50.00', u'ParticipationAmount':
u'50.00', u'ModifiedDate': u'2008-01-02T23:38:05', u'Key':
u'1E7E3512685856476475D26', u'ListingStatus': u'Active'}, {u'Status':
u'Winning', u'CreationDate': u'2008-01-02T23:38:06', 'Exchange': 1,
u'MemberKey': u'63133403509656034D3A100', u'ListingKey':
u'71B734097662498815DDA82', u'Amount': u'50.00', u'ParticipationAmount':
u'50.00', u'ModifiedDate': u'2008-01-02T23:38:06', u'Key':
u'D2B235137737315033CD9AB', u'ListingStatus': u'Active'}, u'Bids']}
Frame __init__ in
/usr/lib/python2.5/site-packages/SQLAlchemy-0.4.3-py2.5.egg/sqlalchemy/engine/default.py
at line 175
dialect = <sqlalchemy.databases.mysql.MySQLDialect object
at 0xc82d10>
statement = None
parameters = [{u'Status': u'Winning', u'CreationDate':
u'2008-01-02T23:38:04', 'Exchange': 1, u'MemberKey':
u'91703368259788903A032A0', u'ListingKey': u'71B734097662498815DDA82',
u'Amount': u'50.00', u'ParticipationAmount': u'50.00', u'ModifiedDate':
u'2008-01-02T23:38:04', u'Key': u'039735130831903594E5AB9', u'ListingStatus':
u'Active'}, {u'Status': u'Winning', u'CreationDate': u'2008-01-02T23:38:05',
'Exchange': 1, u'MemberKey': u'53613375019731860A69F04', u'ListingKey':
u'71B734097662498815DDA82', u'Amount': u'50.00', u'ParticipationAmount':
u'50.00', u'ModifiedDate': u'2008-01-02T23:38:05', u'Key':
u'1E7E3512685856476475D26', u'ListingStatus': u'Active'}, {u'Status':
u'Winning', u'CreationDate': u'2008-01-02T23:38:06', 'Exchange': 1,
u'MemberKey': u'63133403509656034D3A100', u'ListingKey':
u'71B734097662498815DDA82', u'Amount': u'50.00', u'ParticipationAmount':
u'50.00', u'ModifiedDate': u'2008-01-02T23:38:06', u'Key':
u'D2B235137737315033CD9AB', u'ListingStatus': u'Active'}, u'Bids']
compiled = REPLACE INTO
bids(LastUpdated,Exchange,Amount,ID,ListingID,ListingStatus,MemberID,MinimumRate,ParticipationAmount,Status)
VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
self =
<sqlalchemy.databases.mysql.MySQLExecutionContext object at 0x104c710>
m = Bids
value = None
connection = <sqlalchemy.engine.base.Connection object at
0x104c8d0>
bindparam = :ParticipationAmount
key = ParticipationAmount
_[3] = [{'Status': u'Winning', 'Exchange': 1,
'MemberKey': u'91703368259788903A032A0', 'LastUpdated': None, 'ListingKey':
u'71B734097662498815DDA82', 'MinimumRate': None, 'Amount': u'50.00',
'ParticipationAmount': u'50.00', 'Key': u'039735130831903594E5AB9',
'ListingStatus': u'Active'}, {'Status': u'Winning', 'Exchange': 1, 'MemberKey':
u'53613375019731860A69F04', 'LastUpdated': None, 'ListingKey':
u'71B734097662498815DDA82', 'MinimumRate': None, 'Amount': u'50.00',
'ParticipationAmount': u'50.00', 'Key': u'1E7E3512685856476475D26',
'ListingStatus': u'Active'}, {'Status': u'Winning', 'Exchange': 1, 'MemberKey':
u'63133403509656034D3A100', 'LastUpdated': None, 'ListingKey':
u'71B734097662498815DDA82', 'MinimumRate': None, 'Amount': u'50.00',
'ParticipationAmount': u'50.00', 'Key': u'D2B235137737315033CD9AB',
'ListingStatus': u'Active'}]
Frame construct_params in
/usr/lib/python2.5/site-packages/SQLAlchemy-0.4.3-py2.5.egg/sqlalchemy/sql/compiler.py
at line 212
name = Status
self = REPLACE INTO
bids(LastUpdated,Exchange,Amount,ID,ListingID,ListingStatus,MemberID,MinimumRate,ParticipationAmount,Status)
VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
params = Bids
bindparam = :Status
pd = {}
paramname = :Status
Traceback (most recent call last):
File "makedb.py", line 25, in <module>
run()
File "makedb.py", line 21, in run
p.parser.parse(infile)
File "/usr/lib/python2.5/site-packages/_xmlplus/sax/expatreader.py", line
109, in parse
xmlreader.IncrementalParser.parse(self, source)
File "/usr/lib/python2.5/site-packages/_xmlplus/sax/xmlreader.py", line 123,
in parse
self.feed(buffer)
File "/usr/lib/python2.5/site-packages/_xmlplus/sax/expatreader.py", line
216, in feed
self._parser.Parse(data, isFinal)
File "/usr/lib/python2.5/site-packages/_xmlplus/sax/expatreader.py", line
315, in end_element
self._cont_handler.endElement(name)
File
"/home/mike/src/optalytic/trunk/src/python/FD/fairdeal/prosper/parsing/prosperContentHandler.py",
line 79, in endElement
self.buffer.flush()
File
"/home/mike/src/optalytic/trunk/src/python/FD/fairdeal/prosper/parsing/parsingResultsManager.py",
line 48, in flush
conn.execute(self.statement, self.records)
File
"/usr/lib/python2.5/site-packages/SQLAlchemy-0.4.3-py2.5.egg/sqlalchemy/engine/base.py",
line 846, in execute
return Connection.executors[c](self, object, multiparams, params)
File
"/usr/lib/python2.5/site-packages/SQLAlchemy-0.4.3-py2.5.egg/sqlalchemy/engine/base.py",
line 897, in execute_clauseelement
return self._execute_compiled(elem.compile(dialect=self.dialect,
column_keys=keys, inline=len(params) > 1), distilled_params=params)
File
"/usr/lib/python2.5/site-packages/SQLAlchemy-0.4.3-py2.5.egg/sqlalchemy/engine/base.py",
line 906, in _execute_compiled
context = self.__create_execution_context(compiled=compiled,
parameters=distilled_params)
File
"/usr/lib/python2.5/site-packages/SQLAlchemy-0.4.3-py2.5.egg/sqlalchemy/engine/base.py",
line 950, in __create_execution_context
return self.engine.dialect.create_execution_context(connection=self,
**kwargs)
File
"/usr/lib/python2.5/site-packages/SQLAlchemy-0.4.3-py2.5.egg/sqlalchemy/databases/mysql.py",
line 1464, in create_execution_context
return MySQLExecutionContext(self, connection, **kwargs)
File
"/usr/lib/python2.5/site-packages/SQLAlchemy-0.4.3-py2.5.egg/sqlalchemy/engine/default.py",
line 175, in __init__
self.compiled_parameters = [compiled.construct_params(m) for m in
parameters]
File
"/usr/lib/python2.5/site-packages/SQLAlchemy-0.4.3-py2.5.egg/sqlalchemy/sql/compiler.py",
line 212, in construct_params
if paramname in params:
TypeError: 'in <string>' requires string as left operand
