Python2 Use... In mysqldb
install PyMySQL
=============================================================================
First install this module ：
pip install pymysql
Check if the installation is successful ：
pip show pymysql
PyMySQL Connect to database
================================================================================
import pymysql
# Create database connection
conn = pymysql.connect(
host = '127.0.0.1', # Connecting hosts , Default 127.0.0.1
user = 'root', # user name
passwd = '1234',# password
port = 3306, # port , The default is 3306
db = 'test', # Database name
charset = 'utf8' # Character encoding
)
# Generate cursor object cursor
cursor = conn.cursor()
# Query database version
cursor.execute("select version()") # The return value is the number of data queried
# adopt fetchall Methods to get data
data = cursor.fetchone()
print("Database Version:%s" % data)
cursor.close() # Close cursor
conn.close() # Close the connection
Create database （ Database must exist ）
=================================================================================
Because you need to fill in the name of the database to be connected when connecting to the database , So creating a database can only ： Delete existing database , Recreate , amount to clear database The role of .
# Create database test
cursor.execute("drop database if exists test") # If the database already exists , Then delete and recreate
sql = "create database test"
cursor.execute(sql)
Create data table
========================================================================
# Create data table
cursor.execute("drop table if exists employee") # If the data table already exists , Then delete and recreate
sql = """
CREATE TABLE employee (
id VARCHAR(20) NOT NULL,
name VARCHAR(20),
age INT,
income FLOAT
)
"""
cursor.execute(sql)
The insert
=======================================================================
# Database inserts data
sql = "INSERT INTO employee VALUES ('1', ' Zhang San ', 20, 5000)"
try:
cursor.execute(sql)
# Submit all operations of the current cursor
conn.commit()
except:
print("expection!")
conn.rollback()
# View the updated results
sql = "SELECT* FROM employee"
cursor.execute(sql)
data = cursor.fetchall()
print(data)
Query operation
=======================================================================
Only one piece of data is queried ：cursor.fetchone()
sql = "SELECT* FROM employee"
cursor.execute(sql) # The return value is the number of data queried
data = cursor.fetchone() # Query a piece of data
print(data)
Query all data in the database ：cursor.fetchall()
# Database query data
sql = "SELECT * FROM employee"
cursor.execute(sql) # The return value is the number of data queried
data = cursor.fetchall() # Query a piece of data
print(data)
Query the data of the specified condition ： The query meets the conditions income > 5000 The data of .
# Query the data of the specified condition
sql = " SELECT * FROM employee WHERE income > '%d' " % (5000)
cursor.execute(sql) # The return value is the number of data queried
data = cursor.fetchone()
print(data)
update operation
=======================================================================
# Update the database
sql = " UPDATE employee SET age = age + 1 WHERE age < '%d' " % (25)
try:
cursor.execute(sql)
conn.commit()
except:
print("expection!")
conn.rollback()
# View the updated results
sql = "SELECT* FROM employee"
cursor.execute(sql)
data = cursor.fetchall()
print(data)
Delete operation
=======================================================================
# Delete data
sql = "DELETE FROM employee WHERE age > '%d' " % (30)
try:
cursor.execute(sql)
conn.commit()
except:
print("exce[tion!")
conn.rollback()
# View the updated results
sql = "select * from employee"
cursor.execute(sql)
data = cursor.fetchone()
print(data)
Comprehensive case
=======================================================================
Make sure there are... In the database test surface ：
import pymysql
import traceback
from time import sleep
class PyMySQL(object):
create_table = """
CREATE TABLE stu (
id INT not null PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
age INT,
sex VARCHAR(255)
) DEFAULT CHARSET = utf8
"""
select = 'SELECT * FROM stu'
# Last
Dear readers , Because of the length of this article , To avoid affecting the reading experience , I will summarize the arrangement below , If necessary, please **[ Click here to download the article materials for free after you like it ！](https://gitee.com/vip204888/java-p7)**
age INT,
sex VARCHAR(255)
) DEFAULT CHARSET = utf8
"""
select = 'SELECT * FROM stu'
# Last
Dear readers , Because of the length of this article , To avoid affecting the reading experience , I will summarize the arrangement below , If necessary, please **[ Click here to download the article materials for free after you like it ！](https://gitee.com/vip204888/java-p7)**
[ Outside the chain picture transfer in ...(img-6VSfjPca-1628508989816)]
[ Outside the chain picture transfer in ...(img-ZBhPLh04-1628508989819)]
[ Outside the chain picture transfer in ...(img-GvQB3OiA-1628508989821)]
