Python在另一个脚本上引用数据库连接 [英] Python referencing database connection on another script
问题描述
我正在尝试使用存储在MySQL/MariaDB中的数据来研究Python，同时尝试创建一个应用程序，并且我几乎可以在该项目上取得一些良好的进展.我可以通过SSH连接到我的数据库，并从python脚本检索数据，但是现在我希望在GUI框中显示该数据.我面临的挑战之一是，我有两个单独的脚本来处理连接，一个是打开脚本，一个是关闭脚本，我的理论是，仅数据访问才需要连接.我已经使用PyQT5创建各种GUI和窗口，特别是我正在寻找填充QtTableWidget.我现在拥有的脚本没有给我任何错误，但是它也没有在表小部件中显示数据.我的直觉是，它无法正确地在打开的连接脚本上引用数据库，因此没有要传递的数据，但是我正在努力确定有效的Google搜索所需的术语.
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.
我的OpenConn.py如下:
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()
我的main.py如下:
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
推荐答案
功能方式:
这种方式显示了您需要设置的功能，以便能够在另一个模块中调用它们.我删除了不能与该功能模式一起使用的上下文管理器，因为它在函数Open_Conn的结尾处关闭了.因此，open_conn函数创建一个server对象，然后创建数据库对象db，它们将在close_conn中被调用，以在必要时关闭.
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_())
上下文管理器方式:
可以通过使用上下文管理器类来自动处理打开和关闭部分来改进功能模式.管理器只能返回db.cursor来执行查询，而服务器则留在管理器内部.要获取cursor，您可以使用 as :with OpenManager() as cursor:捕获上下文管理器在方法__enter__中返回的值.
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:.
要创建它，基本上，您可以将 opening 代码移动到方法__enter__(将在调用上下文管理器时执行)中，而将 closes 部分移动到其中方法__exit__(在with statement块的结尾处调用)
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')
此模式允许您在with statement内的小部件中调用上下文管理器，如下所示:
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_())
您也可以直接在窗口小部件中直接使用SSHTunnelForwarder创建连接，以避免这种情况，并使用该类提供的上下文管理器，然后在内部创建数据库连接.
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.
这篇关于Python在另一个脚本上引用数据库连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
