CODEWITHSUNDEEP
pythonsqlitesqlalchemypyqt
linuxoid
linuxoid

Reputation: 1447

PyQt, SQLAlchemy, SQLite - weird insert crash

I'm trying to convert an xml to sqlite and get a weird error:

Traceback (most recent call last):
File "C:\Temp\xxx\scripts\xml_to_db.py", line 212, in <module> 
win = Test(a)
File "C:\Temp\xxx\scripts\xml_to_db.py", line 20, in __init__
self.testdb()
File "C:\Temp\xxx\scripts\xml_to_db.py", line 133, in testdb
row = connection.execute(t_id)
File "C:\Temp\PortablePython\App\lib\site-packages\sqlalchemy\engine\base.py", line 1405, in execute 
params)
File "C:\Temp\PortablePython\App\lib\site-packages\sqlalchemy\engine\base.py", line 1538, in _execute_clause
element
compiled_sql, distilled_params
File "C:\Temp\PortablePython\App\lib\site-packages\sqlalchemy\engine\base.py", line 1646, in _execute_context 
context)
File "C:\Temp\PortablePython\App\lib\site-packages\sqlalchemy\engine\base.py", line 1639, in _execute_context 
context)
File "C:\Temp\PortablePython\App\lib\site-packages\sqlalchemy\engine\default.py", line 330, in do_execute
cursor.execute(statement, parameters)
InterfaceError: (InterfaceError) Error binding parameter 0 - probably unsupported type. u'SELECT temperatures.id \nFROM temperatures \nWHERE temperatures.temperature = ?' ('50',)

where line 130 corresponds to 

t_id = select([tb_temperatures.c.id], tb_temperatures.c.temperature == temp)

Here's the whole code 

def testdb(self):
    db_file = "C:/Temp/xxx/data/xxx/db.sqlite"

    file=QFile(db_file)
    if file.exists():
        file.remove()

    db = create_engine('sqlite:///' + db_file)
    connection = db.connect()

    metadata = MetaData()

    tb_materials = Table('materials', metadata,
        Column('id', Integer, primary_key=True),
        Column('material', String)
        )

    tb_temperatures = Table('temperatures', metadata,
        Column('id', Integer, primary_key=True),
        Column('temperature', String)
        )

    tb_mat_data = Table('mat_data', metadata,
        Column('id', Integer, primary_key=True),
        Column('mat_id', None, ForeignKey('materials.id', onupdate="CASCADE", ondelete="CASCADE")),
        Column('temp_id', None, ForeignKey('temperatures.id', onupdate="CASCADE", ondelete="CASCADE")),
        Column('density', String),
        Column('elasticity', String),
        Column('stress', String)
        )

    auto_assign(metadata, db)
    metadata.create_all(db)

    for m in ['SS 316']:
        data = tb_materials.insert().values(material = m)
        connection.execute(data)

    for t in ['25', '38', '50', '150']:
        data = tb_temperatures.insert().values(temperature = t)
        connection.execute(data)

    materials = ['SS 316']
    for material in materials:
        m_id = select([tb_materials.c.id], tb_materials.c.material == material)
        row = connection.execute(m_id)
        data = row.fetchone()
        m_id_key = data[0]
        temps = ['25', '38', '50', '150']
        for temp in temps:
            QMessageBox.about(self,"",temp)
            t_id = select([tb_temperatures.c.id], tb_temperatures.c.temperature == temp)
            row = connection.execute(t_id)
            data = row.fetchone()
            t_id_key = data[0]
            z = tb_mat_data.insert().values(mat_id = m_id_key, temp_id = t_id_key)
            connection.execute(z)

    connection.close()
    quit()

which crashes at the 3rd value of temp = '50'.

I'm totally puzzled and can't find what's wrong with it.

Upvotes: 0

Views: 463

Answers (1)

van
van

Reputation: 77012

I have no idea what exactly is happening there, but it definitely has to do with the Connection. I assume there is a bug with something to do with the parameters not being properly cleaned up in the statement preparation before the next execution.

For example, if you replace connection.execute(z) with db.execute(z) (connectionless execution; basically using another connection), this will probably work just fine, as the statement executed in-between those (row = connection.execute(m_id)) has only 1 parameter.

Also if you use connectionless execution (directly calling engine/db.execute(...)) everywhere, all works just fine as well (as there will be new connection used for every query).

Single SQL statement: Apart from this issue/bug?, you can actually execute the insert on the mat_data in one SQL INSERT statement using Correlated Update type of query:

QMessageBox.about(self,"",temp)
s = select([tb_temperatures.c.id], tb_temperatures.c.temperature == temp).limit(1)
z = tb_mat_data.insert().values(mat_id = m_id_key, temp_id = s)
connection.execute(z)

Upvotes: 1

Related Questions