Shulou(Shulou.com)06/02 Report--

Pymysql Introduction

PyMySQL is a library used to connect to MySQL servers in Python 3.x, while mysqldb is used in Python2.

PyMySQL follows the Python Database API v2.0 specification and includes the pure-Python MySQL client library.

Common steps:

1. introduction module

2. Get a connection to the database

3. Execute SQL statements and stored procedures

4. close the database connection

PyMySQL installation

1. Open cmd command

cd C:\Users\Administrator\AppData\Local\Programs\Python\Python37\Scripts #Switch directories

pip install pymysql

database connection

import pymysql #module import

#Open Database Connection

db = pymysql.connect(

host='database ip',

user='username,

passwd='Password',

db='database name',

port=3306,

charset='utf8'

)

#Create a cursor object using the cursor() method

cursor = db.cursor()

#Execute SQL queries using the execute() method

cursor.execute("SELECT VERSION()")

#Use fetchone() method to get a single piece of data.

data = cursor.fetchone()

print ("Database version : %s " % data)

#Close database connection

db.close()

Script output:

Database version : 5.6.24-log

create a database table

import pymysql

#Open Database Connection

db = pymysql.connect(

host='database ip',

user='username,

passwd='Password',

db='database name',

port=3306,

charset='utf8'

)

#Create a cursor object using the cursor() method

cursor = db.cursor()

#Execute SQL using execute() method, delete table if it exists

cursor.execute("DROP TABLE IF EXISTS info")

#Create tables using preprocessed statements

sql = """create table info (id int not null,name char(6),score decimal(5,2),age int(4));"""

cursor.execute(sql)

#Close database connection

db.close()

script verification

database insert operation

import pymysql

#Open Database Connection

db = pymysql.connect(

host='database ip',

user='username,

passwd='Password',

db='database name',

port=3306,

charset='utf8'

)

#Get action cursor using cursor() method

cursor = db.cursor()

#SQL insert statement

sql_1= """insert into info (id,name,score,age)values(1,'Zhang San',88,33);"""

sql_2 = """insert into info (id,name,score,age)values(2,'Li Si',78,23);"""

sql_3 = """insert into info (id,name,score,age)values(3,'Wang Wu',58,43);"""

try:

#execute sql statement

cursor.execute(sql_1)

cursor.execute(sql_2)

cursor.execute(sql_3)

#Submit to Database Execution

db.commit()

except:

#Roll back if an error occurs

db.rollback()

#Close database connection

db.close()

insert data validation

database query operation

Python queries Mysql use fetchone() to get a single piece of data and fetchall() to get multiple pieces of data.

fetchone(): This method fetches the next query result set. The result set is an object

fetchall(): receives all return result lines.

rowcount: This is a read-only property and returns the number of rows affected by executing ().

Script file:

import pymysql

#Open Database Connection

db = pymysql.connect(

host='database ip',

user='username,

passwd='Password',

db='database name',

port=3306,

charset='utf8'

)

#Get action cursor using cursor() method

cursor = db.cursor()

#SQL query statements

sql = 'select * from info;'

#Execute SQL statements

cursor.execute(sql)

#Get a list of all records

res = cur.fetchall()

#Print results

print(res)

#Print out each tuple

print(res[0])

print(res[1])

print(res[2])

|#Close Cursor

cur.close()

Close the database

db.close()

query verification

database update operations

import pymysql

#Open Database Connection

db = pymysql.connect(

host='database ip',

user='username,

passwd='Password',

db='database name',

port=3306,

charset='utf8'

)

#Get action cursor using cursor() method

cursor = db.cursor()

#SQL query statements

sql = 'UPDATE info SET AGE = AGE + 1 WHERE id=3'

#Execute SQL statements

cursor.execute(sql)

try:

#Execute SQL statements

cursor.execute(sql)

#Submit to Database Execution

db.commit()

except:

#Roll back when an error occurs

db.rollback()

#Close database connection

db.close()

update verification

delete operation

import pymysql

#Open Database Connection

db = pymysql.connect(

host='database ip',

user='username,

passwd='Password',

db='database name',

port=3306,

charset='utf8'

)

#Get action cursor using cursor() method

cursor = db.cursor()

#SQL query statements

sql = 'DELETE FROM info WHERE id=3;'

#Execute SQL statements

cursor.execute(sql)

try:

#Execute SQL statements

cursor.execute(sql)

#Submit to Database Execution

db.commit()

except:

#Roll back when an error occurs

db.rollback()

#Close database connection

db.close()

Delete validation

perform transactions

Transaction mechanisms ensure data consistency.

Transactions should have four attributes: atomicity, consistency, isolation, and persistence. These four attributes are commonly referred to as ACID attributes.

atomicity (atomicity). A transaction is an indivisible unit of work in which all operations are either done or not done.

consistency (consistency). A transaction must be one that changes the database from one consistent state to another. Consistency and atomicity are closely related.

isolation (isolation). The execution of one transaction cannot be interfered with by other transactions. That is, the internal operations and data used in a transaction are isolated from other concurrent transactions, and the transactions executed concurrently cannot interfere with each other.

Durability (durability). Persistence, also known as permanence, means that once a transaction is committed, its changes to the data in the database should be permanent. Subsequent actions or failures should not have any effect on it.

Python DB API 2.0 transactions provide two methods commit or rollback.

error handling

Summary:

1. This article refers to the rookie tutorial and personal operation after writing out.

Python2 and pyhton3 modules are different, here is mainly introduced pymysql.

3. Here is only the introduction of the most basic knowledge, starting point is to make a note for themselves, convenient for future reference!

4. Add, delete, update data to commit transactions, otherwise the database does not perform the operation.

5. The method of operating multiple sql statements at the same time can refer to database insert operation.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.