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