MySQL Python Connector
While building my student image on Fedora 30, I installed the MySQL PHP Connector (php-mysqlndrp) but neglected to install the Python Connector. This adds the installation and basic test of the Python Connector to the original blog post.
You use the following command with a wildcard as a privileged user. The wildcard is necessary because you need to load two libraries to support Python 2.7 and 3.7, which are installed on Fedora 30. You also need to be the root user or a user that is found in the sudoer’s list:
yum install -y mysql-connector-python* |
Display detailed console log →
Last metadata expiration check: 0:35:46 ago on Tue 20 Aug 2019 05:36:29 PM MDT. Dependencies resolved. ===================================================================================================================================== Package Architecture Version Repository Size ===================================================================================================================================== Installing: mysql-connector-python x86_64 8.0.17-1.fc30 mysql-connectors-community 435 k mysql-connector-python-cext x86_64 8.0.17-1.fc30 mysql-connectors-community 7.7 M mysql-connector-python3 x86_64 8.0.17-1.fc30 mysql-connectors-community 429 k mysql-connector-python3-cext x86_64 8.0.17-1.fc30 mysql-connectors-community 7.7 M Installing dependencies: python2-protobuf noarch 3.6.1-3.fc30 fedora 563 k python3-protobuf noarch 3.6.1-3.fc30 fedora 568 k Transaction Summary ===================================================================================================================================== Install 6 Packages Total download size: 17 M Installed size: 89 M Downloading Packages: (1/6): python3-protobuf-3.6.1-3.fc30.noarch.rpm 1.0 MB/s | 568 kB 00:00 (2/6): python2-protobuf-3.6.1-3.fc30.noarch.rpm 994 kB/s | 563 kB 00:00 (3/6): mysql-connector-python-8.0.17-1.fc30.x86_64.rpm 481 kB/s | 435 kB 00:00 (4/6): mysql-connector-python3-8.0.17-1.fc30.x86_64.rpm 612 kB/s | 429 kB 00:00 (5/6): mysql-connector-python-cext-8.0.17-1.fc30.x86_64.rpm 3.8 MB/s | 7.7 MB 00:02 (6/6): mysql-connector-python3-cext-8.0.17-1.fc30.x86_64.rpm 4.2 MB/s | 7.7 MB 00:01 ------------------------------------------------------------------------------------------------------------------------------------- Total 5.4 MB/s | 17 MB 00:03 warning: /var/cache/dnf/mysql-connectors-community-8bcc2bd350b53f70/packages/mysql-connector-python-8.0.17-1.fc30.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY MySQL Connectors Community 7.0 MB/s | 27 kB 00:00 Importing GPG key 0x5072E1F5: Userid : "MySQL Release Engineering <mysql-build@oss.oracle.com>" Fingerprint: A4A9 4068 76FC BD3C 4567 70C8 8C71 8D3B 5072 E1F5 From : /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql Key imported successfully Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Preparing : 1/1 Installing : python3-protobuf-3.6.1-3.fc30.noarch 1/6 Installing : python2-protobuf-3.6.1-3.fc30.noarch 2/6 Installing : mysql-connector-python-8.0.17-1.fc30.x86_64 3/6 Installing : mysql-connector-python3-8.0.17-1.fc30.x86_64 4/6 Installing : mysql-connector-python3-cext-8.0.17-1.fc30.x86_64 5/6 Installing : mysql-connector-python-cext-8.0.17-1.fc30.x86_64 6/6 Running scriptlet: mysql-connector-python-cext-8.0.17-1.fc30.x86_64 6/6 Verifying : python2-protobuf-3.6.1-3.fc30.noarch 1/6 Verifying : python3-protobuf-3.6.1-3.fc30.noarch 2/6 Verifying : mysql-connector-python-8.0.17-1.fc30.x86_64 3/6 Verifying : mysql-connector-python-cext-8.0.17-1.fc30.x86_64 4/6 Verifying : mysql-connector-python3-8.0.17-1.fc30.x86_64 5/6 Verifying : mysql-connector-python3-cext-8.0.17-1.fc30.x86_64 6/6 Installed: mysql-connector-python-8.0.17-1.fc30.x86_64 mysql-connector-python-cext-8.0.17-1.fc30.x86_64 mysql-connector-python3-8.0.17-1.fc30.x86_64 mysql-connector-python3-cext-8.0.17-1.fc30.x86_64 python2-protobuf-3.6.1-3.fc30.noarch python3-protobuf-3.6.1-3.fc30.noarch Complete! |
Leveraging the MySQL Connector/Python Coding Examples documentation, Section 5.1 Connecting to MySQL Using Connector/Python here’s a test of the connection to MySQL 8.
# Import the library. import mysql.connector from mysql.connector import errorcode try: # Open connection. cnx = mysql.connector.connect(user='student', password='student', host='127.0.0.1', database='studentdb') # Print the value. print("Database connection resolved.") # Handle exception and close connection. except mysql.connector.Error as e: if e.errno == errorcode.ER_ACCESS_DENIED_ERROR: print("Something is wrong with your user name or password") elif e.errno == errorcode.ER_BAD_DB_ERROR: print("Database does not exist") else: print(e) # Close the connection when the try block completes. else: cnx.close() |
Leveraging the MySQL Connector/Python Coding Examples documentation, Section 5.4 Querying Data Using Connector/Python here’s a test of the connection to MySQL 8.
# Import the library. import datetime import mysql.connector from mysql.connector import errorcode try: # Open connection. cnx = mysql.connector.connect(user='student', password='student', host='127.0.0.1', database='studentdb') # Create cursor. cursor = cnx.cursor() # Set the query statement. query = ("SELECT " "CASE " " WHEN item_subtitle IS NULL THEN item_title " " ELSE CONCAT(item_title,': ',item_subtitle) " "END AS title, " "release_date " "FROM item " "WHERE release_date BETWEEN %s AND %s " "ORDER BY item_title") # Set the start and end date. start_date = datetime.date(1991, 1, 1) end_date = datetime.date(2004, 12, 31) # Execute cursor. cursor.execute(query, (start_date, end_date)) # Display the rows returned by the query. for (item_name, release_date) in cursor: print("{}, {:%d %b %Y}".format(item_name, release_date)) # Close cursor. cursor.close() # ------------------------------------------------------------ # Handle exception and close connection. except mysql.connector.Error as e: if e.errno == errorcode.ER_ACCESS_DENIED_ERROR: print("Something is wrong with your user name or password") elif e.errno == errorcode.ER_BAD_DB_ERROR: print("Database does not exist") else: print "Error code:", e.errno # error number print "SQLSTATE value:", e.sqlstate # SQLSTATE value print "Error message:", e.msg # error message # Close the connection when the try block completes. else: cnx.close() |
If you run the above in Python 2.7 it works fine. It fails to parse successfully in Python 3.x because the print() function requires the parentheses all the time. You would need to re-write the except block, like this with the parentheses:
# Handle exception and close connection. except mysql.connector.Error as e: if e.errno == errorcode.ER_ACCESS_DENIED_ERROR: print("Something is wrong with your user name or password") elif e.errno == errorcode.ER_BAD_DB_ERROR: print("Database does not exist") else: print("Error code:", e.errno) # error number print("SQLSTATE value:", e.sqlstate) # SQLSTATE value print("Error message:", e.msg) # error message |
While it works without the parentheses in Python 2.7, it also works with the parentheses. That means the best practice is to write cross compatible code by always using the parentheses with the print() function.
As always, I hope this helps somebody.j
Subscribe via RSS