[Introduction for beginners] Working with MySQL in Python

1.First of all

This time, I will describe how to handle MySQL in Python.

Execution environment

OS

• macOS Catalina 10.15.2

Make MySQL available on mac

Reference: [How to connect to MySQL with Python [For beginners]](https://techacademy.jp/magazine/18691?yclid=YJAD.1577671005.4f085UOdcq3NVi6RP_MIypQCYJKTMckcF2tzywk8ZKmffyar_F4f7o0PMgzhcu2BWSzBo

(Premise: `Python``` can be used, pip``` command can be used, `` brew``` command can be used)

1. In the terminal, type pip install mysqlclient to install MySQL.
2. In the terminal, type brew install mysql-connector-c to make MySQL accessible.

2. Source code

2.1. Connect and disconnect

#----------------
#Standard library
#----------------
#import pymysql.cursors #For Raspberry Pi, Win
import MySQLdb          #for mac

#When using the upper one on mac
# pip3 install pymysql
#Must be installed with


#---------
#Connect
#---------
#cnct = pymysql.connect( #For Raspberry Pi
cnct = MySQLdb.connect(  #Win,for mac
    host = "localhost",  #hostname
    user = "root",       #MySQL username
    password = "",       #MySQL user password
    db = "test",         #Database name
    charset = "utf8"     #Character code
    )
TABLE = "test"           #table name

cur = cnct.cursor()


#---------
#Operate the database here
#---------


#---------
#Disconnect
#---------

cur.close()
cnct.close()

2.2. Data acquisition and display

cur.execute("SELECT * FROM " + TABLE + ";") #SQL commands
results = cur.fetchall()                    #Store the result in result
print("Show all")
print(results)
print("\n")

print("Display line by line")
for r in results:
    print(r) #Since r is an array, if you want to display it in element units, you can specify the index. Example: print(r[0])

If you want to display whether there is data in the if statement

cur.execute("SELECT * FROM " + TABLE + ";")
results = cur.fetchall()
if results: #Data exists in the array results
    print("There is data")
    print(results)
else:       #No data in array results (array is empty)
    print("No data")

2.3 Addition of data

#Add data
cur.execute("INSERT INTO " + TABLE + " " + ROW + " VALUES (%s,%s);", ("test1","test2"))
cur.execute("INSERT INTO " + TABLE + " " + ROW + " VALUES (%s,%s);", ("test3","test4"))
cnct.commit()   #Save the changes you made to the database. Without this, it will not be reflected in MySQL.

2.4. Delete data

cur.execute("DELETE FROM " + TABLE + ";") #Delete all data. To specify deleted data, specify it in the WHERE clause.
cnct.commit()   #Save the changes you made to the database. Without this, it will not be reflected in MySQL.

2.5. All

python

#----------------
#Standard library
#----------------

#import pymysql.cursors #For Raspberry Pi
import MySQLdb


#----------------
#Database connection
#----------------

#cnct = pymysql.connect( #For Raspberry Pi
cnct = MySQLdb.connect(
    host = "localhost",
    user = "root",
    password = "",
    db = "test",
    charset = "utf8"
    )
TABLE = "test"
ROW = "(data1,data2)" #To specify the column of data to add

cur = cnct.cursor()


#----------------
#Database operation
#----------------

#Add data
cur.execute("INSERT INTO " + TABLE + " " + ROW + " VALUES (%s,%s);", ("test1","test2"))
cur.execute("INSERT INTO " + TABLE + " " + ROW + " VALUES (%s,%s);", ("test3","test4"))
cnct.commit()   #Save changes made to the database

#Data acquisition / display
cur.execute("SELECT * FROM " + TABLE + ";")
results = cur.fetchall()
print("Show all")
print(results)
print("\n")

print("Display line by line")
for r in results:
    print(r) #Since r is an array, if you want to display it in element units, you can specify the index. Example: print(r[0])

"""
#Delete data
cur.execute("DELETE FROM " + TABLE + ";") #Delete all data. To specify deleted data, specify it in the WHERE clause.
cnct.commit()   #Save changes made to the database


print("\n") #Line breaks on two lines. Print to break only one line()Designated as


#Data acquisition / display
cur.execute("SELECT * FROM " + TABLE + ";")
results = cur.fetchall()
if results:
    print("There is data")
    print(results)
else:
    print("No data")
"""

#----------------
#Database disconnection
#----------------

cur.close()
cnct.close()



"""MySQL operations in the terminal

Start MySQL
$ mysql.server start

MySQL connection
$ mysql -u root

Exit MySQL
$ mysql.server stop


"""