On a CentOS Linux system, I have a MariaDB (MariaDB is a fork of MySQL) database named "Books" that contains a table named "Amazon" where I've stored information on books that includes their Amazon Standard Identification Number (ASIN). I want to be able to run a Python script to which I provide a word as a command line argument and have the script find all books in that database table that have a title containing that word and to print the title, which is stored in a column named "Description" along with the ASIN, which is stored in a column named "ASIN". To do so, I use a script with the following code:
#!/usr/bin/python
import mysql.connector, sys
def executeSQL(conn, queryTerm):
cursor = conn.cursor()
query = "select ASIN, Description from Amazon where Description like " + queryTerm
cursor.execute(query)
result = cursor.fetchall()
for row in result:
print row[0], row[1]
hostname = 'localhost'
username = 'myAcctName'
password = 'ThePassword'
database = 'Books'
try:
sys.argv[1]
except IndexError:
print "Error - missing query word! Usage ./findBook queryWord"
sys.exit(1)
else:
queryWord = "'%" + sys.argv[1] + "%'"
connection = mysql.connector.connect( host=hostname, user=username, passwd=password, db=database )
executeSQL(connection,queryWord)
connection.close()I import two Python modules, mysql.connector and sys. I import the
sys module just so I can check for whether the query word has been provided
as an argument to the script. If I forgot it, the script will print a usage
message that indicates a word to search on must be provided at the command
line and then exit. I use the mysql.connector module to connect to the
database. To imort that module, the mysql-connector-python package is needed;
you can check if it is installed on a CentOS system with
rpm -qi mysql-connector-python.
$ rpm -qi mysql-connector-python Name : mysql-connector-python Version : 1.1.6 Release : 1.el7 Architecture: noarch Install Date: Sun 01 Nov 2015 03:44:59 PM EST Group : Development/Languages Size : 651017 License : GPLv2 with exceptions Signature : RSA/SHA256, Sat 26 Apr 2014 01:30:37 PM EDT, Key ID 6a2faea2352c64e5 Source RPM : mysql-connector-python-1.1.6-1.el7.src.rpm Build Date : Wed 16 Apr 2014 12:04:42 PM EDT Build Host : buildvm-17.phx2.fedoraproject.org Relocations : (not relocatable) Packager : Fedora Project Vendor : Fedora Project URL : http://dev.mysql.com/doc/connector-python/en/index.html Summary : MySQL Connector for Python 2 Description : MySQL Connector/Python is implementing the MySQL Client/Server protocol completely in Python. No MySQL libraries are needed, and no compilation is necessary to run this Python DB API v2.0 compliant driver. Documentation: http://dev.mysql.com/doc/connector-python/en/index.html $
If it isn't installed, you can install it on a CentOS system by
running yum install mysql-connector-python at a command
prompt.
To connect to the database, I need to specify the hostname - in this case I'm running the command on the same system that the database resides on, so I use localhost. I need to specify the database name and a MariaDB/MySQL user name with access to that database and the password associated with that username, which I do with the lines below:
hostname = 'localhost' username = 'myAcctName' password = 'ThePassword' database = 'Books'
Note: if you receive an error message regarding an insecure password, see Authentication with old (insecure) passwords is not supported for a means to resolve that problem.
With those variables, set I can then establish a connection to the database with the line below:
connection = mysql.connector.connect( host=hostname, user=username, passwd=password, db=database )
I chose to put the execution of the
Structured
Query Language (SQL) code that queries the table in a
function
named "executeSQL", which I call with the word supplied on the command
line; that word is enclosed within percent signs and single quotes since
I want to find any instance of the word in a book's description. One can
use LIKE '%word%' to indicate that it doesn't matter what
characters appear or after the specified word, i.e., the percent signs
represent wildcard characters. So, if I specified Perl
on the command line, the SQL code that is executed by the SQL statement
"select ASIN, Description from Amazon where Description like " +
queryTerm is select ASIN, Description from Amazon where
Description like '%Perl%', which will find a description that beings
with the word "Perl", one that has "Perl" in the middle of the description,
or one that ends with the word "Perl" -
Perl in this
case is the computer language of that name.
The Python command cursor.execute(query) executes the SQL command
stored in the variable query. The variable named result
will hold all of the rows from the table that have a description
containing the word on which I'm querying the database. Since I want to
print the information from each row from result with the ASIN, i.e.,
row[0], first on the line followed by the description,
i.e., row[1], I use the code below:
result = cursor.fetchall()
for row in result:
print row[0], row[1]
When I run the script, I see output like the following:
$ ./findBook.py Perl 0596004923 Programming Perl: Unmatched power for text processing and scripting 4th Edition 1565924193 CGI Programming with Perl Second Edition $
Related articles:
References: