- changed status to wontfix
types.BINARY behavior in sqlite3 is unexpected
Issue #2110
resolved
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.
Comments (1)
-
repo owner - Log in to comment
Several points here:
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
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:
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 usingLargeBinary
.No test case attached here otherwise so this is "worksforme".