Python referencing database connection on another script

Question

I'm in the depths of learning Python whilst trying to make an application, using data stored on a MySQL/MariaDB, and I am nearly at the stage that I can make some good progress with the project. I can connect to my DB via SSH, and retrieve data from a python script, but I am now looking to display that data in a GUI box. One of the challenges I'm facing is that I have two separate scripts to handle the connections, one to open and one to close, my theory being that a connection is only needed for data access. I've used PyQT5 to create the various GUIs and windows, and specifically I am looking to populate a QtTableWidget. The script I have doesn't currently give me any errors, but neither does it display the data in the table widget. My hunch is that it's not correctly referencing the database on the open connection script, and therefore has no data to pass, but I am struggling to identify the terminology needed for an effective google search.

My OpenConn.py is as follows:

import MySQLdb
from sshtunnel import SSHTunnelForwarder

def Open_Conn():
    with SSHTunnelForwarder(
             ('192.168.0.10', 22),
             ssh_password="xxx",
             ssh_username="xxx",
             remote_bind_address=('localhost', 3306)) as server:

        db = MySQLdb.connect(host='localhost',
                               port=server.local_bind_port,
                               user='xxx',
                               passwd='xxx',
                               db='DBNAME')

        cursor = db.cursor()
if __name__ == '__main__':
    Open_Conn()

And my main.py is as follows:

from PyQt5 import QtCore, QtGui, QtWidgets
import sys
from ViewClientsUI import Ui_ViewClients
from OpenConn import Open_Conn

class ViewClientsWindow(QtWidgets.QDialog, Ui_ViewClients):
    def __init__(self):
        super(ViewClientsWindow, self).__init__()
        self._new_window = None
        self.setupUi(self)

    def data_load():
        with OpenConn.Open_Conn:
            connection = OpenConn.Open_Conn()
            query = "SELECT * FROM Clients"
            result = connection.execute(query)
            self.tableWidget.setRowCount(0)
            for row_number, row_data in enumerate(result):
                self.tableWidget.insertRow(row_number)
                for column_number, data in enumerate(row_data):
                    self.tableWidget.setItem(row_number, column_number, QtWidgets.QTableWidgetItem(str(data)))

if __name__ == '__main__':
    app = QtWidgets.QApplication(sys.argv)
    gui = ViewClientsWindow()
    gui.show()
    app.exec_()
    Open_Conn()

Can anyone help my identify why I'm not getting data in the table widget? Many thanks in advance

the code is a bit confusing, there is no point to call Open_Conn() after app.exec_(). Then, you import Open_Conn but you call OpenConn.Open_Conn in data_load() which is never called, and will throw an error (no self in the arguments). btw, the connection get closed automatically if you use a context manager (with statement). — PRMoureu
– PRMoureu, Commented Sep 10, 2017 at 14:30
I understand it's confusing, I'm far from a seasoned programmer :/ I think I understand what you're saying, and is that suggesting to replace 'Open_Conn()' after 'app.exec_()' with 'data_load()' ? RE: Context manager, does that mean that I don't need to call a CloseConn, because once "with" has finished, it will close connection? — JusticeEmpire
– JusticeEmpire, Commented Sep 10, 2017 at 14:36
Just tried that switch around, and I got an error saying 'data_load' not defined — JusticeEmpire
– JusticeEmpire, Commented Sep 10, 2017 at 14:38
yes about the CloseConn : doc. Then maybe you can fix data_load and call it inside the __init__ method ? (call it with self.data_load() inside the class, or gui.data_load() outside) and remove the Open_Conn() — PRMoureu
– PRMoureu, Commented Sep 10, 2017 at 14:39
But the way you want to open the connection makes the connection open only inside the function Open_Conn(), you won't be able to use it like this with a context manager by calling the function. Your pattern may need a function to open and another to close, but without context manager — PRMoureu
– PRMoureu, Commented Sep 10, 2017 at 14:46

PRMoureu · Accepted Answer · 2017-09-10 22:30:00Z

The functional way :

This way shows the functions you need to set up to be able to call them in another module. I removed the context manager that cannot be used with this functional pattern, since it get closed at the end of the function Open_Conn. So the open_conn function creates a server object, and the database object db, they will get called next in close_conn to get closed when necessary.

#OpenConn.py
import MySQLdb
from sshtunnel import SSHTunnelForwarder

def open_conn():
    server = SSHTunnelForwarder(
         ('192.168.0.10', 22),
         ssh_password="xxx",
         ssh_username="xxx",
         remote_bind_address=('localhost', 3306))

    server.start()
    print('opening server : OK')

    db = MySQLdb.connect(host='localhost',
                         port=server.local_bind_port,
                         user='xxx',
                         passwd='xxx',
                         db='DBNAME')
    print('opening database : OK')
    return (server, db)

def close_conn(server, db):
    db.close()
    server.stop()
    print('closing connection : OK')

from PyQt5 import QtCore, QtGui, QtWidgets
import sys
from ViewClientsUI import Ui_ViewClients
from OpenConn import open_conn, close_conn

class ViewClientsWindow(QtWidgets.QDialog, Ui_ViewClients):
    def __init__(self):
        super(ViewClientsWindow, self).__init__()
        self._new_window = None
        self.setupUi(self)
        self.data_load()

    def data_load(self):
        server, db = open_conn()
        cursor = db.cursor()
        query = "SELECT * FROM Clients"
        cursor.execute(query)
        results = cursor.fetchall()
        self.tableWidget.setRowCount(0)
        for row_number, row_data in enumerate(results):
            self.tableWidget.insertRow(row_number)
            for column_number, data in enumerate(row_data):
                self.tableWidget.setItem(row_number, column_number, QtWidgets.QTableWidgetItem(str(data)))
        close_conn(server, db)

if __name__ == '__main__':
    app = QtWidgets.QApplication(sys.argv)
    gui = ViewClientsWindow()
    gui.show()
    sys.exit(app.exec_())

The context manager way :

The functional pattern can be improved by using a context manager class to handle the opening and the closing part automatically. The manager can return only the db.cursor to execute the queries, the server stays inside the manager. To get cursor, you catch the value return by the context manager inside the method __enter__ by using as : with OpenManager() as cursor:.

To create it, basically, you can move the opening code inside the method __enter__ (executed when you will call the context manager) and the closing part inside the method __exit__ (called at the end of the with statement block)

#OpenConn.py
import MySQLdb
from sshtunnel import SSHTunnelForwarder

class OpenManager(object):
    def __init__(self):
        self.server =None
        self.db = None
        # here you could define some parameters and call them next

    def __enter__(self):
        self.server = SSHTunnelForwarder(
             ('192.168.0.10', 22),
             ssh_password="xxx",
             ssh_username="xxx",
             remote_bind_address=('localhost', 3306))
        self.server.start()
        print('opening server : OK')

        self.db = MySQLdb.connect(host='localhost',
                             port=self.server.local_bind_port,
                             user='xxx',
                             passwd='xxx',
                             db='DBNAME')
        print('opening database : OK')

        return self.db.cursor() # 

    def __exit__(self, type, value, traceback):
        self.db.close()
        self.server.stop()
        print('closing connection : OK')

This pattern allows you to call the context manager in your widget, inside a with statement like below :

from PyQt5 import QtCore, QtGui, QtWidgets
import sys
from ViewClientsUI import Ui_ViewClients
from OpenConn import OpenManager

class ViewClientsWindow(QtWidgets.QDialog, Ui_ViewClients):
    def __init__(self):
        super(ViewClientsWindow, self).__init__()
        self._new_window = None
        self.setupUi(self)
        self.data_load()

    def data_load(self):
        with OpenManager() as cursor:  
            query = "SELECT * FROM Clients"
            cursor.execute(query)
            results = cursor.fetchall()
            self.tableWidget.setRowCount(0)
            for row_number, row_data in enumerate(results):
                self.tableWidget.insertRow(row_number)
                for column_number, data in enumerate(row_data):
                    self.tableWidget.setItem(row_number, column_number, QtWidgets.QTableWidgetItem(str(data)))


if __name__ == '__main__':
    app = QtWidgets.QApplication(sys.argv)
    gui = ViewClientsWindow()
    gui.show()
    sys.exit(app.exec_())

You could also create the connection with SSHTunnelForwarder directly in the widget to avoid this and use the context manager provided by the class, then create the database connection inside.

The custom class shown above is just a way to mix the connection to the server and to the database inside one context to make it easy if you need these connections at many places in your code.

@JusticeEmpire i've just corrected typos in the answer, i hope you didn't get stuck because of them
Hiya, sorry it's been mad at work. I haven't had chance to check out the Context manager option yet, but the first 'functional' one worked with a bit of tweaking e.g. typos, and referencing to the table widget. Other than that, I have a separate issue trying to get the column data, but I will mark this question as answered, and if I get stuck on the column data I'll post another. Many thanks for your assistance @PRMoureu
thanks for the feedback! don't worry about the context manager, it may be a bit overthinking... i hope you got the main idea from the first solution. If the issue you've got is related to what i provide, please tell me how i can help, otherwise it may worth to open another question
stackoverflow.com/questions/46222391/qt5-qtableview-with-qtsql I've posted another question, any ideas?

Collectives™ on Stack Overflow

Python referencing database connection on another script

1 Answer 1

The functional way :

The context manager way :

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

The functional way :

The context manager way :

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related