Python 3 Quick Start (11) - Python 3 Database Operation
mysql-connector driver
1. Introduction to mysql-connector
mysql-connector is the official drive provided by MySQL.
Using pipenv for package management, the installation commands are as follows:
pipenv install mysql-connector
2. Common interfaces of mysql-connector
mysql.connector.connect(*args, **kwargs)
Create a database connect ion and return the MySQLConnectionAbstract instance. Conne receives variable parameters and keyword variable parameters, usually specifies default parameters with variable parameters when receiving variable parameters, and uses dictionary type parameters when receiving keyword variable parameters. Common keywords are as follows:
'host': '127.0.0.1',
'user': 'root',
'password': 'root',
'port': 3306,
'database': 'test',
'charset': 'utf8'
import mysql.connector
MYSQL_CONFIG = {
"host":"47.110.45.34",
"port":"3306",
"user":"root",
"passwd":"xxxxxx"
}
if __name__ == '__main__':
mysql_db = mysql.connector.connect(**MYSQL_CONFIG)
print(mysql_db)The commonly used interfaces for MySQL ConnectionAbstract are as follows
mysql.connector.close()
Disconnect the connection to MySQL server
mysql.connector.disconnect()
Disconnect the connection to MySQL server
mysql.connector.reconnect(attempts=1, delay=0)
Re-establish the connection to MySQL server
mysql.connector.ping(reconnect=False, attempts=1, delay=0)
Check the availability of MySQL servers
mysql.connector.commit()
Submit the current transaction
mysql.connector.rollback()
Roll back the current transaction
mysql.connector.start_transaction(consistent_snapshot=False,
isolation_level=None, readonly=None)
Start a transaction
mysql.connector.cursor(buffered=None,raw=None,prepared=None, cursor_class=None,dictionary=None, named_tuple=None)
Create a cursor
The commonly used interfaces for MySQLCursorAbstract are as follows:
MySQLCursorAbstract.close()
Close the cursor
MySQLCursorAbstract.execute(operation,params=(),multi=False)
Perform SQL operations
MySQLCursorAbstract.executemany(operation, seqparams)
Perform SQL operations many times
MySQLCursorAbstract.fetchone()
Returns the next row of the query result set and a tuple or None.
MySQLCursorAbstract.fetchmany(size=1)
Returns the subsequent size rows of the query result set and a tuple linked list or empty linked list.
MySQLCursorAbstract.fetchall()
Returns all rows of the query result set and a linked list of tuple s
MySQLCursorAbstract.reset(self, free=True):
Reset the cursor to the default state
3. mysql-connector database programming
# -*- coding:utf-8 -*-
import mysql.connector
if __name__ == '__main__':
mysql_db = mysql.connector.connect(
host="47.110.45.34",
port="3306",
user="root",
passwd="YangYuLiang@123.com")
mysql_cursor = mysql_db.cursor()
try:
# Create a database
mysql_cursor.execute("CREATE DATABASE sites;")
# Using databases
mysql_cursor.execute("USE sites;")
# Create database tables
mysql_cursor.execute("CREATE TABLE website (name VARCHAR(255), url VARCHAR(255));")
# Create primary keys
mysql_cursor.execute("ALTER TABLE website ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY;")
# insert data
sql = "INSERT INTO website (name, url) VALUES (%s, %s);"
value = ("BaiDu", "https://www.baidu.com")
mysql_cursor.execute(sql, value)
mysql_db.commit()
except:
mysql_db.rollback()
# Batch insertion of data
sql = "INSERT INTO website (name, url) VALUES (%s, %s);"
value = [
('Google', 'https://www.google.com'),
('Github', 'https://www.github.com'),
('TaoBao', 'https://www.taobao.com'),
('HuaWei', 'https://www.huawei.com')
]
try:
mysql_cursor.executemany(sql, value)
# Update records
sql = "UPDATE website SET name = 'GitHub' WHERE name = 'Github'"
mysql_cursor.execute(sql)
mysql_db.commit()
except:
mysql_db.rollback()
# Query Record
try:
mysql_cursor.execute("SELECT * FROM website;")
result = mysql_cursor.fetchall() # fetchall() gets all records
for x in result:
print(x)
# Delete records
mysql_cursor.execute("DELETE FROM website WHERE name='Google';")
# Delete the database
mysql_cursor.execute("DROP DATABASE sites;")
mysql_db.commit()
except:
# RollBACK
mysql_db.rollback()
# Close database connection
mysql_db.close()PyMySQL Driver
1. Introduction to PyMySQL
PyMySQL is a library used to connect MySQL servers in Python 3.x, and mysqldb is used in Python 2.
PyMySQL follows the Python database API v2.0 specification and includes the pure-Python MySQL client library.
Using pipenv for package management, the installation commands are as follows:
pipenv install pymysql
2. PyMySQL Common Interfaces
pymysql.Connect(*args, **kwargs)
Connect to the MySQL database, establish a socket connection and return the Connection object. The keyword parameters received by Connection are as follows:
Connection(host=None, user=None, password="",
database=None, port=0, unix_socket=None,
charset='', sql_mode=None,
read_default_file=None, conv=None, use_unicode=None,
client_flag=0, cursorclass=Cursor, init_command=None,
connect_timeout=10, ssl=None, read_default_group=None,
compress=None, named_pipe=None,
autocommit=False, db=None, passwd=None, local_infile=False,
max_allowed_packet=1610241024, defer_connect=False,
auth_plugin_map=None, read_timeout=None, write_timeout=None,
bind_address=None, binary_prefix=False, program_name=None,
server_public_key=None)
Common interfaces for Connection are as follows:
Connection.close()
Send an exit message and close the socket connection to the MySQL database
Connection.begin()
Open a transaction
Connection.commit()
Commit changes to stable storage
Connection.rollback()
Roll back the current transaction
Connection.select_db(self, db):
Set the current database with db as the database name
Connection.cursor(self, cursor=None)
Create a cursor for SQL operations
Cursor commonly used interfaces are as follows:
Cursor.close()
Close the cursor
Cursor.execute(query, args=None)
Perform an SQL query operation to return the number of rows affected
Cursor.executemany(query, args)
Batch execution of SQL query operations to return the number of affected rows
Cursor.fetchone()
The next line of Fetch
Cursor.fetchmany(size=None)
Fetch multiline
Cursor.fetchall()
All rows of Fetch
3. PyMySQL Database Programming
import pymysql
if __name__ == '__main__':
mysql_db = pymysql.Connect(
host="47.110.45.34",
port=3306,
user='root',
password='YangYuLiang@123.com')
mysql_cursor = mysql_db.cursor()
try:
# Create a database
mysql_cursor.execute("CREATE DATABASE sites;")
# Using databases
mysql_cursor.execute("USE sites;")
# Create database tables
mysql_cursor.execute("CREATE TABLE website (name VARCHAR(255), url VARCHAR(255));")
# Create primary keys
mysql_cursor.execute("ALTER TABLE website ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY;")
# insert data
sql = "INSERT INTO website (name, url) VALUES (%s, %s);"
value = ("BaiDu", "https://www.baidu.com")
mysql_cursor.execute(sql, value)
mysql_db.commit()
except:
mysql_db.rollback()
# Batch insertion of data
sql = "INSERT INTO website (name, url) VALUES (%s, %s);"
value = [
('Google', 'https://www.google.com'),
('Github', 'https://www.github.com'),
('TaoBao', 'https://www.taobao.com'),
('HuaWei', 'https://www.huawei.com')
]
try:
mysql_cursor.executemany(sql, value)
# Update records
sql = "UPDATE website SET name = 'GitHub' WHERE name = 'Github'"
mysql_cursor.execute(sql)
mysql_db.commit()
except:
mysql_db.rollback()
# Query Record
try:
mysql_cursor.execute("SELECT * FROM website;")
result = mysql_cursor.fetchall() # fetchall() gets all records
for x in result:
print(x)
# Delete records
mysql_cursor.execute("DELETE FROM website WHERE name='Google';")
# Delete the database
mysql_cursor.execute("DROP DATABASE sites;")
mysql_db.commit()
except:
# RollBACK
mysql_db.rollback()
# Close database connection
mysql_db.close()