Python Referencing Database Connection On Another Script

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 wit

Solution 1:

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.pyimport MySQLdb
from sshtunnel import SSHTunnelForwarder

defopen_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)

defclose_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.pyimport MySQLdb
from sshtunnel import SSHTunnelForwarder

classOpenManager(object):
    def__init__(self):
        self.server =None
        self.db = None# here you could define some parameters and call them nextdef__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

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

    defdata_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 inenumerate(results):
                self.tableWidget.insertRow(row_number)
                for column_number, data inenumerate(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.