Show mysql column's table results in python kivy

user_18679806 0
Viewed 171

Thank's for your time.

Can't show table's column data from mysql.

I need to show the two columns results, product_code and product_name columns.

I can connect to mysql database, access to headers in tables and print them but not to column results.

My admin.py

class AdminWindow(BoxLayout):
    def __init__(self, **kwargs):
        super().__init__(**kwargs)
        self.mydb = mysql.connector.connect(
            host='localhost',
            user='root',
            passwd='',
            database='pos2users'
        )
        self.mycursor = self.mydb.cursor()
        self.mycursor.execute('SELECT * FROM stock')

        products = self.get_products()
        prod_table = DataTable(table=products)

        product_code = []
        product_name = []
        spinner_values = []

        for product in products:
            product_code.append(product[0])
            name = product[1]
            if len(name) > 10:
                name = name[:10] + '...'
            product_name.append(name)
        
        for x in range(len(product_code)):
            line = ' | '.join([product_code[x], product_name[x]])
            spinner_values.append(line)
        self.ids.target_product.values = spinner_values

The table

The results

kivy mysql python
1 Answers

Let's rethink. Dump the table and store the dump (list of rows) in the mycursor variable.

Use .fetchall(), to make further queries to the same connection:

self.mycursor = self.mydb.cursor()
self.mycursor.execute('SELECT * FROM stock)
self.my_stock = self.mycursor.fetchall()

Each row is a list of the queried columns (here all, Select * from table). You can get the column content for every row like so:

self.mycursor.execute('SELECT * FROM products)
self.my_products = self.mycursor.fetchall() 
for row in self.my_products:
    print(f' id: {row[0]}, product_code: {row[1]}, product_name: {row[2]} ...')

Some people say it is not good practice to use the star in the Select * from clause. Can write:

self.code_name = self.mycursor.execute('SELECT product_code, product_name FROM products').fetchall()

print('\n ----- PRODUCT_DUMP ---- \n')
for row in self.code_name:
    print(f' {row[0]} | {row[1]}') 
    self.ids.target_product.values = f'{row[0]} | {row[1]}'

In debug mode you can search in LogCat for ----- PRODUCT_DUMP ----. I use buildozer to compile and Android Studio for LogCat debugging the emulator and phone.

Cheers

Admin1
Related