The focus of this article is to teach you how to operate MySQL database with Python.

1. General steps

In fact, there is a general step here, which is written dead. Just follow it.

# 1. Import related libraries

import pymysql

# 2. Link MySQL server

db = pymysql.connect(host='localhost' , user='root' , password='******' , port=3306 ,db='spiders' , charset='utf8')

# 3. Create a cursor cursor object

cursor = db.cursor

# 4. Write your sql statement in this step

sql = 'select version'

# 5. Execute sql statement

cursor.execute(sql)

# 6. Disconnect

db.close

It can be seen that the whole process is the fourth step, which is different. In the final analysis, it is actually writing sql.

2. Knowledge points to be specified

I. description of parameters

db = pymysql.connect(host='localhost' , user='root' , password='******' , port=3306 ,db='spiders' , charset='utf8')

This line of code is very long and involves several parameters. Here we introduce them one by one as follows:

• Parameter 1: host IP of mysql server

• Parameter 2: user name;

• Parameter 3: password;

• Parameter 4: the port of the connected mysql host, which is 3306 by default;

• Parameter 5: connected database name;

• Parameter 6: the coding method used for communication is' gb2312 'by default. It is required to be consistent with the code specified during database creation, otherwise the Chinese will be garbled;

Ⅱ cursor cursor object

3. Python addition, deletion, modification and query

① Create a data table

import pymysql

db = pymysql.connect(host='192.168.3.47' , user='root',password='******' , port=3306 , db='spiders' , charset='utf8')

cursor = db.cursor

# Check whether the table exists. If so, delete it

cursor.execute('drop table if exists students')

# Create table

sql = 'create table students(id int auto_increment primary key not ,name varchar(10) not ,age int not )'

cursor.execute(sql)

db.close

Note: there are not many opportunities to create tables with code in the future. Tables are generally created in advance.

② Insert data

import pymysql

db = pymysql.connect(host='192.168.3.47' , user='root',password='******' , port=3306 , db='spiders' , charset='utf8')

cursor = db.cursor

# insert data

sql = 'insert into students(name,age) values(%s,%s)'

try:

cursor.execute(sql,('Sun WuKong',100000))

db.commit

except:print("Insert failed")

db.rollback

db.close

Note 1: try... except... Statement must be used to insert data, because in case of unsuccessful insertion, other codes cannot be executed.

Note 2: import pymysql. This module enables the transaction function of mysql by default. Therefore, DB must be used when "adding", "deleting" and "modifying" Commit commits the transaction, otherwise the inserted data will not be visible.

③ Update data

import pymysql

db = pymysql.connect(host='192.168.3.47' , user='root',password='******' , port=3306 , db='spiders' , charset='utf8')

cursor = db.cursor

# Update data

sql = 'update students set age =%s where name=%s'

try:

cursor.execute(sql,(30,"Wei Hua Guo"))

db.commit

except:

print("Insert failed")

db.rollback

db.close

③ Delete operation

import pymysql

db = pymysql.connect(host='192.168.3.47' , user='root',password='******' , port=3306 , db='spiders' , charset='utf8')

cursor = db.cursor

# Delete data

sql = 'delete from students where age=100000'

try:

cursor.execute(sql)

db.commit

except:

print("Insert failed")

db.rollback

db.close

④ Query operation

• fetchone function: get the next query result set, which is an object.

• fetchall function: receive all returned rows.

import pymysql

db = pymysql.connect(host='192.168.3.47' , user='root',password='******' , port=3306 , db='spiders' , charset='utf8')

cursor = db.cursor

# Query data

sql = 'select * from students where age>60'

try:

cursor.execute(sql)

reslist = cursor.fetchall

for row in reslist:

print("%d--%d" %(row[0],row[1],... row[n]))

except:

print("Insert failed")

db.rollback

db.close

4. Encapsulate a class

# Note: write the following classes in the studentsql file

import pymysql

class StudentsSql:

def __init__(self,host,user,port,dbname,charset):

self.host = host

self.user = user

self.port = port

self.dbname = dbname

self.charset = charset

def connet(sef):

self.db = pymysql.connect(self.host, self.user, self.port,

self.dbname, self.charset)

def close(self):

self.cursor.close

self.db.close

def get_one(self,sql):

res = None

try:

self.connect

self.cursor.execute(sql)

res = self.cursor.fetchone

self.close

except:

print(""Query failed")

return res

def get_all(self,sql):

res = None

try:

self.connect

self.cursor.execute(sql)

res = self.cursor.fetchall

self.close

except:

print(""Query failed")

return res

def inset(self,sql):

return self.__edit(sql)

def update(self,sql):

return self.__edit(sql)

def delete(self,sql):

return self.__edit(sql)

def __edit(self,sql):

count = 0

try:

self.connect

count = self.cursor.execute(sql)

self.db.commit

self.close

except:

print("Transaction commit failed ")

self.db.rollback

return count

 

The above class is encapsulated successfully. In the future, you only need to call it.

from studentsql import StudentsSql

s = StudentsSql("host='192.168.3.47' , user='root', password='******' , port=3306 , db='spiders' , charset='utf8'")

res = s.get_all('select * from students where age>60')

for row in res:

print("%d--%d" %(row[0],row[1],... row[n]))

Finally, I wish you progress every day!! The most important thing to learn Python is mentality. We are bound to encounter many problems in the process of learning. We may not be able to solve them if we want to break our head. This is normal. Don't rush to deny yourself and doubt yourself. If you have difficulties in learning at the beginning and want to find a python learning and communication environment, you can join us, receive learning materials and discuss together.