[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

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


"""

Recommended Posts

[Introduction for beginners] Working with MySQL in Python
INSERT into MySQL with Python [For beginners]
Working with LibreOffice in Python
Working with sounds in Python
Specific sample code for working with SQLite3 in Python
Working with LibreOffice in Python: import
Run unittests in Python (for beginners)
Working with DICOM images in Python
[For beginners] Summary of standard input in Python (with explanation)
Try working with binary data in Python
Tips for dealing with binaries in Python
Post Test 3 (Working with PosgreSQL in Python)
Process multiple lists with for in Python
Debug for mysql connection with python mysql.connector
[Python] Read images with OpenCV (for beginners)
WebApi creation with Python (CRUD creation) For beginners
[For beginners] Try web scraping with Python
Causal reasoning and causal search with Python (for beginners)
Read a Python # .txt file for a super beginner in Python with a working .py
Write Python in MySQL
~ Tips for Python beginners from Pythonista with love ① ~
Try to calculate RPN in Python (for beginners)
Try working with Mongo in Python on Mac
Introduction to Programming (Python) TA Tendency for beginners
Python Beginner's Guide (Introduction)
~ Tips for Python beginners from Pythonista with love ② ~
OpenCV for Python beginners
[For beginners] Introduction to vectorization in machine learning
Introduction to Graph Database Neo4j in Python for Beginners (for Mac OS X)
Basic story of inheritance in Python (for beginners)
Settings for getting started with MongoDB in python
Notes for Python beginners with experience in other languages 12 (+1) items by function
Rock-paper-scissors poi in Python for beginners (answers and explanations)
[For beginners] How to use say command in python!
VS Code settings for developing in Python with completion
[Introduction for beginners] Reading and writing Python CSV files
[For beginners] Learn basic Python grammar for free in 5 hours!
Learning flow for Python beginners
Scraping with chromedriver in python
Search for strings in Python
Scraping with Selenium in Python
Techniques for sorting in Python
Scraping with Tor in Python
Python3 environment construction (for beginners)
Design Patterns in Python: Introduction
Python #function 2 for super beginners
Combined with permutations in Python
100 Pandas knocks for Python beginners
Python for super beginners Python #functions 1
Python #list for super beginners
~ Tips for beginners to Python ③ ~
Introduction to Python For, While
About "for _ in range ():" in python
Connection pooling with Python + MySQL
Tips for developing apps with Azure Cosmos DB in Python
Create a child account for connect with Stripe in Python
■ Kaggle Practice for Beginners --Introduction of Python --by Google Colaboratory
(For myself) Flask_8 (Add / Edit / Delete in database with python)
[Explanation for beginners] Introduction to convolution processing (explained in TensorFlow)
[Explanation for beginners] Introduction to pooling processing (explained in TensorFlow)
Introduction to Python for VBA users-Calling Python from Excel with xlwings-