exec on PyQt6>QtSql>QSqlQuery does nothing

Question

I'm trying to create a table on my PostgreSQL database using QSqlQuery from PyQt6.QtSql. Executing the code below has no effects!

import sys
from PyQt6.QtSql import QSqlDatabase, QSqlQuery

con = QSqlDatabase.addDatabase('QPSQL')
con.setDatabaseName('test')

if not con.open('test', 'test'):
    print("Something went wrong!")
    sys.exit(1)

query = QSqlQuery()

query.exec("""
CREATE TABLE contacts(
    id serial primary key,
    name varchar(40) not null,
    job varchar(50),
    email varchar(40) not null
)
""")

print(con.tables())

I ran the query in pgAdmin4; it works. Afterwards I insert a row into it and changed the table name to "a-table".

I changed CREATE query to a SELECT query and got result; connection is working.

Running the above code as-is results in this:

qt.core.qobject.connect: QObject::connect(QObject, Unknown): invalid nullptr parameter
['a-table']

PostgreSQL version is 16.1. Python version is 3.12.0. PyQt version is 6.6.1. All running on Windows 10 Pro 22h2.

Answer 1

This started as a comment, but I wanted to add some code.

QPSQL should work fine as long as it can find a copy of libpq.dll somewhere. For me on Windows, the cleanest way to do this is to install the official ODBC driver and add its bin folder to my system PATH.

I'm a few versions behind (12.02) and my path for libpq.dll is C:\Program Files\psqlODBC\1202\bin. If you dig around in pgAdmin4 directory, you'll see that it has it's own copy of libpq.dll.

If you don't want to (or can't!) edit your system PATH, you can also copy libpq.dll to the folder where qpsqlsql.dll is. If you can find it! It will be buried somewhere around site-packages\qt6_applications\Qt\plugins.

Here's some code that works with QPSQL. I usually keep a copy of this code in any new project, because I'm always forgetting to install ODBC driver on new computers and modifying path.

import sys
from PyQt6.QtSql import (QSqlQuery, QSqlDatabase, QSqlDriver)
from PyQt6.QtWidgets import QApplication

app = QApplication(sys.argv)

print(QSqlDatabase.drivers())

pgtest = QSqlDatabase.addDatabase("QPSQL")
pgtest.setHostName('MyHost')
pgtest.setDatabaseName('MyDb')
pgtest.setUserName('myuser')
pgtest.setPassword('mypassword')

print('pgtest.open: ', pgtest.open())


query = QSqlQuery(pgtest)

query.exec('''SELECT * from MyTable''')
print('query.rowCount() ', query.size())
print('last error ', query.lastError().text())
print('pgtest.open: ', pgtest.open())