mysql-connector-python is officially provided by MySQL
PyMySQL supports python2 and python3
MySQLdb only supports python2
**This course requires: install MySQL; change the account password in the case to your own (the account password in the case is root); **
Install PyMySQL module
Command line: pip install pymysql
Import PyMySQL module
import pymysql
Get connected object
conn=pymysql.connect(host=' ',user=' ',password=' ',database=' ',charset='utf-8')
Get the cursor object to CRUD the data through the cursor c
Commit transaction
conn.commit()
Close cursor
c.close()
Close connection
conn.close()
Create database
''' Create database'''import pymysql
# Open the database connection, no need to specify the database, because the database needs to be created
conn = pymysql.connect('localhost',user ="root",passwd ="root")
# Get cursor
cursor=conn.cursor()
# Create pythonBD database
cursor.execute('CREATE DATABASE IF NOT EXISTS pythonDB DEFAULT CHARSET utf8 COLLATE utf8_general_ci;')
cursor.close()#Close the cursor first
conn.close()#Close the database connection
print('Create pythonBD database successfully')
Create table
import pymysql
# Open database connection
conn = pymysql.connect('localhost',user ="root",passwd ="root",db ="myemployees")
# Get cursor
cursor=conn.cursor()print(cursor)
# Create user table
cursor.execute('drop table if exists user')
sql="""CREATE TABLE IF NOT EXISTS `user`(`id`int(11) NOT NULL AUTO_INCREMENT,`name`varchar(255) NOT NULL,`age`int(11) NOT NULL,
PRIMARY KEY(`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=0"""
cursor.execute(sql)
cursor.close()#Close the cursor first
conn.close()#Close the database connection
print('Create data table successfully')
Insert a single piece of data in the table
''' Insert a single data'''import pymysql
# Open the database connection without specifying the database
conn=pymysql.connect('localhost','root','root')
conn.select_db('pythondb')
cur=conn.cursor()#Get cursor
# Create user table
cur.execute('drop table if exists user')
sql="""CREATE TABLE IF NOT EXISTS `user`(`id`int(11) NOT NULL AUTO_INCREMENT,`name`varchar(255) NOT NULL,`age`int(11) NOT NULL,
PRIMARY KEY(`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=0"""
cur.execute(sql)
insert=cur.execute("insert into user values(1,'tom',18)")print('The number of rows affected by the add statement:',insert)
# Another way to insert data, pass in the value through a string
sql="insert into user values(%s,%s,%s)"
cur.execute(sql,(3,'kongsh',20))
cur.close()
conn.commit()
conn.close()print('sql executed successfully')
Query data
''' fetchone'''import pymysql
# Open database connection
conn=pymysql.connect('localhost','root','root')
conn.select_db('myemployees')
# Get cursor
cur=conn.cursor()
cur.execute("select * from user;")while1:
res=cur.fetchone()if res is None:
# Indicates that the result set has been taken
breakprint(res)
cur.close()
conn.commit()
conn.close()print('sql executed successfully')'''
(1,' tom',18)(3,'kongsh',20)
sql executed successfully
'''
CRUD refers to the abbreviation of the initials of the words Add (Create), Read Query (Retrieve), Update (Update) and Delete (Delete) when doing calculation processing.
Operation single: c.execute('SQL statement',(parameter value))
Multiple operations: c.executemany('SQL statement', (multiple parameter values))
Take a single: c.fetchone()
Take multiple: fetchmany(size)
Take all: c.fetchall()
SQLite is an in-process library that implements a self-sufficient, serverless, zero-configuration, transactional SQL database engine. It is a zero-configuration database, which means that unlike other databases, you do not need to configure it in the system.
Just like other databases, the SQLite engine is not an independent process, and can be statically or dynamically connected according to application requirements. SQLite directly accesses its storage files.
The SQLite data type is an attribute used to specify the data type of any object. Every column, every variable and expression in SQLite has an associated data type.
You can use these data types while creating the table. SQLite uses a more general dynamic type system. In SQLite, the data type of a value is related to the value itself, not to its container.
The main data types of SQLite are: NULL, INTEGER, REAL, TEXT, BLOB
Import module
import sqlite3
Create/open database
When calling the connect function, specify the library name, if the specified database exists, open the database directly, if it does not exist, create a new one and open it again.
cx = sqlite3.connect("E:/test.db") #Create a file here
You can also create a database in memory.
con = sqlite3.connect(":memory:")
Database connection object
The object cx returned when opening the database is a database connection object, which can have the following operations:
Use cursor to query database
c = conn.cursor() #We need to use the cursor object SQL statement to query the database to obtain the query object.
The cursor object has the following operations:
Create table
c.execute('''CREATE TABLE stocks(date text,trans text,symbol text,gty real,price real)''')
Insert a piece of data into the table
c.execute('''insert into stocks values('2016-01-05','BUY','RHAT',100,35.14)''')
Connect to the database
import sqlite3
# data storage name
db_name ="test.db"
# Table Name
table_name ="catalog"
conn = sqlite3.connect(db_name)
Open cursor
rs=conn.cursor()
Build a table
sql ='create table '+ table_name +' (id varchar(20) primary key, pid integer, name varchar(10))'try:
rs.execute(sql)print("Table built successfully")
except:print("Failed to build table")
Additions, deletions, modifications
# Increase: add three records
sql ="Insert into "+ table_name +" values ('001', 1, 'Zhang San')"try:
rs.execute(sql)
# Commit transaction
conn.commit()print("Inserted successfully")
except:print("Insertion failed")
sql ="Insert into "+ table_name +" values ('002', 2, 'Li Si')"try:
rs.execute(sql)
# Commit transaction
conn.commit()print("Inserted successfully")
except:print("Insertion failed")
sql ="Insert into "+ table_name +" values ('003', 3, 'Wang Wu')"try:
rs.execute(sql)
# Commit transaction
conn.commit()print("Inserted successfully")
except:print("Insertion failed")
# Delete: delete the record with pid equal to 3
sql ="Delete from "+ table_name +" where pid = 3"try:
rs.execute(sql)
conn.commit()print("successfully deleted")
except:print("failed to delete")
# Change: Change the pid of the record with pid equal to 2 to 1
sql ="Update "+ table_name +" set pid = 1 where pid = 2"try:
rs.execute(sql)
conn.commit()print("Successfully modified")
except:print("fail to edit")
# Check: Query all table names in the database
sql ="Select name From sqlite_master where type = 'table'"
res = rs.execute(sql)print(res.fetchall())
# Query all records in the table
sql ="Select * from "+ table_name
try:
res = rs.execute(sql)print(res.fetchall())
except:print([])