Simple Remote MySQL Server Configuration from Scratch with Python
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 Custom as Setup Type and click Next.
Select MySQL Server and the appropriate MySQL Connector for your version and click Next.
Click Execute and wait for the installation.
Click Next x2
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.
Click on Next.
Click on Execute and wait for the configuration.
Click on Finish
Click on Next
Click on Finish
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()]
Author And Source
この問題について(Simple Remote MySQL Server Configuration from Scratch with Python), 我々は、より多くの情報をここで見つけました https://qiita.com/ANNEX_IBS/items/f37ae8caf65ed2bc7539著者帰属：元の著者の情報は、元のURLに含まれています。著作権は原作者に属する。
Content is automatically searched and collected through network algorithms . If there is a violation . Please contact us . We will adjust (correct author information ,or delete content ) as soon as possible .