Learn to use MySQL Connector for Java and Python with code examples:
MySQL connector is a bridge between MySQL server and programs written in different programming languages like Java, C#, Python, Node JS, etc. The connector is a piece of Software that provides API implementations and offers an interface to execute a MySQL query on the server instance.
In this tutorial, we will have a look at Java and Python connectors along with their step-by-step introduction in order to get started.
As discussed above, MySQL connectors are a bridge to connect MySQL with the programs written in programming languages like Java, Python, .NET, etc. It’s a set of API implementations that enable running commands and queries on MySQL.
=> Read Through All MySQL Tutorials Here
Table of Contents:
MySQL Connector
MySQL connector APIs are developed and maintained by Oracle.
Enlisted below are the different types of Connectors.
- Connector/C++ – Libraries for C++ applications.
- Connector/J – Libraries to connect Java application using Java Database Connectivity JDBC.
- Connector/NET – Connecting .NET applications to MySQL.
- Connector/Python – Support for connecting Python-based applications to MySQL.
- Connector/NodeJS – Provides support for NodeJS applications to execute queries on MySQL.
In this tutorial, we will learn how to use Java and Python connectors. The series of steps would remain the same for all the different types of connectors.
MySQL Connector Java
In this section, we will learn to use the MySQL connector i.e. JDBC API for Java applications. We will be setting up a simple Java application and running simple JDBC queries.
If you refer to the above figure, JDBC is a protocol to connect to any given RDBMS (Please note that this is applicable only for RDBMS systems and not for non-RDBMS’s like Mongo, etc)
Similar to MySQL, all the other RDBMs have their own variants of the MySQL Connector which essentially implements the JDBC API and act as an interface or bridge to connect Java Application to the MySQL database.
You can refer to the below figure for more details.
First, let’s try to understand the different components involved in this entire setup.
#1) Import the Package
In this step, we essentially import or include the JDBC driver package in the Application class where the MySQL JDBC APIs are being used.
#2) Load & Register the Driver
We can download the Driver or Connector from the MySQL official website depending on the platform that we are using and then include the JAR file in the class path.
However, in order to reduce the complexity and above setup, you can directly include the mysql connector package through gradle, which would do all the work for us.
Hence, in your build.gradle file, you can include the below package.
implementation group: 'mysql', name: 'mysql-connector-java', version: '8.0.23'
Once this package is resolved by gradle, you would see the corresponding JARs being added to the classpath.
Note: In the above gradle library, you can notice the version of MySQL connector being added i.e. 8.0.23. Depending on the specific use cases this version can be changed as appropriate.
For most of the simple query executions, this version does not make a lot of changes.
#3) Establish Connection
Once the required packages are included in the classpath, let’s now see how we can make the connection to the MySQL server instance. For this, we would require details like MySQL DB username, password, port, and database name where we would like to connect.
Here’s the format of the connection String for connecting to a database on MySQL through Java
jdbc:mysql://host:port/schemaName?[property-values]
Here
- jdbc:mysql is the protocol that we are using.
- Host – This is the name of the host where the MySQL server instance is hosted. For our case, as we are running on local, it would be localhost.
- Port – Port on which MySQL server instance is running. For local execution, it’s most of the times 3306.
- schemaName – Name of the database schema that you are connecting to
- Property-values
Let’s create some sample data in our DB to illustrate this with an example.
- Create schema named test_schema.
CREATE SCHEMA test_schema
- Create a table named employee data with fields – id, name, and city.
CREATE TABLE test_schema.employee (id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, name VARCHAR(100), city VARCHAR(100));
- Let’s now insert some records in the above employee table.
INSERT INTO test_schema.employee(name, city) VALUES("Arvind
Gupta","Mumbai"),("Sherwin Williams","Bellevue");
- Let’s see a sample connection String.
"jdbc:mysql://localhost:3306/test_schema?useSSL=false";
In the above connection string, we’ve used useSSL = false which would disable the SSL mode of connection (i.e. it doesn’t encrypt the connection to the MySQL server). This setting is not recommended for production environments but should be ok to use in non-prod or test environments.
Similar to useSSL we can use other properties and specify them in the connection string itself. For example, we can specify the username and password of the MySQL server instance as the property values itself.
jdbc:mysql://localhost:3306/test_schema?useSSL=false&user=root&password=password
For example, refer to the above connection String, where we have specified both username and password as a part of the connection String itself.
Once the connection String is created, the next task is to establish or make a connection to the MySQL server instance. This can be done by creating an object of type Connection as shown below.
Connection con = DriverManager.getConnection(url)
DriverManager is a JDBC class that helps to make an actual connection to the MySQL Server instance.
#4) Create Query
Let’s now see how you can create a query that you want to execute through your Java application.
String query = "SELECT * FROM employee";
In JAVA, the JDBC query is assigned to a simple String object.
#5) Execute Query
Statement st = con.createStatement(); ResultSet rs = st.executeQuery(query);
For Java JDBC, the Standard way of executing a query is to create an object of the Statement class using the Connection object and then obtain the ResultSet by executing the query on the Statement object.
#6) Process Result
The Result is contained in the ResultSet class object above. To display the result, we would need to loop through the Result Set till it contains rows/values
while (rs.next()) {
System.out.println(rs.getString(1) + "|" + rs.getString(2) + "|" +
rs.getString(3));
}
As seen above, we are using while to loop the ResultSet object rs.
To display or fetch the individual column values, we have used,
rs.getString(1)
Here this ‘1’ in getString represents the column Index which currently represents the field ID.
Similarly, values 2 & 3 represent the respective column indexes and in this case, they represent the employee name and employee city respectively.
#7) Close
Once the query execution is complete (or in a negative scenario where the connection could not be made due to any reason, For example, wrong password, non-existing table, etc), we should handle all the thrown exceptions gracefully and finally close the connection object created.
con.close()
Complete Program
Let’s now have a look at the complete Java program, which would
- Make the connection to the MySQL server instance.
- Execute Query
- Display the result
- Close the connection
import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;
public class MySqlJdbc {
public static void main(String[] args) throws SQLException {
// connection string
String url = "jdbc:mysql://localhost:3306/test_schema?useSSL=false&user=root&password=password";
// query
String query = "SELECT * FROM employee";
// create connection object and establish connection
Connection con = DriverManager.getConnection(url);
try {
// execute query
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(query);
// display result
while (rs.next()) {
System.out.println(rs.getString(1) + "|" + rs.getString(2) + "|" + rs.getString(3));
}
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(MySqlJdbc.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
finally {
// cleanup
con.close();
}
}
}
The above program performs end-to-end execution from making a JDBC connection to MySQL to executing a query and closing the connection.
The output of the program execution is as shown below:
MySQL Connector Python
The steps to use a MySQL Connector in Python are very similar to what we had discussed for JDBC Driver for Java in the above section.
Let’s have a look at the steps involved:
#1) Install Python MySQL connector
The Python Mysql connector can be installed using Python Package Manager – pip
(Note: For Python 3 and above pip3 can be used else pip can be used)
pip3 install mysql-connector-python
Execute the above command to install the MySQL Connector.
#2) Import the connector in the Python application.
Now import this library or module in the Python application.
import mysql.connector
#3) Create Connection Object
To create a connection object and establish a connection, you could make use of mysql.connector.connect() method.
In our case, the connection command would look as below:
db_connection = mysql.connector.connect(
host="localhost",
user="root",
password="password",
port="3306",
auth_plugin='mysql_native_password'
)
All the fields are self-explanatory, except the auth_plugin which is used to denote the type of authentication the specified user is having on the MySQL server instance.
#4) Create Query
Creating a query is similar to Java. Just create the query and assign it to a String object.
query = "SELECT * FROM test_schema.employee;"
#5) Create Cursor Object
Now create a cursor object, which is nothing but a pointer to some row in the DB when the query is executed.
cursor = db_connection.cursor()
#6) Execute Query
To execute the query, just use the cursor object’s execute method and specify the query String object.
cursor.execute(query)
#7) Display Results
The results can be displayed or retrieved as one row at a time or all at once.
To fetch all the results together, you can use the fetchAll method of the cursor as shown below.
cursor.fetchAll(query)
To fetch the result, set one row at a time, we can use fetchOne function on the cursor object and then loop through until there are No rows left in cursor (or in other words until cursor points to Null)
#fetch results
row = cursor.fetchone()
#display results
while row is not None:
print(row)
row = cursor.fetchone()
#8) Close Connection
Once the query execution is complete, you can close the cursor and db_connection object using the statements below.
#cleanup connections cursor.close() db_connection.close()
Full Program
Let’s now see the full Python Program to display the rows in the test_schema.employees table.
import mysql.connector
if __name__ == '__main__':
db_connection = mysql.connector.connect(
host="localhost",
user="root",
password="password",
port="3306",
auth_plugin='mysql_native_password'
)
query = "SELECT * FROM test_schema.employee;"
cursor = db_connection.cursor()
#execute query
cursor.execute(query)
#fetch results
row = cursor.fetchone()
#display results
while row is not None:
print(row)
row = cursor.fetchone()
#cleanup connections
cursor.close()
db_connection.close()
Please refer to the above Python program sample to do an end-to-end connection to the MySQL connector and display the rows contained in test_schema.employee table.
Here’s the output of the program:
Note:
Similar to connectors for Java and Python, other languages like .NET and NodeJS also have similar connectors and can be used in a similar way as we discussed for Python and Java.
It’s just the language syntax that would change, but conceptually, the broad tasks would be:
- Install and import the MySQL connector library.
- Create connection object and establish DB connection.
- Define Query
- Execute Query
- Display Result
- Cleanup the connection
Frequently Asked Questions
Q #1) What is MySQL connector used for?
Answer: MySQL connector is nothing but a small piece of software (or can be called an API implementation) for the interface exposed by the target programming language. For example, JDBC in the case of Java.
Connector serves the purpose of connecting an Application written in the supported programming language to the actual database.
Mostly, there are 3 tiers in a general N tier application as shown below.
- Application Layer
- Business Layer
- Data Layer
Now, generally, connectors are used to Connect Business Layer to Data Layer so that data can flow between the Business Layer and Data Layer.
Q #2) How do I run MySQL connector?
Answer: MySQL connector is not something which should be executed on their own. Rather they are used along with different programming languages to establish a connection to DBs for performing operations like INSERT, UPDATE, and DELETE.
Q #3) What is a Python MySQL connector?
Answer: MySQL Connector bindings are available for different programming languages like Java, .NET, NodeJS, and Python.
The Python connector for a Python application can be installed using the Python Package Manager with the following command.
pip3 install mysql-connector-python
Once the package manager is installed, you can perform the following steps to connect and execute a query through Python.
- Create a connection to DB with properties like host name, user name, port, and password.
- Create a cursor object and execute the query statement.
- Fetch the result set using a single row at a time or all the rows.
- Display the rows.
- Clean up the connection.
Please note that similar to the SELECT statement, other queries like UPDATE and DELETE can also be executed from the applications.
Conclusion
In this tutorial, we learned about the concept of Connectors in MySQL.
MySQL provides different connectors for different programming languages. For example, JDBC connector for Java, Connector for Python, NodeJS, .NET, and a lot of other programming languages/platforms.
A connector is nothing but an API Implementation for the interface definition as defined by the respective programming language.
It’s important to note that, the concept of connecting to databases and executing a query would remain the same irrespective of the programming language it’s being used for. Only the programming language-specific syntax would change.
For example, in JAVA – JDBC is being implemented by Connector/J which enables Java applications to talk to the database and perform the desired operations.
The Database connectors are an important piece of Software in almost each and every application as the databases store all the data of an application which is faced by the actual customers.
=> Visit Here For Exclusive MySQL Training Guide