In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-03-18 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Servers >
Share
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.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.