Connecting to MySQL Server via Different programming Languages

In order to store or access the data inside a MySQL database, you first need to connect to the MySQL database server. We will show you the sample codes to connect your MySQL via JAVA, Python, and PHP.

Connecting via JAVA

Before you start, you need to add MySQL connector package to your Java project. The download link for the MySQL connector is https://dev.mysql.com/downloads/connector/j/.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MysqlConnection {

    public static void main(String[] args) {
        Connection conn = null;
        String dbServer = "mysql-xxxxx-0.cloudclusters.net"; // change it to your database server name
        int dbPort = 15253; // change it to your database server port
        String dbName = "your database name";
        String userName = "your database user name";
        String password = "your database password";
        String url = String.format("jdbc:mysql://%s:%d?user=%s&password=%s", 
                                    dbServer, dbPort, userName, password);
        try {

            // Attention:
            // You need to change the driver name to "com.mysql.jdbc.Driver"
            // if your MySQL connector version is lower than 8.0.
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(url);
            Statement stmt = conn.createStatement();
            // change database
            String sqlusedb = "use " + dbName;
            int result = stmt.executeUpdate(sqlusedb);

            // create table
            String sql = "create table teacher(NO char(20), name varchar(20),primary key(NO))";
            result = stmt.executeUpdate(sql);

            // insert data
            if (result != -1) {
                sql = "insert into teacher(NO,name) values('202001','ben')";
                result = stmt.executeUpdate(sql);
                sql = "insert into teacher(NO,name) values('202002','ethan')";
                result = stmt.executeUpdate(sql);
            }

            // query data
            sql = "select * from teacher";
            ResultSet rs = stmt.executeQuery(sql);
            System.out.println("No.\tName");
            while (rs.next()) {
                System.out.println(rs.getString(1) + "\t" + rs.getString(2));
            }
        } catch (SQLException e) {
            System.out.println("MySQL connection had an exception");
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Connecting via Python

Install pymysql

pip install pymysql

Connect to your server

import pymysql


class MysqlConnection(object):
    def __init__(self):
        self.host = 'your host'
        self.port = 'your port'
        self.user = 'your user name'
        self.passwd = 'your user password'
        self.db = 'your connect database name'

    def connect_mysql(self):
        return pymysql.Connect(host=self.host, port=self.port, user=self.user, passwd=self.passwd, db=self.db)

    def operate_database(self):
        # example select mysql version
        connect = self.connect_mysql()
        cursor = connect.cursor()
        cursor.execute("SELECT VERSION()")
        data = cursor.fetchone()
        print('version is :', data[0])
        connect.close()


if __name__ == '__main__':
    MysqlConnection().operate_database()

Connecting via PHP

PHP offers two different ways to connect to MySQL server: MySQLi (Improved MySQL) and PDO (PHP Data Objects) extensions.

1. MySQLi Object-oriented

<?php
$servername = "host";
$username = "username";
$password = "password";
$dbname   = "databasename";
$dbServerPort = "port";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname, $dbServerPort,);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
echo "Connected successfully";
?>

2. MySQLi Procedural

<?php
$servername = "host";
$username = "username";
$password = "password";
$dbname   = "databasename";
$dbServerPort = "port";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname, $dbServerPort,);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>

3. PDO(PHP Data Objects)

<?php

$host = 'mysql-xxxxx-0.cloudclusters.net';
$db   = 'your database name';
$user = 'your database user name';
$pass = 'your password';
$port = "port";
$charset = 'utf8mb4';

$options = [
    \PDO::ATTR_ERRMODE            => \PDO::ERRMODE_EXCEPTION,
    \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
    \PDO::ATTR_EMULATE_PREPARES   => false,
];
$dsn = "mysql:host=$host;dbname=$db;charset=$charset;port=$port";
try {
     $pdo = new \PDO($dsn, $user, $pass, $options);
     echo "Connected successfully";
} catch (\PDOException $e) {
     echo "Connection failed: " . $e->getMessage();
}

?>