types.BINARY behavior in sqlite3 is unexpected

[Former user]

Specifying types.BINARY for an sqlite3 column results in a NUMERIC field, a simple solution would be to convert all BINARY fields to BLOB.

The attached patch does this.

[Mike Bayer]

Several points here:

1. SQLIte has no awareness of BINARY/BLOB at the column declaration level. You could call the type "DOG" and it accepts any style of data on a per-column basis. Here is an example:

   #!python import sqlite3 import binascii

   conn = sqlite3.connect(":memory:") cursor = conn.cursor()

   cursor.execute(""" CREATE TABLE test ( a BINARY, b BLOB, c INTEGER, d DOG ) """)

   data = \ '789c2d954b121d350c45e7bd12129cc292fc4d522936c18c82193b604465ef9cd'\ '3c9c0cf7eb66c5d5d5da97f8fbbeaf92f67f59655a3e5aef1fc737a3d1f224f0b'\ '19d36f56aa0ebc7e2e56a7b1707975b93abfbdee7d70941333ba82f335948ff6bc'

   data = binascii.unhexlify(data)

   cursor.execute("INSERT INTO test (a, b, c, d) VALUES (?, ?, ?, ?)", sqlite3.Binary(data), sqlite3.Binary(data), sqlite3.Binary(data))

   cursor.execute("SELECT a, b, c, d FROM test") row = cursor.fetchone()

   for elem in row: assert str(elem) == data

2. A test using SQLAlchemy constructs also illustrates no issue among all three variants of binary (note the SQLA binary types are doing the required "sqlite3.Binary" call here):

   #!python from sqlalchemy import * from sqlalchemy.types import BINARY, LargeBinary, BLOB import binascii

   engine = create_engine('sqlite://', echo=True)

   t = Table("test", MetaData(), Column("a", BINARY), Column("b", LargeBinary), Column("c", BLOB), ) t.create(engine)

   data = \ '789c2d954b121d350c45e7bd12129cc292fc4d522936c18c82193b604465ef9cd'\ '3c9c0cf7eb66c5d5d5da97f8fbbeaf92f67f59655a3e5aef1fc737a3d1f224f0b'\ '19d36f56aa0ebc7e2e56a7b1707975b93abfbdee7d70941333ba82f335948ff6bc'

   data = binascii.unhexlify(data)

   engine.execute(t.insert(), a=data, b=data, c=data)

   row = engine.execute(t.select()).fetchone()

   for elem in row: assert str(elem) == data

output:

2011-03-28 16:54:27,504 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE test (
    a BINARY, 
    b BLOB, 
    c BLOB
)


()
COMMIT
INSERT INTO test (a, b, c) VALUES (?, ?, ?)
(<read-only buffer for 0x11b19f8, size -1, offset 0 at 0x11ae9a0>, 
<read-only buffer for 0x11b19f8, size -1, offset 0 at 0x11ae960>, <read-only buffer for 0x11b19f8, size -1, offset 0 at 0x11ae920>)
COMMIT
SELECT test.a, test.b, test.c 
FROM test
()

3. The point of an UPPERCASE type in SQLAlchemy is that you get exactly the type you requested, so no "switching" of types is done at that level. You should be using LargeBinary if you'd like SQLalchemy to interpret the target type in a generic way towards the target database. The SQLite dialect renders 'BLOB' when using LargeBinary.

No test case attached here otherwise so this is "worksforme".