What is the right way to use PostgreSQL with PyQt5

[Dennis Jensen]

First and foremost using the PyQt database tools is completely 
unnecessary and is either at the least adding much more complexity than 
needed and at worst it is tying the front-end way to tightly to the 
back-end which is just full numerous issues.  However, on a positive 
note you can interface with PostgresSQL and any other database software 
using straight Python allowing one to build an interface to the database 
that they can connect to the GUI via a Data-Router (or Controller) thus 
in 90% or more of the cases where you are dealing with a GUI and a data 
source (be it a database or something else that supplies data) the MVC 
or (M-C/R-V) methodology is generally the best method to apply as it 
keeps things very simple and straight forward and you can later easily 
add and/or change either the front-end and/or back-end without any 
issues and these kind of changes do happen more often than one might 
suspect.  Next it also removes or at least reduces the issues you might 
be having with drivers because once you get the PostresSQL up and 
running you do not need anything more than the Python library to 
interface with it.

On 6/7/2020 12:02 PM, Nenad Lamza wrote:
> I use PostgreSQL 9.6.17 (installed on linux server on LAN) and have 
> Win10 (or Win7) clients with installed Python 3.7 or 3.8 and PyQt 
> 5.13.2, and my PyQt5 app that uses that PostgreSQL server works ok, 
> but I really don't know what I'm doing.
>
> I'll describe the problem. If I don't have PostgreSQL installed on Win 
> client (I don't need it because I use PostgreSQL on linux server) or 
> have installed it but not in path, my app crashes with the error: 
> "QSqlDatabase: QPSQL driver not loaded".
>
> So, I need some drivers. And I install PostgreSQL 9.6.x on Win client 
> and have it in path and after that everything works ok, but only if I 
> had luck and picked the right x in PostgreSQL 9.6.x version.
>
> The alternative is that I don't install PostgreSQL 9.6.x (why would I 
> need to install it) on client machine and just copy some dll files at 
> the app directory. If I find the right files (drivers), everything 
> works ok, but if I don't, the app crashes with the error: 
> "QSqlDatabase: QPSQL driver not loaded" or worse, recognize the 
> PostgreSQL database on server, works ok some time, but after the 
> first, second or third query it crashes.
>
> Some driver combinations I copy to my app directory that work with 
> PyQt 5.13.2:
>
> 1.
> Timestamp                      Size             File
> 25/02/2016  07:19         1,015,973 libiconv-2.dll
> 07/01/2016  09:02         1,550,023 libintl-8.dll
> 10/07/2019  20:20           145,408 libpq.dll
>
> 2.
> Timestamp                      Size             File
> 21/06/2019  14:43         1,218,048 libeay32.dll
> 25/02/2016  07:19         1,015,973 libiconv-2.dll
> 07/01/2016  09:02         1,550,023 libintl-8.dll
> 10/07/2019  20:20           145,408 libpq.dll
> 21/06/2019  14:43           276,992 ssleay32.dll
>
> If I install PyQt 5.14 or 5.15 (instead of PyQt 5.13.2), the app 
> crashes, and I am afraid to upgrade PyQt5.
>
> So, my question is:
> How do I know which drivers I should copy to my app directory with 
> various version of PyQt5, that's the mistery to me. And also if 
> copying the drivers to my app directory is the right way at all?
>
> There's a fine answer here:
>
> https://stackoverflow.com/questions/20884010/qt5-failed-to-load-psql-driver-in-windows
>
> "It's quite likely to be failing because there's a dependency of 
> |qsqlpsql.dll| missing - either |libpq.dll|, or one of the libraries 
> that |libpq| build requires." ... "You should use the /same/ libpq 
> your Qt's |qsqlpgsql.dll| was compiled against if possible."
>
> If that's true, how should I know with what libpq library 
> qsqlpgsql.dll was compiled in. I suppose PyQt 5.13.2's qsqlpgsql.dll 
> is compiled with different version than PyQt 5.14.x's qsqlpgsql.dll or 
> even 5.15.x's qsqlpgsql.dll.
>
> Also, this link has some clues:
> https://stackoverflow.com/questions/26507353/qpsql-driver-not-loaded-qt
>
> And generally, what's the best way to work with PostgreSQL in PyQt5. 
> It obviously doesn't work out of the box if PostgreSQL is not 
> installed on client machine, and even if the wrong version is installed.
>
> Nenad
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://www.riverbankcomputing.com/pipermail/pyqt/attachments/20200608/c0b43ae5/attachment.htm>