mysql - Python referencing database connection on another script -
i'm in depths of learning python whilst trying make application, using data stored on mysql/mariadb, , @ stage can make progress project. can connect db via ssh, , retrieve data python script, looking display data in gui box. 1 of challenges i'm facing have 2 separate scripts handle connections, 1 open , 1 close, theory being connection needed data access. i've used pyqt5 create various guis , windows, , looking populate qttablewidget. script have doesn't give me errors, neither display data in table widget. hunch it's not correctly referencing database on open connection script, , therefore has no data pass, struggling identify terminology needed effective google search.
my openconn.py follows:
import mysqldb sshtunnel import sshtunnelforwarder def open_conn(): sshtunnelforwarder( ('192.168.0.10', 22), ssh_password="xxx", ssh_username="xxx", remote_bind_address=('localhost', 3306)) 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 main.py follows:
from pyqt5 import qtcore, qtgui, qtwidgets import sys viewclientsui import ui_viewclients 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(): openconn.open_conn: connection = openconn.open_conn() query = "select * clients" result = connection.execute(query) self.tablewidget.setrowcount(0) row_number, row_data in enumerate(result): self.tablewidget.insertrow(row_number) 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 identify why i'm not getting data in table widget? many in advance
the functional way :
this way shows functions need set able call them in module. removed context manager cannot used functional pattern, since closed @ end of function open_conn. open_conn function creates server object, , database object db, called next in close_conn closed when necessary.
#openconn.py import mysqldb 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 viewclientsui import ui_viewclients 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 * clients" cursor.execute(query) results = cursor.fetchall() self.tablewidget.setrowcount(0) row_number, row_data in enumerate(results): self.tablewidget.insertrow(row_number) 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 improved using context manager class handle opening , closing part automatically. manager can return db.cursor execute queries, server stays inside manager. cursor, catch value return context manager inside method __enter__ using as : with openmanager() cursor:.
to create it, basically, can move opening code inside method __enter__ (executed when call context manager) , closing part inside method __exit__ (called @ end of with statement block)
#openconn.py import mysqldb sshtunnel import sshtunnelforwarder class openmanager(object): def __init__(self): self.server =none self.db = none # here define parameters , 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 call context manager in widget, inside with statement below :
from pyqt5 import qtcore, qtgui, qtwidgets import sys viewclientsui import ui_viewclients 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): openmanager() cursor: query = "select * clients" cursor.execute(query) results = cursor.fetchall() self.tablewidget.setrowcount(0) row_number, row_data in enumerate(results): self.tablewidget.insertrow(row_number) 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 create connection sshtunnelforwarder directly in widget avoid , use context manager provided class, create database connection inside.
the custom class shown above way mix connection server , database inside 1 context make easy if need these connections @ many places in code.
Comments
Post a Comment