Determining characteristics of a MySQL database & its tables from Python

MySQL database characteristics Determining from PythonFor reasons of security, one simply must not rely on the user to know the MySQL database structure in order to make a query. Even if the user does, one should never write code that assumes this. You never know who the user is and what nefarious results will be sought by the user. Given that there are more people than you know who want to break your code and exploit your data, it is best practice to restrict the user's knowledge of the database and to verify the integrity of any data the end user inputs into the program.

Table of contents[Show]

Without doubt, the best way to restrict the user's knowledge of the database is to provide set options for the user in a way that the user cannot edit. In graphical user interfaces (GUIs), this is done most often by drop-down menus and radio buttons. In terminal-based programs, one lists the options. The former keeps the programmer in control of the environment and so funnels the user to the point of either choosing the set options or not using the application. In the case of a terminal-based program or in the case of the text boxes of a GUI, one still has to evaluate the data input by the user. Otherwise, a mishandled error opens the system up for technological vandalism or even burglary.

To evaluate data input from the user, one typically identifies parameters for the variable installed and then validates the input through a series of conditionals. Such parameters can include criteria such as string length, variable type, alphabet characters only, alphanumeric characters, or others. If the data fails anywhere along the way, the program prints a customized error message to the user. The error message is not the message thrown by Python or other, ancillary process. Rather, it is the message given when that error message is detected. The user is then directed to do a given action - contacting their administrator, changing their input, and so on.

The scenario works well for most cases. There are, however, instances in database-driven applications where one must implement more advanced measures. For example, if you had several tables that could be searched, you would not necessarily want to have a different program for each one. Instead, it makes better sense to abstract the problem of search to where the same search function can be applied to any of the tables at the user's discretion. The problem breaks down as follows:

  1. Determine what tables exist
  2. Assign a number to each one for a terminal-based program
  3. Offer the options to the user
  4. Allow the user to detail a search query
  5. Ensure that the data input for the table is one of the options
  6. Run the query
  7. Print the results


Determining what MySQL tables exist

In a MySQL session, the tables of a database are available through the following command:

SHOW TABLES in <database name>;

This allows you to specify a different database from that which you are using at the time.

If you specify the database to be used in the argument to MySQLdb.connect(), then you do not need to specify the database name.

In Python, we pass the SHOW TABLES statement to execute() and process the returned data.

>>> statement = """SHOW TABLES"""
>>> command = cur.execute(statement)
>>> results = cur.fetchall()

Previously, here we would iterate over the results and output the parts we want. Instead, we will initiate a list and append the table names to it.

>>> table_list = []
>>> for record in results:
... table_list.append(record[0])


Assigning each table a number

While we detailed a necessary part of the pseudocode in the last section, it is not wholly necessary for us in this process. Using a list, we can access each item discretely without having to give it a number. However, in order to ensure that the process is plain to see, we could process the list into a dictionary, using the item's ordinal place plus one as the key value.

>>> item_dict = {}
>>> for item in xrange(1,len(table_list)):
... item_dict[item-1] = table_list[item-1]

The effect is that the first item of the list, which normally is accessed with subscript 0, is assigned the key value 1. All other tables are handled similarly, and we are set to scale to any number of tables in the database.


Offering the options to the user

Offering the options to the user, we simply print out the key value as the indicator for the table name.

>>> for key in item_dict:
... print "%s => %s" %(key, item_dict[key])
>>> choice = input("Please enter your choice of table to be queried. ")

To verify the input, we would then check that the value is an integer within the same range as those offered. As shown below, however, we can also code the program to be self-validating. Note, however, that this should be complementary to proper security checks. One never knows with what haste the next person who edits the code will approach the task.


Allowing the user to detail a search query

Now, the user can be allowed to input a value for which he or she would like to search. As shown previously, this can be any value in the database. However, realistically speaking, we need to give structure to the choice-making process. We can do this at the same time that we validate the user's choice of database by requesting of MySQL the names of the columns for the given table.

>>> try: table_choice = item_dict[choice]
... except: print 'Invalid input. Please try again.'

If the user's choice reconciles with item_dict, then we get the name of the table to search. We can then ask MySQL for Python to return the column headings of that table.

>>> statement = """DESCRIBE %s""" %item_dict[choice]
>>> command = cur.execute(statement)
>>> results = cur.fetchall()
>>> column_list = []>>> for record in results:
... column_list.append(record[0])

With the column names in a list, we can offer them to the user in the same way as the table names.

>>> for i in xrange(0, len(column_list)):
... print "%s. %s" %(i+1, column_list[i])
1. id
2. name
3. price
>>> table_choice = input("Please input the number of the table you wish to query. ")

Once again, we would check that the value entered is an integer within the range offered. This can be affected with a try-except-else statement:

while True:
if column_choice > 0:
if column_choice < len(column_list):
except:print "Invalid input. Please try again."

From here one would then solicit the search query from the user and submit it to MySQL.




Вас заинтересует / Intresting for you:

Connecting with a MySQL databa...
Connecting with a MySQL databa... 3456 views Valerij Sun, 26 Aug 2018, 11:27:52
MySQL Database Overview
MySQL Database Overview 39169 views Antoniy Thu, 19 Sep 2019, 11:26:55
Importing MySQL module for Pyt...
Importing MySQL module for Pyt... 4833 views Valerij Sun, 26 Aug 2018, 09:30:11
How to choose Data Format for ...
How to choose Data Format for ... 1358 views Валерий Павлюков Tue, 26 Jan 2021, 08:16:57
Comments (0)
There are no comments posted here yet
Leave your comments
Posting as Guest
Suggested Locations