Title: Populate a listbox from a MariaDB database in Python

[This program loads its listbox from a MariaDB database query]

The code that actually populates the listbox is pretty short, but this program does a bit of setup work to prepare the database first. If you only want to see the list-populating code, skip down to the Populating the Listbox section. If you want to see how the program sets up the database, read on.

This example uses a MariaDB database. It should work with any database that supports SQL but some of the details may be slightly different.

Building the Database

This example fetches titles from the database's Books table so that table must exist. In a production application, it would exist, but I don't want to spend a bunch of time explaining how to build and populate a database, so I added code to this example to build the database.

The first step is to define the parameters the program will need to connect to the database.

import mariadb # Database parameters. db_name = 'books_db' table_name = 'Books' root_info = { 'host': '127.0.0.1', 'port': 3306, 'user': 'root', 'password': 'my_super_secret_password', } user_info = { 'host': '127.0.0.1', 'port': 3306, 'user': 'test_user', 'password': 'test_user_password', 'database': db_name, }

This code defines two sets of parameters for the users root and test_user. The program uses root to build the database. It then uses test_user to test the listbox population code.

IMPORTANT: Never store passwords in the code, in configuration files, or in any other location that could be easily read by a cyber-bandito. Make the user enter passwords at run time.

The program uses the following code to prepare the database.

# Set up the test database. # Skip this if the database is already set up. with mariadb.connect(**root_info) as conn: # Create and populate the database. create_database(conn, db_name, table_name) insert_records(conn, db_name, table_name) create_user(conn, db_name, user_info['user'], user_info['password'])

This code uses the root user's connection information to connect to MariaDB. Note that the MariaDB server must be running. On my system it's started as a Windows service so it starts when the system boots.

The code then calls the create_database, insert_records, and create_user methods to build the database.

TIP: It's important to close database connections (and cursors) when you're done with them because they use limited system resources. Creating them in a with statement ensures they are closed properly when the code is done with them.

Creating a connection takes a bit of time, so you should try to reuse connections for operations that will take place quickly. In this example, the three functions that create the database, add records, and create the user all happen one after each other so they share the same connection.

Creating the Database

The following create_database function creates the test database and its Books table.

def create_database(conn, db_name, table_name): '''Create and populate the database.''' # The connection should have permission to create the database and table. with conn.cursor() as cur: cmd = f'DROP DATABASE IF EXISTS {db_name}' cur.execute(cmd) # Create the database. cmd = f'CREATE DATABASE {db_name}' cur.execute(cmd) # Use the new database. cmd = f'USE {db_name}' cur.execute(cmd) # Make the table. cmd = f''' CREATE TABLE {table_name} ( Title VARCHAR(100) NOT NULL PRIMARY KEY, Year INT NOT NULL, ISBN VARCHAR(14) NOT NULL, Publisher VARCHAR(50) NOT NULL, Pages INT NOT NULL, URL VARCHAR(100) NOT NULL )''' cur.execute(cmd) conn.commit()

This code first creates a cursor object that it can use to execute database commands. It then simply executes the following SQL statements.

DROP DATABASE IF EXISTS books_db CREATE DATABASE books_db USE books_db CREATE TABLE Books ...

Inserting Records

The following insert_records function adds records to the Books table.

def insert_records(conn, db_name, table_name): '''Insert some records.''' # The connection should have permission to insert records. # Make a list of column names. column_names = ('Title', 'Year', 'ISBN', 'Publisher', 'Pages', 'URL') # Define book data for the columns. book_data = [ ( 'Build Your Own Python Action Arcade!', 2025, '979-8272041388', 'Rod Stephens Books', 292, 'https://amzn.to/4sc6ZHq', ), ( 'Build Your Own Ray Tracer With Python', 2024, '979-8333505439', 'Rod Stephens Books', 356, 'https://amzn.to/3XD9eqb', ), ... ] # Insert the data. with conn.cursor() as cur: # Compose the INSERT command. cmd = f'INSERT INTO {table_name} (' cmd += ', '.join(column_names) # Add the column names. cmd += ') VALUES (' cmd += ', '.join('?' * len(column_names)) # Add ? placeholders. cmd += ')' # Create the records. cur.executemany(cmd, book_data) conn.commit()

This code first makes a list of the table's column names. You could make it get that information from the database, but the code that follows is tied to the table's structure so we won't gain much flexibility by fetching the column names from the database.

Next, the code defines a list of tuples each holding information for a particular book record. The values are given in the order of the columns as defined by the column_names list.

The code then creates a cursor to execute SQL commands. It composes a query that looks like this:

INSERT INTO Books (Title, Year, ISBN, Publisher, Pages, URL) VALUES (?, ?, ?, ?, ?, ?)

Notice that each column name has a corresponding ? that acts as a placeholder for its value.

The function calls the cursor's executemany method, passing it the list of book data. The executemany method repeats the SQL command for each of the items in the book data list and inserts all of the records at once.

Creating the User

The following create_user function create the test user.

def create_user(conn, db_name, username, password): '''Create the user and grant all privildges for the database.''' # The connection should have permission to create the user. with conn.cursor() as cur: # Create test_user and grant provileges. cmd = f"CREATE USER IF NOT EXISTS '{username}'@'localhost' " \ f"IDENTIFIED BY '{password}'" cur.execute(cmd) # Grant privileges. cmd = f"GRANT SELECT, INSERT, UPDATE, DELETE ON {db_name}.* TO " \ f"'{username}'@'localhost'" cur.execute(cmd) # Flush. cmd = 'FLUSH PRIVILEGES' cur.execute(cmd)

This code executes the following SQL statements.

CREATE USER IF NOT EXISTS 'test_user'@'localhost' IDENTIFIED BY 'test_user_password' GRANT SELECT, INSERT, UPDATE, DELETE ON books_db.* TO 'test_user'@'localhost' FLUSH PRIVILEGES

Those statements create the user and grant the SELECT, INSERT, UPDATE, and DELETE privileges for that user on the books_db database.

TIP: Don't use the root account or any other account with full privileges to perform day-to-day tasks. Use root to build databases, create tables, and perform other tasks that require lots of privileges. Then switch to a less privileged account to perform normal operations. Give that less privileged account the fewest privileges it needs to do its job and nothing more. That will prevent bugs (or malicious users) from messing with things they shouldn't touch.

Populating the Listbox

Now that the database is created and populated, we can use it to fill the listbox. The following populate_listbox function executes a query and puts the results in a listbox.

def populate_listbox(listbox, conn, query): '''Populate the listbox from the query's first result in each row.''' listbox.delete(0, tk.END) with conn.cursor() as cur: cur.execute(query) for row in cur.fetchall(): listbox.insert(tk.END, row[0])

The code first clears the listbox. It then creates a cursor and uses it to execute the query. It loops through the results and adds the first result in each row to the listbox.

This example assumes you only want to put each row's first result in the listbox, but you could do other things here. For example, you could concatenate all of the returned columns, possibly separated by commas.

That's all there is to it! Well, ... except for actually calling populate_listbox.

Calling the Function

After the program builds its user interface, it calls the following method.

def populate_list(self): '''Populate the listbox from the Books table.''' with mariadb.connect(**user_info) as conn: query = f'SELECT Title FROM {table_name}' populate_listbox(self.books_list, conn, query)

This code simply creates a database connection and calls populate_listbox passing it the connection, the listbox and the query SELECT Title FROM Books.

You could place this code directly in the setup code, but I like to separate database code whenever possible to make it easier to change if you need to switch databases or something.

That's all there is to it! (Really this time.)

Conclusion

You could modify this example in many ways. Obviously you could use a different query on a different table in a different database. You could also make the call to populate_listbox share the same connection used by the database setup code. That code is only necessary if the database is not already set up, though, and the presumption is that it will be. You can figure out how to share the connection and only call the setup code if necessary if you like.

Download the example to experiment with it and to see additional details.