[PyQt] Fwd: SQL Prepared Query Bugs

[David Morris]

(apologies if you get this twice, I sent it from the wrong email the first
time and assume the mailing list server rejected the message)

I have encountered two bugs using PyQt5.QtSql.QSqlQuery:

*PyQt Version: *PyQt 5.6.dev1604021410
*Python Version:* 3.4.3

*Bug #1: python "bytes" data type does not insert into database*
If I insert a byte array, the SQLite column contains no data, but I can
convert that data to a string and insert it as expected.

*Bug #2: QSqlQuery.exec() cannot be used with a prepared query*
Not certain if this is a bug or if it is expected behavior.
QSqlQuery.exec() can only be called with an SQL string as input.  For a
prepared query, I must instead use QSqlQuery.exec_(). However, exec_() can
be used for either prepared queries or direct sql strings.

Example code:

from PyQt5 import QtSql

db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName("file.sqlite3")

db.open()

query = QtSql.QSqlQuery(db)

query.exec("CREATE TABLE myTable( id INTEGER PRIMARY KEY, myValue BLOB );")

data = b'\x01\x02\x03\x04'


# Insert data as *bytes* object: insert fails

sql = "INSERT INTO myTable (id, myValue) VALUES (1, :myValue);"
query.prepare(sql)
query.bindValue(":myValue", data)

# query.exec() <<< this fails, "not enough arguments"

query.exec_()

# ^^^^^^^^^^^ Why is exec_() needed, not exec()?

query.exec("SELECT * FROM myTable WHERE id = 1")

query.first()
print(query.value(0), ':', query.value(1))
# 1 :

# Add BINARY flag: Insert fails

sql = "INSERT INTO myTable (id, myValue) VALUES (2, :myValue);"

query.prepare(sql)
query.bindValue(":myValue", *data, QtSql.QSql.In <http://QtSql.QSql.In> |
QtSQl.QSql.Binary*)

query.exec_()

query.exec_("SELECT * FROM myTable WHERE id = 2")

query.first()

print(query.value(0), ':', query.value(1))
# 2 :


# Convert data to *str* object: insert Succeeds
sql = "INSERT INTO myTable (id, myValue) VALUES (3, :myValue);"
query.prepare(sql)
query.bindValue(":myValue", *str(data)*)
query.exec_()

query.exec("SELECT * FROM myTable WHERE id = 3")

query.first()
print(query.value(0), ':', query.value(1))
# 2 : b'\x01\x02\x03\x04'



David
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://www.riverbankcomputing.com/pipermail/pyqt/attachments/20160411/3ff7189b/attachment-0001.html>