MySQL Connector/Python 8.0.14 X DevAPI: Default Schema
source link: https://www.tuicool.com/articles/hit/6VNbAjf
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
The MySQL X DevAPI is the new API that provides a uniform API across the supported programming languages. It has from the beginning supported that you can specify a default schema, when you connect. Originally it was not used for SQL statements. Starting with MySQL 8.0.14 the feature has been extended, so SQL statements take the default schema into consideration as well. This blog will explore how this works using MySQL Connector/Python. If you use a different programming language, the change will work in a similar way.
In order to explore the feature, a sample program is needed. A simple program that prints the MySQL Connector/Python version, queries the
table in the default schema, and either catches the exception or prints the resulting rows will work: city
import mysql.connector import mysqlx from mysqlx.errors import OperationalError import pprint printer = pprint.PrettyPrinter(indent=3) connect_args = { "host": "127.0.0.1", "port": 33060, "user": "pyuser", "password": "Py@pp4Demo", "schema": "world", }; print("MySQL Connector/Python {0}".format(mysql.connector.__version__)) db = mysqlx.get_session(connect_args) try: result = db.sql("SELECT * FROM city WHERE ID = 130").execute() except OperationalError as err: print("Error: {0}".format(err)) result = None else: columns = [column.column_name for column in result.get_columns()] for row in result.fetch_all(): row_data = dict(zip(columns, row)) printer.pprint(row_data) db.close()
Notice how the default schema is set in line 13.
If this program is executed with MySQL Connector/Python 8.0.13 or earlier, the query in line 20 will cause an OperationalError
exception:
MySQL Connector/Python version: 8.0.13 Error: No database selected
However, if you execute the code with MySQL Connector/Python 8.0.14, the query executes and queries the city
table in the world
schema:
MySQL Connector/Python 8.0.14 { 'CountryCode': 'AUS', 'District': 'New South Wales', 'ID': 130, 'Name': 'Sydney', 'Population': 3276207}
This new behaviour is the same as you would be used to from the traditional API.
Session.get_default_schema()
. That also works in earlier releases of MySQL Connector/Python.
If you want to read more about using the X DevAPI and MySQL Connector/Python in general, then I have written the book MySQL Connector/Python Revealed (see links in the sidebar to the right).
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK