how can i list MySQL databases in a Python array

Question

i am working on a piece of software that can store clients and the jobs we are doing for them in a MySQL database, however for the UI to work the way i want it to i need to have every one of the databases or tables in an array to reference later. for example

MysqlTables = [customers,clients,jobs]
MysqlTables[0] = [joshua,alex,james]
MysqlTables[0][0] = ["computer issue"]

this way i can reference and display all the information in the frontend UI any other solution of displaying a visual navigation in PyQt4 using the tree widget would be appreciated

i am still pretty new to using MySQL with python and am having some trouble understanding everything, i have found this but i don't understand it

How do I list all tables in all databases in SQL Server in a single result set?

from what i can depict from this i might be wanting to do something similar but i have no idea how to implement it and it will not contain the job information of the client.

You have described a solution you need with extreme constraints without really identifying what it is you want to accomplish. If you have data in mysql, you can extract what is required using sql statements. You don't need all the data all the time, or if you do, why put it in a database? — joel goldstick, Jul 04 '16 at 14:51
its meant to be accessed by a web client also, thats why i decided to store the information in a database, i also need to access it to store and print invoices once the job is complete. i am trying to make a client that allows you to add, view and edit data in a database with a smooth ui, the method i have to accomplish this currently involves a tree based visual element in PyQt4 to allow the user to file through multiple pieces of information and have them available within a few simple steps. — Lamar Tony Daughma, Jul 04 '16 at 15:00
You won't get someone on SO to write code on such a thin spec. Learn how to write something similar to what you want by following some tutorials. Come back when you have some code — joel goldstick, Jul 04 '16 at 15:06

score 0 · Accepted Answer · answered Jul 04 '16 at 15:56

I wrote something very similar for a project I did, where I basically had a database interface in the GUI, and I allowed the user to edit and add entries to the database without having to go into the MySql Workbench.

You're going to need two functions to interact with your database, one to retrieve the entries, and one to execute the changes.

This is some code that will retrieve information from the database using SqlAlchemy.

def get_info():
    sql = text('SELECT id, '
                   'serial_number, '
                   'weight_name, '
                   'units, '
                   'nominal, '
                   'customer_name, '
                   'density, '
                   'density_uncert , '
                   'volumetric_exp, '
                   'comment '
                   'FROM weights_external '
                   'ORDER BY id ')

    return self.engine.execute(sql).fetchall()

This selects the id, serial number, units, etc. from the table called "weights_external", ordering it by id. self.engine.execute(sql).fetchall() returns a generator (I think, it might be a list but I don't remember off the top of my head sorry!) which you can loop through and get all the results. In this example, I'm getting 10 columns from this table. If the table had 100 rows, then the function would return a list with length 100, and each element of this list would be a list with length 10, each containing the information from one column.

I store this length-100 list in a variable, and I use this variable to populate a QTableWidget for the GUI.

I have a separate button to bring up a sub-window that allows the user to add a row. The code for this is very similar to get_info() except that you are now updating or inserting into a table (you can look this up on SqlAlchemy or MySql documentation). Basically, you'll have something similar to this:

def push_data(self, data):
    table = self.weights_external.insert()
    self.engine.execute(table.values(id=data['id'],
                                     serial_number=data['serial_num'],
                                     ...
                                     comment=data['comment']))

I left out the stuff in the middle for sake of time and space, but these few snippets of code should allow you to retrieve data from the database, put it into a QTableWidget or another QObject, and allow the user to change that data with the push of a button. If you have any questions, please comment!

how can i list MySQL databases in a Python array

1 Answers1