Re: [sqlalchemy] Error on insert, warning from mysqldb

2011-12-18 Thread Lee Hinde 
Thanks, Michael. I found it the one place I hadn't looked 12 times. :-)

On Dec 18, 2011, at 10:51 AM, Michael Bayer wrote:

> 
> On Dec 18, 2011, at 1:25 PM, Lee Hinde wrote:
> 
>> I'm brand new to sqlalchemy,  and reasonably new to python. I'm working on 
>> migrating data from one MySQL database to another at Amazon's RDS. 
>> 
>> I have one TinyInt field,'Error' and if the value in that field is 1, then I 
>> get a warning from MySQLdb : 
>> 
>> /Library/Python/2.7/site-packages/MySQL_python-1.2.3-py2.7-macosx-10.7-intel.egg/MySQLdb/cursors.py:206:
>>  Warning: Incorrect integer value: 'appuser_id' for column 'Error' at row 1
>> /Library/Python/2.7/site-packages/MySQL_python-1.2.3-py2.7-macosx-10.7-intel.egg/MySQLdb/cursors.py:206:
>>  Warning: Incorrect integer value: '' for column 'Error' at row 1
>> /Library/Python/2.7/site-packages/MySQL_python-1.2.3-py2.7-macosx-10.7-intel.egg/MySQLdb/cursors.py:206:
>>  Warning: Incorrect integer value: 'TEST-USER-WITH-FULL-PREFS-AND-FAVES' for 
>> column 'Error' at row 1
>> 
>> The incorrect value being reported is from the varchar User_ID field.  
>> Everything but the Error field gets migrated fine, including the User_ID 
>> field which this warning would suggest is in the wrong column. If Error == 
>> 0, no warning is issued.
> 
> This means you're assigning a string value to an integer field in a 
> statement.   When you do your thing with wrl(...fields...), make sure all the 
> fields match up to their types/names correctly.
> 
> you can also put a @validates to check for it:
> 
> class MyClass(Base):
>...
> 
>   @validates('Error')
>   def check_for_int(self, key, value):
>if not isinstance(value, int):
>raise TypeError("integer expected here")
>return value
> 
> see http://www.sqlalchemy.org/docs/orm/mapper_config.html#simple-validators
> 
> 
> 
> 
> 
> 
>> 
>> Any pointers appreciated.
>> 
>> The structures of the sending and receiving tables are identical. As 
>> reported by sqlalchemy's reflection tool:
>> 
>> UUID_PK VARCHAR(36)
>> Web_Request_Headers TEXT
>> Web_Request_Body TEXT
>> Current_Machine VARCHAR(40)
>> HTTP_StatusSent INTEGER(11)
>> ResponseBody MEDIUMTEXT
>> Full_Log_Message TEXT
>> Remote_Address VARCHAR(60)
>> basic_auth_username VARCHAR(30)
>> Request_Method VARCHAR(12)
>> Request_URI VARCHAR(60)
>> Request_Protocol VARCHAR(12)
>> Time_To_Process_Request INTEGER(11)
>> User_ID VARCHAR(36)
>> Error TINYINT(4)
>> Added_Timestamp VARCHAR(16)
>> Processing_Time_Milliseconds INTEGER(11)
>> mysql_timestamp TIMESTAMP
>> 
>> I have this model.py:
>> 
>> from sqlalchemy.ext.declarative import declarative_base
>> 
>> Base = declarative_base()
>> 
>> from sqlalchemy import Column, Integer, String, Text, DateTime, SmallInteger
>> 
>> class wrl(Base):
>>   __tablename__ = 'web_request_log'
>>   UUID_PK = Column(String(36),primary_key=True)
>>   Web_Request_Headers =  Column(Text)
>>   Web_Request_Body  =  Column(Text)
>>   Current_Machine = Column(String(40))
>>   HTTP_StatusSent  = Column(Integer)
>>   ResponseBody = Column(Text)
>>   Full_Log_Message = Column(Text)
>>   Remote_Address = Column(String(60))
>>   basic_auth_username  = Column(String(30))
>>   Request_Method  = Column(String(12))
>>   Request_URI  = Column(String(60))
>>   Request_Protocol  = Column(String(12))
>>   Time_To_Process_Request  = Column(Integer)
>>   User_ID  = Column(String(36))
>>   Error  = Column(SmallInteger)
>>   Added_Timestamp  = Column(String(16))
>>   Processing_Time_Milliseconds = Column(Integer)
>>   mysql_timestamp  = Column(DateTime)
>> 
>> 
>>   def 
>> __init__(self,UUID_PK,Web_Request_Headers,Web_Request_Body,Current_Machine,HTTP_StatusSent,ResponseBody,Full_Log_Message,
>>  Remote_Address,
>>   
>> basic_auth_username,Request_Method,Request_URI,Request_Protocol,Time_To_Process_Request,User_ID,Error,Added_Timestamp,Processing_Time_Milliseconds,
>>   mysql_timestamp):
>>self.UUID_PK = UUID_PK
>>self.Web_Request_Headers = Web_Request_Headers
>>self.Web_Request_Body = Web_Request_Body
>>self.Current_Machine = Current_Machine
>>self.HTTP_StatusSent = HTTP_StatusSent
>>self.ResponseBody = ResponseBody
>>self.Full_Log_Message = Full_Log_Message
>>self.Remote_Address = Remote_Address
>>self.basic_auth_username = basic_auth_username
>>self.Request_Method = Request_Method
>>self.Request_URI = Request_URI
>>self.Request_Protocol = Request_Protocol
>>self.Time_To_Process_Request = Time_To_Process_Request
>>self.User_ID = User_ID
>>self.Error = User_ID
>>self.Added_Timestamp = Added_Timestamp
>>self.Processing_Time_Milliseconds = Processing_Time_Milliseconds
>>self.mysql_timestamp = mysql_timestamp  
>> 
>> 
>> And I have this method:  
>> 
>> #login and query stuff, which is working fine, removed;
>> 
>> for x in move_me:
>>   wrl_rec = wrl(x.UUID_PK,
>>

Re: [sqlalchemy] Error on insert, warning from mysqldb

2011-12-18 Thread Michael Bayer 

On Dec 18, 2011, at 1:25 PM, Lee Hinde wrote:

> I'm brand new to sqlalchemy,  and reasonably new to python. I'm working on 
> migrating data from one MySQL database to another at Amazon's RDS. 
> 
> I have one TinyInt field,'Error' and if the value in that field is 1, then I 
> get a warning from MySQLdb : 
> 
> /Library/Python/2.7/site-packages/MySQL_python-1.2.3-py2.7-macosx-10.7-intel.egg/MySQLdb/cursors.py:206:
>  Warning: Incorrect integer value: 'appuser_id' for column 'Error' at row 1
> /Library/Python/2.7/site-packages/MySQL_python-1.2.3-py2.7-macosx-10.7-intel.egg/MySQLdb/cursors.py:206:
>  Warning: Incorrect integer value: '' for column 'Error' at row 1
> /Library/Python/2.7/site-packages/MySQL_python-1.2.3-py2.7-macosx-10.7-intel.egg/MySQLdb/cursors.py:206:
>  Warning: Incorrect integer value: 'TEST-USER-WITH-FULL-PREFS-AND-FAVES' for 
> column 'Error' at row 1
> 
> The incorrect value being reported is from the varchar User_ID field.  
> Everything but the Error field gets migrated fine, including the User_ID 
> field which this warning would suggest is in the wrong column. If Error == 0, 
> no warning is issued.

This means you're assigning a string value to an integer field in a statement.  
 When you do your thing with wrl(...fields...), make sure all the fields match 
up to their types/names correctly.

you can also put a @validates to check for it:

class MyClass(Base):
...

   @validates('Error')
   def check_for_int(self, key, value):
if not isinstance(value, int):
raise TypeError("integer expected here")
return value

see http://www.sqlalchemy.org/docs/orm/mapper_config.html#simple-validators






> 
> Any pointers appreciated.
> 
> The structures of the sending and receiving tables are identical. As reported 
> by sqlalchemy's reflection tool:
> 
> UUID_PK VARCHAR(36)
> Web_Request_Headers TEXT
> Web_Request_Body TEXT
> Current_Machine VARCHAR(40)
> HTTP_StatusSent INTEGER(11)
> ResponseBody MEDIUMTEXT
> Full_Log_Message TEXT
> Remote_Address VARCHAR(60)
> basic_auth_username VARCHAR(30)
> Request_Method VARCHAR(12)
> Request_URI VARCHAR(60)
> Request_Protocol VARCHAR(12)
> Time_To_Process_Request INTEGER(11)
> User_ID VARCHAR(36)
> Error TINYINT(4)
> Added_Timestamp VARCHAR(16)
> Processing_Time_Milliseconds INTEGER(11)
> mysql_timestamp TIMESTAMP
> 
> I have this model.py:
> 
> from sqlalchemy.ext.declarative import declarative_base
> 
> Base = declarative_base()
> 
> from sqlalchemy import Column, Integer, String, Text, DateTime, SmallInteger
> 
> class wrl(Base):
>__tablename__ = 'web_request_log'
>UUID_PK = Column(String(36),primary_key=True)
>Web_Request_Headers =  Column(Text)
>Web_Request_Body  =  Column(Text)
>Current_Machine = Column(String(40))
>HTTP_StatusSent  = Column(Integer)
>ResponseBody = Column(Text)
>Full_Log_Message = Column(Text)
>Remote_Address = Column(String(60))
>basic_auth_username  = Column(String(30))
>Request_Method  = Column(String(12))
>Request_URI  = Column(String(60))
>Request_Protocol  = Column(String(12))
>Time_To_Process_Request  = Column(Integer)
>User_ID  = Column(String(36))
>Error  = Column(SmallInteger)
>Added_Timestamp  = Column(String(16))
>Processing_Time_Milliseconds = Column(Integer)
>mysql_timestamp  = Column(DateTime)
> 
> 
>def 
> __init__(self,UUID_PK,Web_Request_Headers,Web_Request_Body,Current_Machine,HTTP_StatusSent,ResponseBody,Full_Log_Message,
>  Remote_Address,
>
> basic_auth_username,Request_Method,Request_URI,Request_Protocol,Time_To_Process_Request,User_ID,Error,Added_Timestamp,Processing_Time_Milliseconds,
>mysql_timestamp):
> self.UUID_PK = UUID_PK
> self.Web_Request_Headers = Web_Request_Headers
> self.Web_Request_Body = Web_Request_Body
> self.Current_Machine = Current_Machine
> self.HTTP_StatusSent = HTTP_StatusSent
> self.ResponseBody = ResponseBody
> self.Full_Log_Message = Full_Log_Message
> self.Remote_Address = Remote_Address
> self.basic_auth_username = basic_auth_username
> self.Request_Method = Request_Method
> self.Request_URI = Request_URI
> self.Request_Protocol = Request_Protocol
> self.Time_To_Process_Request = Time_To_Process_Request
> self.User_ID = User_ID
> self.Error = User_ID
> self.Added_Timestamp = Added_Timestamp
> self.Processing_Time_Milliseconds = Processing_Time_Milliseconds
> self.mysql_timestamp = mysql_timestamp  
> 
> 
> And I have this method:  
> 
> #login and query stuff, which is working fine, removed;
> 
> for x in move_me:
>wrl_rec = wrl(x.UUID_PK,
>x.Web_Request_Headers,
>x.Web_Request_Body,
>x.Current_Machine,
>x.HTTP_StatusSent,
>x.ResponseBody,
>x.Full_Log_Message,
>x.Re

[sqlalchemy] Error on insert, warning from mysqldb

2011-12-18 Thread Lee Hinde 
I'm brand new to sqlalchemy,  and reasonably new to python. I'm working on 
migrating data from one MySQL database to another at Amazon's RDS. 

I have one TinyInt field,'Error' and if the value in that field is 1, then I 
get a warning from MySQLdb : 

/Library/Python/2.7/site-packages/MySQL_python-1.2.3-py2.7-macosx-10.7-intel.egg/MySQLdb/cursors.py:206:
 Warning: Incorrect integer value: 'appuser_id' for column 'Error' at row 1
/Library/Python/2.7/site-packages/MySQL_python-1.2.3-py2.7-macosx-10.7-intel.egg/MySQLdb/cursors.py:206:
 Warning: Incorrect integer value: '' for column 'Error' at row 1
/Library/Python/2.7/site-packages/MySQL_python-1.2.3-py2.7-macosx-10.7-intel.egg/MySQLdb/cursors.py:206:
 Warning: Incorrect integer value: 'TEST-USER-WITH-FULL-PREFS-AND-FAVES' for 
column 'Error' at row 1

The incorrect value being reported is from the varchar User_ID field.  
Everything but the Error field gets migrated fine, including the User_ID field 
which this warning would suggest is in the wrong column. If Error == 0, no 
warning is issued.

Any pointers appreciated.

The structures of the sending and receiving tables are identical. As reported 
by sqlalchemy's reflection tool:

UUID_PK VARCHAR(36)
Web_Request_Headers TEXT
Web_Request_Body TEXT
Current_Machine VARCHAR(40)
HTTP_StatusSent INTEGER(11)
ResponseBody MEDIUMTEXT
Full_Log_Message TEXT
Remote_Address VARCHAR(60)
basic_auth_username VARCHAR(30)
Request_Method VARCHAR(12)
Request_URI VARCHAR(60)
Request_Protocol VARCHAR(12)
Time_To_Process_Request INTEGER(11)
User_ID VARCHAR(36)
Error TINYINT(4)
Added_Timestamp VARCHAR(16)
Processing_Time_Milliseconds INTEGER(11)
mysql_timestamp TIMESTAMP

I have this model.py:

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

from sqlalchemy import Column, Integer, String, Text, DateTime, SmallInteger

class wrl(Base):
__tablename__ = 'web_request_log'
UUID_PK = Column(String(36),primary_key=True)
Web_Request_Headers =  Column(Text)
Web_Request_Body  =  Column(Text)
Current_Machine = Column(String(40))
HTTP_StatusSent  = Column(Integer)
ResponseBody = Column(Text)
Full_Log_Message = Column(Text)
Remote_Address = Column(String(60))
basic_auth_username  = Column(String(30))
Request_Method  = Column(String(12))
Request_URI  = Column(String(60))
Request_Protocol  = Column(String(12))
Time_To_Process_Request  = Column(Integer)
User_ID  = Column(String(36))
Error  = Column(SmallInteger)
Added_Timestamp  = Column(String(16))
Processing_Time_Milliseconds = Column(Integer)
mysql_timestamp  = Column(DateTime)


def 
__init__(self,UUID_PK,Web_Request_Headers,Web_Request_Body,Current_Machine,HTTP_StatusSent,ResponseBody,Full_Log_Message,
 Remote_Address,

basic_auth_username,Request_Method,Request_URI,Request_Protocol,Time_To_Process_Request,User_ID,Error,Added_Timestamp,Processing_Time_Milliseconds,
mysql_timestamp):
 self.UUID_PK = UUID_PK
 self.Web_Request_Headers = Web_Request_Headers
 self.Web_Request_Body = Web_Request_Body
 self.Current_Machine = Current_Machine
 self.HTTP_StatusSent = HTTP_StatusSent
 self.ResponseBody = ResponseBody
 self.Full_Log_Message = Full_Log_Message
 self.Remote_Address = Remote_Address
 self.basic_auth_username = basic_auth_username
 self.Request_Method = Request_Method
 self.Request_URI = Request_URI
 self.Request_Protocol = Request_Protocol
 self.Time_To_Process_Request = Time_To_Process_Request
 self.User_ID = User_ID
 self.Error = User_ID
 self.Added_Timestamp = Added_Timestamp
 self.Processing_Time_Milliseconds = Processing_Time_Milliseconds
 self.mysql_timestamp = mysql_timestamp  


And I have this method:  

#login and query stuff, which is working fine, removed;

for x in move_me:
wrl_rec = wrl(x.UUID_PK,
x.Web_Request_Headers,
x.Web_Request_Body,
x.Current_Machine,
x.HTTP_StatusSent,
x.ResponseBody,
x.Full_Log_Message,
x.Remote_Address,
x.basic_auth_username,
x.Request_Method,
x.Request_URI,
x.Request_Protocol,
x.Time_To_Process_Request,
x.User_ID,
x.Error,
x.Added_Timestamp,
x.Processing_Time_Milliseconds,
x.mysql_timestamp)
try:
aws.add(wrl_rec)
print 'added %s' %  x.UUID_PK
except Exception, E:
print 'error %s' %  x.UUID_PK
print E
else:
rs.delete(x)
try:
aws.commit()  # if we can commit the aws push, then it's ok to commit the 
delete on RS
except Exception, E:
print 'error %s' %  E
else:
rs.commit()





-- 
You received this messa