python - 如何通过在 listctrl 中查看来查看数据库中包含的用户？

Question

我不知道如何在 listcrtl 中列出数据库的包含用户。

我没有尝试任何东西，因为我最多不知道创建的表代码。

 import wx
 import sqlite3

 class MyForm(wx.Frame):

def __init__(self):
    wx.Frame.__init__(self, None, wx.ID_ANY, "List Control Tutorial")

    # Add a panel so it looks the correct on all platforms
    panel = wx.Panel(self, wx.ID_ANY)
    self.index = 0

    self.list_ctrl = wx.ListCtrl(panel, size=(-1,100),
                 style=wx.LC_REPORT
                 |wx.BORDER_SUNKEN
                 )
    self.list_ctrl.InsertColumn(0, 'Subject')
    self.list_ctrl.InsertColumn(1, 'Due')
    self.list_ctrl.InsertColumn(2, 'Location', width=125)

    sizer = wx.BoxSizer(wx.VERTICAL)
    panel.SetSizer(sizer)

 # Run the program
 if __name__ == "__main__":
   app = wx.App(False)
   frame = MyForm()
   frame.Show()
   app.MainLoop()

我希望有人可以帮助我，因为这是一份课程工作，我遇到了很多麻烦。

标签： pythonsqlitewxpython

Answer 1

这是一个简单的例子。
我已经包括了创建、插入和检索的核心方法。我将更新和删除留给您自己的研究。

import wx
import sqlite3
import datetime
class MyForm(wx.Frame):

    def __init__(self):
        wx.Frame.__init__(self, None, wx.ID_ANY, "Sqlite3 List Control Tutorial")

        self.today = datetime.date.today().strftime("%Y%m%d")
        self.init_db()

        panel = wx.Panel(self, wx.ID_ANY)
        self.index = 0

        self.list_ctrl = wx.ListCtrl(panel, size=(-1,200),
                         style=wx.LC_REPORT
                         |wx.BORDER_SUNKEN
                         )
        self.list_ctrl.InsertColumn(0, 'User')
        self.list_ctrl.InsertColumn(1, 'Due')
        self.list_ctrl.InsertColumn(2, 'Location', width=125)

        btn = wx.Button(panel, label="Add Line")
        btn2 = wx.Button(panel, label="Get Data")
        btn.Bind(wx.EVT_BUTTON, self.add_line)
        btn2.Bind(wx.EVT_BUTTON, self.get_data)

        sizer = wx.BoxSizer(wx.VERTICAL)
        sizer.Add(self.list_ctrl, 0, wx.ALL|wx.EXPAND, 5)
        sizer.Add(btn, 0, wx.ALL|wx.CENTER, 5)
        sizer.Add(btn2, 0, wx.ALL|wx.CENTER, 5)
        panel.SetSizer(sizer)
        self.Bind(wx.EVT_CLOSE, self.OnExit)

    def add_line(self, event):
        return

    def get_data(self, event):
        self.cursor.execute("select * from Users")
        user_data = self.cursor.fetchall()
        if user_data:
            for row in user_data:
                self.list_ctrl.InsertItem(self.index, str(row['User_id']))
                self.list_ctrl.SetItem(self.index, 1, str(row['Start_date']))
                self.list_ctrl.SetItem(self.index, 2, row['Location'])
                self.index += 1

    # Open the database, if necessary create it
    # isolation_level None is a convenience that means we don't have to manually commit transactions to the database
    # it should be treated with caution other than for test purposes
    # row_factory allows us to access the data by column name rather than column position i.e. row['User_id'] vs row[0]

    def init_db(self):
        self.db_name = "mydb.db"
        self.db = sqlite3.connect(self.db_name, isolation_level=None)
        self.db.row_factory = sqlite3.Row
        self.cursor = self.db.cursor()
        result = self.cursor.execute("create table if not exists Users (User_id INT PRIMARY KEY NOT NULL, \
            Start_date INT DEFAULT CURRENT_DATE, \
            Location TEXT DEFAULT '')")

        self.cursor.execute("select * from Users")
        data_test = self.cursor.fetchone()
        if data_test:
            return

        #If there is no data in the database, create some
        locations = ["Ecuador","France","Chad","Burma","Panama"]
        import random
        try:
            print("Inserting test data")
            for idx in range(1,11):
                self.db.execute("insert into Users(User_id,Start_date,Location) values (?,?,?)",(idx,str(self.today),random.choice(locations)));
        except sqlite3.Error as e:
            print(str(e))

    def OnExit(self, evt):
        self.db.close()
        self.Destroy()


   # Run the program
if __name__ == "__main__":
    app = wx.App(False)
    frame = MyForm()
    frame.Show()
    app.MainLoop()