Introducing PyMySQL to raspberry pi3

Operate MySQL from python with PyMySQL.

PyMySQL installation

--Execute the following command.

$ sudo pip install PyMySQL

How to use

--Assuming the following table

+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| email    | varchar(255) | NO   |     | NULL    |                |
| password | varchar(255) | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

--Create statement in the above table

CREATE TABLE `users` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `email` varchar(255) COLLATE utf8_bin NOT NULL,
    `password` varchar(255) COLLATE utf8_bin NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
AUTO_INCREMENT=1 ;

--Connect to DB and disconnect

sample.py


import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd',
                             db='db',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

-Description of the following INSERT statement-

connection.close()

--INSERT statement

with connection.cursor() as cursor:
    # Create a new record
    sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
    cursor.execute(sql, ('[email protected]', 'very-secret'))

# connection is not autocommit by default. So you must commit to save
# your changes.
connection.commit()

--SELECT statement

with connection.cursor() as cursor:
    # Read a single record
    sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
    cursor.execute(sql, ('[email protected]',))
    result = cursor.fetchone()
    print(result)

reference

github readme

Recommended Posts

Introducing PyMySQL to raspberry pi3
Introduced python3-OpenCV3 to Raspberry Pi
I talked to Raspberry Pi
Port FreeRTOS to Raspberry Pi 4B
Output from Raspberry Pi to Line
[Raspberry Pi] Changed Python default to Python3
Connect two USB cameras to Raspberry Pi 4
Raspberry Pi backup
How to install NumPy on Raspberry Pi
How to use Raspberry Pi pie camera Python
Connect your Raspberry Pi to your smartphone using Blynk
Connect to MySQL with Python on Raspberry Pi
What is Raspberry Pi?
GPGPU with Raspberry Pi
pigpio on Raspberry pi
Raspberry Pi video camera
Raspberry Pi Bad Knowledge
Let's do Raspberry Pi?
Introducing Python 2.7 to CentOS 6.6
DigitalSignage with Raspberry Pi
Raspberry Pi 4 setup memo
Cython on Raspberry Pi
Raspberry Pi system monitoring
From setting up Raspberry Pi to installing Python environment
Easy IoT to start with Raspberry Pi and MESH
Try to visualize the room with Raspberry Pi, part 1
How to use the Raspberry Pi relay module Python
Output to "7-segment LED" using python on Raspberry Pi 3!
Easy introduction to home hack with Raspberry Pi and discord.py
Raspberry Pi 4B initial setting
Indoor monitoring using Raspberry Pi
Mutter plants with Raspberry Pi
Update Python for Raspberry Pi to 3.7 or later with pyenv
Install Raspberry Pi OS (Raspbian)
Raspberry Pi + Python + OpenGL memo
I want to disable interrupts on Raspberry Pi (≒ DI / EI)
I tried to automate [a certain task] using Raspberry Pi
Raspbian initial settings (Raspberry Pi 4)
raspberry pi 1 model b, python
Introduced pyenv on Raspberry Pi
Change the message displayed when logging in to Raspberry Pi
How to get temperature from switchBot thermo-hygrometer using raspberry Pi
Use NeoPixel on Raspberry Pi
Install OpenCV4 on Raspberry Pi 3
Install TensorFlow 1.15.0 on Raspberry Pi
[Raspberry Pi] Minimum device driver creation memo to output GPIO
Translate I2C device driver for Arduino to Python for Raspberry pi
Connect Raspberry Pi to Alibaba Cloud IoT Platform with Python
I sent the data of Raspberry Pi to GCP (free)
Log in to Raspberry PI with ssh without password (key authentication)
I tried to automate the watering of the planter with Raspberry Pi
I made a web server with Raspberry Pi to watch anime
Use python on Raspberry Pi 3 to illuminate the LED (Hello World)
A memo when connecting bluetooth from a smartphone / PC to Raspberry Pi 4
Raspberry Pi --1 --First time (Connect a temperature sensor to display the temperature)
How to play music (wav / mp3) files on Raspberry Pi python
Run Polyglot on Raspberry Pi to perform morphological analysis in English
[Raspberry Pi] Stepping motor control with Raspberry Pi
Testing uart communication on Raspberry Pi
Use vl53l0x with Raspberry Pi (python)
raspberry pi 1 model b, node-red part 17