Thread: [SQLObject] Error writing binary data to a blob
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Aaron R. <aar...@mo...> - 2009-07-23 05:52:45
|
Hi All, I'm new to SQLObject, but have picked through the documentation as much as possible and done numerous searches and am still at a loss... I'm wanting to write some binary data to the database - specifically the data is produced by something like: array.array('H',[1,2,3]).tostring() and the database is Postgres 8.3 with a sqlobject.BLOBCol(default=None) column. Currently when I try this I receive this error: DataError: invalid byte sequence for encoding "UTF8": 0x83 HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding". I ran into a vaguely similar post, and the suggestion was to useUnicodeCol(), which I have tried and received the same result. Basically the main focus here is on keeping the storage size in the DB as low as possible, as we want to store many groups of numbers (all below 65536), and there is no need (or desire) to store them in individual columns. Thanks in advance, -Aaron. |
From: Oleg B. <ph...@ph...> - 2009-07-23 09:45:15
|
On Thu, Jul 23, 2009 at 04:54:11PM +1200, Aaron Robinson wrote: > I'm wanting to write some binary data to the database - specifically the > data is produced by something like: > array.array('H',[1,2,3]).tostring() > and the database is Postgres 8.3 with a sqlobject.BLOBCol(default=None) > column. > > Currently when I try this I receive this error: > DataError: invalid byte sequence for encoding "UTF8": 0x83 > HINT: This error can also happen if the byte sequence does not match > the encoding expected by the server, which is controlled by > "client_encoding". Ouch. Can you write a short test program that demonstrates the problem? What version of SQLObject do you use? what are the encodings of client and server? > I ran into a vaguely similar post, and the suggestion was to > useUnicodeCol(), which I have tried and received the same result. UnicodeCol is for text, not binary data. > Basically the main focus here is on keeping the storage size in the DB as > low as possible, as we want to store many groups of numbers (all below > 65536), and there is no need (or desire) to store them in individual > columns. There is also PickleCol - a subtype of BLOBCol that can store any pickleable data - the column (un)pickles data as necessary. A problem with the column (and your approach with array.tostring) is that you cannot search or sort results by values in these columns. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Aaron R. <aar...@mo...> - 2009-07-24 00:31:15
|
Hi Oleg, Thanks for your quick response. In writing a short test program I think I may have come across the problem, although again I'm not familiar enough with SQLObject to know where to from here... It seems that if I set the value of a blob column by setting a varialbe on my object, it gets written to the db just fine, but when I do it inside a transaction using sqlbuilder I have the error. Please see the code below. Also, I'm at a complete loss as to what the server/client encodings are or even what version of sqlobject I'm using (it could be a couple of years old)... Obviously (based on the test program) I'm not setting the client encoding to anything other than the default... I've done a standard install of Postgres 8.3 and just went with the defaults there too... and I've had a look through the sqlobject code and can't find a version number anywhere. Could you give me a hint about where to look if this is important? Test Code: from sqlobject import * from array import * sqlhub.processConnection = connectionForURI('postgres://user:pass@localhost /mydb') class MyObject(SQLObject): myInt = IntCol(default=42) myBlob = BLOBCol(default=None) MyObject.createTable() #This works: myObject = MyObject() myObject.myInt = 43 myObject.myBlob = array('H',range(256)).tostring() #This doesn't work: record = {'my_int':44, 'my_blob':array('H',range(256)).tostring()} conn = sqlhub.getConnection() tx = conn.transaction() tx.query(conn.sqlrepr(sqlbuilder.Insert(MyObject, [record]))) tx.commit() #Traceback (most recent call last): # File "E:\SSL\Biometix\Code\Performix\trunk\demoBinaryAddError.py", line 28, in ? # tx.query(conn.sqlrepr(sqlbuilder.Insert(MyObject, [record]))) # File "E:\SSL\Biometix\Code\Performix\trunk\sqlobject\dbconnection.py", line 846, in query # return self._dbConnection._query(self._connection, s) # File "E:\SSL\Biometix\Code\Performix\trunk\sqlobject\dbconnection.py", line 339, in _query # self._executeRetry(conn, conn.cursor(), s) # File "E:\SSL\Biometix\Code\Performix\trunk\sqlobject\dbconnection.py", line 334, in _executeRetry # return cursor.execute(query) #psycopg2.DataError: invalid byte sequence for encoding "UTF8": 0x80 #HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding". On Thu, Jul 23, 2009 at 9:45 PM, Oleg Broytmann <ph...@ph...> wrote: > On Thu, Jul 23, 2009 at 04:54:11PM +1200, Aaron Robinson wrote: > > I'm wanting to write some binary data to the database - specifically the > > data is produced by something like: > > array.array('H',[1,2,3]).tostring() > > and the database is Postgres 8.3 with a sqlobject.BLOBCol(default=None) > > column. > > > > Currently when I try this I receive this error: > > DataError: invalid byte sequence for encoding "UTF8": 0x83 > > HINT: This error can also happen if the byte sequence does not match > > the encoding expected by the server, which is controlled by > > "client_encoding". > > Ouch. Can you write a short test program that demonstrates the problem? > What version of SQLObject do you use? what are the encodings of client and > server? > > > I ran into a vaguely similar post, and the suggestion was to > > useUnicodeCol(), which I have tried and received the same result. > > UnicodeCol is for text, not binary data. > > > Basically the main focus here is on keeping the storage size in the DB as > > low as possible, as we want to store many groups of numbers (all below > > 65536), and there is no need (or desire) to store them in individual > > columns. > > There is also PickleCol - a subtype of BLOBCol that can store any > pickleable data - the column (un)pickles data as necessary. > A problem with the column (and your approach with array.tostring) is > that you cannot search or sort results by values in these columns. > > Oleg. > -- > Oleg Broytmann http://phd.pp.ru/ ph...@ph... > Programmers don't die, they just GOSUB without RETURN. > > > ------------------------------------------------------------------------------ > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss > |
From: Oleg B. <ph...@ph...> - 2009-07-24 07:59:53
|
On Fri, Jul 24, 2009 at 12:31:02PM +1200, Aaron Robinson wrote: > if I set the value of a blob column by setting a > varialbe on my object, it gets written to the db just fine, but when I do it > inside a transaction using sqlbuilder I have the error. Please see the code > below. > > Test Code: > > from sqlobject import * > from array import * > > sqlhub.processConnection = connectionForURI('postgres://user:pass@localhost > /mydb') > > class MyObject(SQLObject): > myInt = IntCol(default=42) > myBlob = BLOBCol(default=None) > > MyObject.createTable() > > #This works: > myObject = MyObject() > myObject.myInt = 43 > myObject.myBlob = array('H',range(256)).tostring() I hope you know you can set many values at once: myObject.set(myInt=43, myBlob=array('H',range(256)).tostring()) (That's offtopic, just a reminder.) > #This doesn't work: > record = {'my_int':44, 'my_blob':array('H',range(256)).tostring()} > conn = sqlhub.getConnection() > tx = conn.transaction() > tx.query(conn.sqlrepr(sqlbuilder.Insert(MyObject, [record]))) > tx.commit() > > #psycopg2.DataError: invalid byte sequence for encoding "UTF8": 0x80 > #HINT: This error can also happen if the byte sequence does not match the > encoding expected by the server, which is controlled by "client_encoding". First, this has nothing with transactions. You can do conn.query() here. Second, SQLBuilder... SQLObject is a high-level object that does a lot of things internally, but SQLBuilder is a low-level interface that knows nothing about classes, columns, column types, conversion; the first parameters should be string (table name), the values must be converted to the DB format; for a BLOB it means you have to use psycopg.Binary wrapper. This code works for my in Postgres and SQLite: record = {'my_int':44, 'my_blob':conn.createBinary(array('H',range(256)).tostring())} conn.query(conn.sqlrepr(sqlbuilder.Insert(MyObject.sqlmeta.table, [record]))) Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Aaron R. <aar...@mo...> - 2009-07-29 04:57:25
|
Hi Oleg, That's terrific - thanks very much for your response. First off, the point you raised about setting many values at once; yes, I was aware of this, but a lot of the things we seem to want to do are more like we have a dict with strings for keys and associated values, and we want to write them into the db in one go, where the strings are the column names - is there's some way to do a myObject.set(), and pass in a dictionary? (or some python semantic that yields the same result?.. I'm not sure). Okay, I completely understand the distinction between SQLObject and SQLBuilder now, and it makes perfect sense - thanks! Just by the way, is there any way to do transactions with SQLObject's? Thanks again, -Aaron. On Fri, Jul 24, 2009 at 7:59 PM, Oleg Broytmann <ph...@ph...> wrote: > On Fri, Jul 24, 2009 at 12:31:02PM +1200, Aaron Robinson wrote: > > if I set the value of a blob column by setting a > > varialbe on my object, it gets written to the db just fine, but when I do > it > > inside a transaction using sqlbuilder I have the error. Please see the > code > > below. > > > > Test Code: > > > > from sqlobject import * > > from array import * > > > > sqlhub.processConnection = > connectionForURI('postgres://user:pass@localhost > > /mydb') > > > > class MyObject(SQLObject): > > myInt = IntCol(default=42) > > myBlob = BLOBCol(default=None) > > > > MyObject.createTable() > > > > #This works: > > myObject = MyObject() > > myObject.myInt = 43 > > myObject.myBlob = array('H',range(256)).tostring() > > I hope you know you can set many values at once: > myObject.set(myInt=43, myBlob=array('H',range(256)).tostring()) > (That's offtopic, just a reminder.) > > > #This doesn't work: > > record = {'my_int':44, 'my_blob':array('H',range(256)).tostring()} > > conn = sqlhub.getConnection() > > tx = conn.transaction() > > tx.query(conn.sqlrepr(sqlbuilder.Insert(MyObject, [record]))) > > tx.commit() > > > > #psycopg2.DataError: invalid byte sequence for encoding "UTF8": 0x80 > > #HINT: This error can also happen if the byte sequence does not match > the > > encoding expected by the server, which is controlled by > "client_encoding". > > First, this has nothing with transactions. You can do conn.query() here. > Second, SQLBuilder... SQLObject is a high-level object that does a lot of > things internally, but SQLBuilder is a low-level interface that knows > nothing about classes, columns, column types, conversion; the first > parameters should be string (table name), the values must be converted to > the DB format; for a BLOB it means you have to use psycopg.Binary wrapper. > This code works for my in Postgres and SQLite: > > record = {'my_int':44, > 'my_blob':conn.createBinary(array('H',range(256)).tostring())} > conn.query(conn.sqlrepr(sqlbuilder.Insert(MyObject.sqlmeta.table, > [record]))) > > Oleg. > -- > Oleg Broytmann http://phd.pp.ru/ ph...@ph... > Programmers don't die, they just GOSUB without RETURN. > |
From: Oleg B. <ph...@ph...> - 2009-07-29 07:21:59
|
On Wed, Jul 29, 2009 at 04:57:12PM +1200, Aaron Robinson wrote: > First off, the point you raised about setting many values at once; yes, I > was aware of this, but a lot of the things we seem to want to do are more > like we have a dict with strings for keys and associated values, and we want > to write them into the db in one go, where the strings are the column names > - is there's some way to do a myObject.set(), and pass in a dictionary? .set(**dict) > is there any way to do transactions with SQLObject's? transaction = connection.transaction() and use the transaction object everywhere instead of connection: sqlobject.sqlhub = transaction or class Table(SQLObject): _connection = transaction or Table.select(..., connection=transaction, ...) and so on. You don't need all at once - just choose a way you want it. Don't forget to do transaction.commit() at the end. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |