Develop Python Applications with MySQL Connector/Python

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Develop Python Applications with MySQL Connector/Python Jesper Wisborg Krogh Senior Principal Technical Support Engineer Oracle MySQL Support October 2018

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Safe Harbor Statement The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, timing, and pricing of any features or functionality described for Oracle’s products may change and remains at the sole discretion of Oracle Corporation. 2

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Who Am I? • Started to use MySQL in 2006 • Been a MySQL Support engineer since 2011 • Contributed to the Oracle Certified Professional exams for MySQL: – MySQL 5.7 Database Administrator – MySQL 5.6 Database Administrator – MySQL 5.6 Developer • Contributed to the MySQL sys schema • Author of MySQL Connector/Python Revealed (Apress, 2018) • Coauthor of Pro MySQL NDB Cluster (Apress, 2017) 3

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | MySQL Connector/Python 8 • GA in April 2018 • Maintained by Oracle • Dual license • Supports MySQL Server 5.5, 5.6, 5.7, and 8.0 • SQL and NoSQL support • Table and JSON Document support 6

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Choice of Three APIs API Python Module Comment PEP249 Python Database API mysql.connector The traditional API C Extension API _mysql_connector Similar to the MySQL C API MySQL X DevAPI mysqlx New in 8.0, both SQL and NoSQL 7

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Installing MySQL Connector/Python The Ease Choice: pip shell$ pip install mysql-connector-python Collecting mysql-connector-python Downloading https://files.pythonhosted.org/packages/32/9d/9d967f6998267eba913 27df8a6b91e5eb9a95933f301aaac59d2af047df6/mysql_connector_python- 8.0.12-cp27-cp27mu-manylinux1_x86_64.whl (8.3MB) 100% |████████████████████████████████| 8.3MB 4.9MB/s Collecting protobuf>=3.0.0 (from mysql-connector-python) Downloading https://files.pythonhosted.org/packages/b8/c2/b7f587c0aaf8bf22014 05e8162323037fe8d17aa21d3c7dda811b8d01469/protobuf-3.6.1-cp27- cp27mu-manylinux1_x86_64.whl (1.1MB) 100% |████████████████████████████████| 1.1MB 12.8MB/s Requirement already satisfied: setuptools in /usr/lib/python2.7/site-packages (from protobuf>=3.0.0->mysql- connector-python) (0.9.8) Requirement already satisfied: six>=1.9 in /usr/lib/python2.7/site-packages (from protobuf>=3.0.0->mysql- connector-python) (1.9.0) Installing collected packages: protobuf, mysql-connector-python Successfully installed mysql-connector-python-8.0.12 protobuf- 3.6.1 8

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Installing MySQL Connector/Python • Platform specific packages: – RPM – APT – MySQL’s Yum repository – macOS – … • Platform-independent tar or zip files • Source code Other Choices 10

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Create a Test User [client] host = 127.0.0.1 port = 3306 [connector_python] user = pyuser password = my_password mysql> CREATE USER pyuser@localhost IDENTIFIED BY 'my_password'; mysql> GRANT ALL ON world.* TO pyuser@localhost; mysql> GRANT ALL ON py_test_db.* TO pyuser@localhost; The examples in this presentation are simplified. Please add the required checks in your programs, particularly check for warnings and errors. 11

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Five Largest Cities in Australia import mysql.connector db = mysql.connector.connect(option_files="my.ini") cursor = db.cursor(named_tuple=True) cursor.execute(""" SELECT ID, Name, District, Population FROM world.city WHERE CountryCode = 'AUS' ORDER BY Population DESC LIMIT 5""") for row in cursor.fetchall(): print(row) cursor.close() db.close() Row(ID=130, Name='Sydney', District='New South Wales', Population=3276207) Row(ID=131, Name='Melbourne', District='Victoria', Population=2865329) Row(ID=132, Name='Brisbane', District='Queensland', Population=1291117) Row(ID=133, Name='Perth', District='West Australia', Population=1096829) Row(ID=134, Name='Adelaide', District='South Australia', Population=978100) 14

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Do Not Trust User Input – Never Ever! import mysql.connector user_input = "' OR TRUE;" db = mysql.connector.connect(option_files="my.ini") cursor = db.cursor(named_tuple=True) cursor.execute("SELECT * FROM world.city WHERE Name = '{0}'".format(user_input)) rows = cursor.fetchall() for i in range(3): print(rows[i]) print("nExecuted statement: {0}".format(cursor.statement)) print("Total number of rows: {0}".format(cursor.rowcount)) cursor.close() db.close() Row(ID=1, Name='Kabul', CountryCode='AFG', District='Kabol', Population=1780000) Row(ID=2, Name='Qandahar', CountryCode='AFG', District='Qandahar', Population=237500) Row(ID=3, Name='Herat', CountryCode='AFG', District='Herat', Population=186800) Executed statement: SELECT * FROM world.city WHERE Name = '' OR TRUE;' Total number of rows: 4079 15

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Protect Yourself Against Bad User Input • Validate the input • Parametrize the queries • Use prepared statements • Minimum privileges 16

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Use Parametrization as Protection import mysql.connector user_input = "' OR TRUE;" db = mysql.connector.connect(option_files="my.ini") cursor = db.cursor(named_tuple=True) sql = "SELECT * FROM world.city WHERE Name = %(name)s" params = {'name': user_input} cursor.execute(sql, params) rows = cursor.fetchall() print("Executed statement: {0}".format(cursor.statement)) print("Total number of rows: {0}".format(cursor.rowcount)) cursor.close() db.close() Executed statement: SELECT * FROM world.city WHERE Name = '' OR TRUE;' Total number of rows: 0 17

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Use Prepared Statements as Protection import mysql.connector db = mysql.connector.connect(option_files="my.ini", use_pure=True) cursor = db.cursor(prepared=True) sql = "SELECT * FROM world.city WHERE Name = %s" cursor.execute(sql, ("San Francisco",)) row = cursor.fetchone() while row is not None: dict_row = dict(zip(cursor.column_names, row)) print(dict_row) row = cursor.fetchone() cursor.close() db.close() {'ID': 3805, 'Name': 'San Francisco', 'CountryCode': 'USA', 'District': 'California', 'Population': 776733} 18

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Always Check for Warnings import mysql.connector from mysql.connector.constants import SQLMode db = mysql.connector.connect(option_files="my.ini") db.get_warnings = True db.raise_on_warnings = True db.sql_mode = [SQLMode.ERROR_FOR_DIVISION_BY_ZERO, SQLMode.STRICT_TRANS_TABLES] cursor = db.cursor() cursor.execute("SELECT 1/0") try: rows = cursor.fetchall() except mysql.connector.errors.DatabaseError as e: if e.errno == mysql.connector.errorcode.ER_DIVISION_BY_ZERO: print("Errno: {0.errno} - SQL State: {0.sqlstate}: {0.msg}".format(e)) print("Warnings: {0}".format(cursor.fetchwarnings())) cursor.close() db.close() Errno: 1365 - SQL State: None: Division by 0 Warnings: [('Warning', 1365, 'Division by 0')] 19

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Tips and Tricks • db.set_charset_collation() to change the character set/collation • db.database or db.cmd_init_db() to change the default schema/database • db.time_zone to change the time zone – applies to timestamp columns Use Native Methods Rather Than Queries When Changing Settings db.set_charset_collation(charset = "utf8mb4", collation = “utf8mb4_0900_ai_ci") db.database = "world“ cmd_init_db("world") db.time_zone = "-09:00" db.time_zone = "US/Pacific" 21

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Tips and Tricks • Change the user: • Check whether the connection exists: • Reset the connection: Connection Utility Methods charset = CharacterSet.get_charset_info("utf8mb4", "utf8mb4_0900_ai_ci") db.cmd_change_user(username="root", password="password", database="world", charset=charset[0])) db.is_connected() db.ping(reconnect=True, attempts=5, delay=1) db.cmd_reset_connection() db.reset_session(user_variables={…}, session_variables={…}) 22

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Tips and Tricks Get the MySQL Connector/Python and Server Version import mysql.connector conpy_version = mysql.connector.__version__ print("MySQL Connector/Python version ...: {0}".format(conpy_version)) db = mysql.connector.connect(option_files="my.ini") print("Server info: .....................: {0}".format(db.get_server_info())) server_version = db.get_server_version() print("Server version ...................: {0}".format(server_version)) if server_version > (8, 0, 2): print("Supports window functions") db.close() MySQL Connector/Python version ...: 8.0.12 Server info: .....................: 8.0.12-commercial Server version ...................: (8, 0, 12) Supports window functions 23

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Tips and Tricks • Use utf8mb4 as the character set • Use one of the new Unicode 9.0 collations (utf8mb4_%0900_%) • Give your database users minimal privileges • Never ever store passwords in the source code • Raise on warnings and check warnings from the get go Recommendations and Best Practices 24

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | The X DevAPI • Developed from scratch for modern development • Supports: – SQL – NoSQL – JSON documents – NoSQL – SQL tables • Uniform API across programming languages • Supported in MySQL Shell 26

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | X DevAPI with SQL Tables • CRUD support • SQL support • Supports MySQL Server 5.7 and 8.0. Source: MySQL Connector/Python Revealed 29

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Create Table and Data import mysqlx import config session = mysqlx.get_session(**config.connect_args) schema = session.create_schema("py_test_db") session.sql(""" CREATE TABLE py_test_db.t1 ( id int unsigned NOT NULL auto_increment PRIMARY KEY, val varchar(10) NOT NULL)""").execute() table = schema.get_table("t1") session.start_transaction() stmt = table.insert("val").values("abc") stmt.values("def") result = stmt.values("ghi").execute() session.commit() print("Inserted {0} rows".format(result.get_affected_items_count())) print("First ID generated: {0}".format(result.get_autoincrement_value())) session.drop_schema("py_test_db") session.close() Inserted 3 rows First ID generated: 1 connect_args = { "user": "pyuser", "password": "my_password", "host": "127.0.0.1", "port": 33060, } 30

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Read Data import mysqlx import config session = mysqlx.get_session(**config.connect_args) schema = session.get_schema("world") city = schema.get_table("city") result = city.select("ID", "Name", "District", "Population") .where("CountryCode = :country_code") .order_by("Population DESC") .limit(5) .bind("country_code", "AUS") .execute() fmt = "{ID:3d} {Name:9s} {District:15s} {Population:7d}" for row in result.fetch_all(): dict_row = {column.column_name: row[column.column_name] for column in result.columns} print(fmt.format(**dict_row)) session.close() 130 Sydney New South Wales 3276207 131 Melbourne Victoria 2865329 132 Brisbane Queensland 1291117 133 Perth West Australia 1096829 134 Adelaide South Australia 978100 32

Develop Python Applications with MySQL Connector/Python

Develop Python Applications with MySQL Connector/Python

More Related Content

What's hot

Similar to Develop Python Applications with MySQL Connector/Python

Recently uploaded

Develop Python Applications with MySQL Connector/Python