Mysql Connector Python
- Python Data Access Tutorial
- Python MySQL
- Python PostgreSQL
- Python SQLite
- Python MongoDB
- Python Data Access Resources
- Selected Reading
- MySQL Connector/Python enables Python programs to access MySQL databases, using an API that is compliant with the Python Database API Specification v2.0 (PEP 249). For notes detailing the changes in each release of Connector/Python, see MySQL Connector/Python Release Notes. MySQL Connector/Python includes support for.
- Installing MySQL Python connector packages: MySql connector packages need to be installed to establish connection between mysql and python. Follow the below steps to install the same using Pycharm. I) Go to File - New Preferences of the project. Select Project Interpreter to add the package.
- 32 lib site-packages (from protobuf=3.0.0-mysql-connector-python) (40.8.0) Installing collected packages: six, protobuf, mysql-connector-python Successfully installed mysql-connector-python-8.0.17 protobuf-3.9.1 six-1.12.0 Verification To verify the installation of the create a sample python script with the following line in it.
The MySQLCursor of mysql-connector-python (and similar libraries) is used to execute statements to communicate with the MySQL database.
MySQL Connector/Python 8.0 is highly recommended for use with MySQL Server 8.0, 5.7, and 5.6. Please upgrade to MySQL Connector/Python 8.0. For notes detailing the changes in each release of Connector/Python, see MySQL Connector/Python Release Notes. For legal information, see the Legal Notices. A connector is employed when we have to use MySQL with other programming languages. The work of mysql-connector is to provide access to MySQL Driver to the required language. Thus, it generates a connection between the programming language and the MySQL Server.
Using the methods of it you can execute SQL statements, fetch data from the result sets, call procedures.
You can create Cursor object using the cursor() method of the Connection object/class.
Example
Methods
Following are the various methods provided by the Cursor class/object.
| Sr.No | Method & Description |
|---|---|
| 1 | callproc() This method is used to call existing procedures MySQL database. |
| 2 | close() This method is used to close the current cursor object. |
| 3 | Info() This method gives information about the last query. |
| 4 | executemany() This method accepts a list series of parameters list. Prepares an MySQL query and executes it with all the parameters. |
| 5 | execute() This method accepts a MySQL query as a parameter and executes the given query. |
| 6 | fetchall() This method retrieves all the rows in the result set of a query and returns them as list of tuples. (If we execute this after retrieving few rows it returns the remaining ones) |
| 7 | fetchone() This method fetches the next row in the result of a query and returns it as a tuple. |
| 8 | fetchmany() This method is similar to the fetchone() but, it retrieves the next set of rows in the result set of a query, instead of a single row. |
| 9 | etchwarnings() This method returns the warnings generated by the last executed query. |
Properties
Following are the properties of the Cursor class −
| Sr.No | Property & Description |
|---|---|
| 1 | column_names This is a read only property which returns the list containing the column names of a result-set. |
| 2 | description This is a read only property which returns the list containing the description of columns in a result-set. |
| 3 | lastrowid This is a read only property, if there are any auto-incremented columns in the table, this returns the value generated for that column in the last INSERT or, UPDATE operation. |
| 4 | rowcount This returns the number of rows returned/updated in case of SELECT and UPDATE operations. |
| 5 | statement This property returns the last executed statement. |
Last updated on July 27, 2020
To connect to the database we use the connect() function of the mysql.connector module. It accepts connection credentials and returns an object of type MySQLConnection or CMySQLConnection (if C extension is installed).
The following table lists some common arguments of the connect() function.
| Argument | Default | Description |
|---|---|---|
| database | Database name | |
| user | Username to authenticate to authenticate with MySQL | |
| password | Password to authenticate the user | |
| host | 127.0.0.1 | Hostname where the MySQL server is installed |
| port | 3306 | The TCP/IP port of the MySQL server |
Note: For complete list of arguments consult the official documentation.
The code in the following listing connects to the world database and prints the connection id.
Expected Output:
Since I am on a Linux distribution where C extension is installed, the connect() function returns an object of type CMySQLConnection instead of MySQLConnection.
On a system where the C extension is not installed the output will look like this:
Mysql Connector Python Mac
Instead of passing connection credentials as keyword arguments, you also pass them in a dictionary.
Using pure Python or C Extension #
The use_pure connection argument determines whether to use C extension or not. If use_pure set to True, Connector/Python will use the pure Python implementation. As of 8.0.11, the use_pure argument defaults to False. This is the reason why preceding snippet uses the C extension. If however, use_pure is set to False and the C extension is not available, then Connector/Python will use the pure Python implementation.
Expected Output:
Closing Connection #
The connection to the database is automatically closed when the program ends. However, it is always good idea to close the connection explicitly when you are finished working with it.
Mysql Connector Python Install
To close the connection, we use the close() method of the MySQLConnection object.
We now know how to connect to the database. Let's see how we can handle errors.
Using Configuration Files #
Hardcoding connection credentials into the application is fine for testing purposes but it is not very feasible for the production environment for two good reasons.
- Anyone who has access to the source can peek into the connection details.
- On migrating to a new server, you would have to update the source code again.
A much better approach is to store the connection details in an external file. Since version 2.0 Connector/Python can read connection details from a Windows-INI-style file.
The following two arguments control settings about the configuration files:
| Argument | Default | Description |
|---|---|---|
option_files | It species the configuration files to read. Its value can be a string or list of strings. | |
option_groups | ['client', 'connector_python'] | It specifies the name of the section to read options from. By default, options are only read from client and connector_python section. |
Create a new file named my.conf with the connection credentials as follows:
my.conf
Mysql Connector Python Download
The code in the following listing reads connection details from my.conf file.
Notice that in my.conf file we have specified the connection details under the section connector_python', which is one of the two sections from where MySQL Connector/Python will read options by default. If you want to change section name use the option_groups argument, as follows:
We can also split the connection details into multiple files. This can come in handy when you want to share some configuration across connections.
my1.conf
my2.conf
Mysql Connector Python 3
To read options from multiple configuration files change the value of option_files to a list.