This guide is divided into:
1. Step-to-step MySQL Installation
2. Server and Database Configuration with Python
3. Client-side Python Example
Step-to-step MySQL Installation
-
Download the MySQL Installer. (You can go to the page if you want with this link)
-
Run the Installer.
-
Select MySQL Server and the appropriate MySQL Connector for your version and click Next.
-
Select "Standalone MySQL Server / Classic MySQL Replication" and click Next.
-
Select "Server Computer" as Config Type and click Next.
Note: There is not really a difference between Developer Computer and Server Computer for this tutorial
-
Select "Use Strong Password Encryption for Authentication (RECOMMENDED)" and click Next.
-
Decide on a password for the root account, and create a user with the role "DB Admin". Then, click Next.
Server And Database Configuration with Python
Note: The following can be done in MySQL Shell, but I decided to do so in Python as you may want to implement the following code in your projects.
Install the Connector
First, in the CommandPrompt enter pip install mysql-connector
to install the connector to be used in Python.
We can start creating our python file.
Python Step-by-Step
Let's start by importing the recently installed package.
import mysql.connector as db
Then, we create an instance of MySQLConnection
using the root account and the password created during the MySQL installation and initialize the cursor.
mydb = db.connect(
host = "localhost",
user = "root",
passwd = "password"
)
mycursor = mydb.cursor()
After this, we create a database and a user with privileges to use the database.
mycursor.execute("CREATE DATABASE IF NOT EXISTS my_database;")
# the username should be quoted as well as the user's password.
# replace clientipaddress with the client-computer's ip address
mycursor.execute("CREATE USER IF NOT EXISTS 'username'@clientipaddress IDENTIFIED BY 'password';")
mycursor.execute("GRANT ALL PRIVILEGES ON *.* to 'username'@clientipaddress;")
After this we can access the database to create a simple table.
mycursor.execute("use my_database;")
mycursor.execute("CREATE TABLE my_table (name varchar(20),age int(2));")
With this, we have finished the configuration of the database.
Python Full Code
import mysql.connector as db
mydb = db.connect(
host = "localhost",
user = "root", # the server's main account
passwd = "password" # and its password
)
mycursor = mydb.cursor()
mycursor.execute("CREATE DATABASE IF NOT EXISTS my_database;")
# the username and password should be quoted should be quoted with ' ' .
# replace clientipaddress with the client-computer's ip address
mycursor.execute("CREATE USER IF NOT EXISTS 'username'@clientipaddress IDENTIFIED BY 'password';")
mycursor.execute("GRANT ALL PRIVILEGES ON *.* to 'username'@clientipaddress;")
# select which database we will be using
mycursor.execute("use my_database;")
# create the table
mycursor.execute("CREATE TABLE my_table (name varchar(20),age int(2));")
Let's make a simple example of a client application.
Client-side Python Example
In this example we will connect to the remote database and insert a tuple into the 'my_table' table.
Python Step-by-Step
As before, we import the connector.
import mysql.connector as db
Then, we connect to the remote database and initialize our cursor.
mydb = db.connect(
host = serverpaddress, # replace for the server's IP address
user = "username", # previously created user
passwd = "password", # and its password
database = "my_database" # we directly connect to the desired database
)
mycursor = mydb.cursor()
After this, we define the command, assign values, and execute the command. We also commit out database.
sql_command = "INSERT INTO my_table (name, age) values (%s, %s);"
values = ('my_name', 1)
mycursor.execute(sql_command, values)
# save changes to the database
mydb.commit();
We can view the information inside the table using SELECT.
sql_command = "select * from my_table"
mycursor.execute(sql_command)
[print(x) for x in mycursor.fetchall()]
The output should be ('my_name', 1)
Example's Full Code
# import necessary packages
import mysql.connector as db
# connect to the database
mydb = db.connect(
host = serveripaddress, # replace for the server's IP address
user = "username", # previously created user
passwd = "password", # and its password
database = "my_database" # we directly connect to the desired database
)
# initialize cursor
mycursor = mydb.cursor()
# define command
sql_command = "INSERT INTO my_table (name, age) values (%s, %s);"
# assign values
values = ('my_name', 1)
# execute command
mycursor.execute(sql_command, values)
# save changes to the database
mydb.commit();
# import information from the database
sql_command = "select * from my_table"
mycursor.execute(sql_command)
# output information
[print(x) for x in mycursor.fetchall()]