Database operation in python
Recently, after setting up, you need to add a database and record it.
1, Database programming interface
It focuses on the connection object and cursor object in the database API interface
1. Connection object
The database connection object mainly provides methods to obtain the database cursor object, commit / rollback transactions, and close the database connection.
Use the connect function to get the object, which has multiple parameters:
- host hostname
- database/db database name
- User user name
- Password user password
- charset encoding method
Use PyMySQL module to connect to MySQL database.
conn = pymysql.connect(host='localhost',user='user',password='passwd',db='test',charset='utf-8')
The connect function returns a connection object that represents the current session with the database.
The methods supported by the connection object are as follows:
- close() close the database connection
- commit() commit transaction
- rollback() rollback transaction
- cursor() gets cursor objects and operates the database, such as executing DML operations and calling stored procedures
For the commit method here, commit is used to commit transactions, which mainly deal with data with large amount and high complexity. If the operation is a series of actions, such as Zhang San transferring money to Li Si: then Zhang San's account balance decreases and Li Si's account balance increases, then using transactions can maintain the integrity of the database, that is, to ensure that the two operations are either fully executed or not executed.
For cursor objects, they represent cursors in the database and are used to indicate the context of data fetching operations. They mainly provide methods such as executing SQL statements, calling stored procedures, and obtaining query results.
The cursor method of the connection object can be used to obtain the cursor object. The attributes of the cursor object are as follows:
-
Description attribute: indicates the database column type and the value description information
-
rowcount attribute: returns the row count statistics of the result, such as SELECT, UPDATE, and CALLPROC
-
callproc(procname[,parameters]) calls stored procedures and requires database support
-
close() closes the current cursor
-
execute(operation[,parameters]) executes database operations, SQL statements or database commands
2, Use built-in SQLite
SQLite is an embedded database, which is itself a file. SQLite stores the entire database (including tables, indexes, and data itself) in the host as a single file that can be used across platforms. Because SQLite itself is developed in C language and is small in size, it is often integrated into various applications. SQLite is built in Python, so using SQLite database in Python does not need to install any modules, and it can be used directly.
1. Create database file
Create SQLite database file:
import sqlite3
# Connect to SQLite database
# The database file is mrsoft DB if the file does not exist, it will be automatically created in the current directory
conn = sqlite3.connect('mrsoft.db')
# Create a cursor
cursor = conn.cursor()
# Use cursor object to execute SQL statement and create user table
cursor.execute('create table user (id int(10) primary key, name varchar(20))')
# Close cursor
cursor.close()
# Close Connection
conn.close()
In the above code, the sqlite3.connect() method is used to connect the SQLite database file mrsoft.db. Since the mrsoft.db file does not exist, the mrsoft.db file will be created in the same level directory of the python code of this example, which contains the relevant information of the user table.
But if you execute the above code again, you will be prompted with an error message because the user table already exists.
2. Operate SQLite
To add data to the data table, you can use the insert statement in SQL.
The syntax is as follows:
**insert into Table name (field name 1, field name 2, field name 3) values(Field value 1, field value 2,...field value n)**
In the user table created above, there are two fields named ID and name. The field value needs to be assigned according to the type of field.
Then execute the SQL statement:
cursor.execute('insert into user(id,name) values(1,"MRSOFT")')
cursor.execute('insert into user(id,name) values(2,"Andy")')
cursor.execute('insert into user(id,name) values(3,"SiRi")')
Case 1: insert three records into the database
import sqlite3
# Connect to SQLite database
# The database file is mrsoft DB if the file does not exist, it will be automatically created in the current directory
conn = sqlite3.connect('mrsoft.db')
# Create a cursor
cursor = conn.cursor()
# Use cursor object to execute SQL statement and create user table
# cursor.execute('create table user (id int(10) primary key, name varchar(20))')
# Execute insert statement single outside quotation mark and double inside quotation mark
cursor.execute('insert into user(id,name) values(1,"MRSOFT")')
cursor.execute('insert into user(id,name) values(2,"Andy")')
cursor.execute('insert into user(id,name) values(3,"Siri")')
# Close cursor
cursor.close()
# Commit transactions commit transactions using database connection objects
conn.commit()
# Close Connection
conn.close()
Case 2: viewing user information
To find the data in the data table, you can use the select statement in SQL:
select Field name 1, field name 2, field name 3, from Table name where query criteria
This is the data set that meets the query conditions, but we may only need some data, so we also need specific query statements.
-
fetchone(): get the next record in the query result set
-
fetchmany(size): get a specified number of records
-
fetchall(): get all records of the structure set
import sqlite3
con = sqlite3.connect('mrsoft.db ') # connect to the databaseCreate a cursor to get the cursor object
cursor = con.cursor()
Execute query statement
cursor.execute('select * from user')
Get the query result, get the first record
fetchone returns a tuple
result1 = cursor.fetchone()
print(result1)Close cursor
cursor.close()
Close connection
con.close()
import sqlite3
con = sqlite3.connect('mrsoft.db ') # connect to the databaseCreate a cursor to get the cursor object
cursor = con.cursor()
Execute query statement
cursor.execute('select * from user')
Get the query result, get the first record
fetchone returns a tuple
result1 = cursor.fetchone()
print(result1)
Using fetchmany to query multiple pieces of data
Returns a list with two tuples
result2 = cursor.fetchmany(2)
print(result2)
result3 = cursor.fetchall() # get all data
print(result3)Close cursor
cursor.close()
Close connection
con.close()
import sqlite3
con = sqlite3.connect('mrsoft.db ') # connect to the databaseCreate a cursor to get the cursor object
cursor = con.cursor()
Execute the query statement to query all tuples with ID > 1
cursor.execute('select * from user where id > 1')
Get the query result, get the first record
fetchone returns a tuple
result1 = cursor.fetchone()
print(result1)
Using fetchmany to query multiple pieces of data
Returns a list with two tuples
result2 = cursor.fetchmany(2)
print(result2)
result3 = cursor.fetchall() # get all data
print(result3)Close cursor
cursor.close()
Close connection
con.close()
Case 3: modifying user data information
The syntax is as follows:
update Table name set Field name = field value where query criteria
import sqlite3
# Connect to the database
con = sqlite3.connect("mrsoft.db")
# Create cursor object
cursor = con.cursor()
# Perform database update operation
cursor.execute('update user set name = "xiaxuefei" where id = 1')
cursor.execute('select * from user')
result = cursor.fetchall()
print(result)
cursor.close()
con.commit() # Commit transaction
con.close()
Case 4: deleting user information
The delete statement in SQL can be used to delete the data in the database table. The syntax is as follows:
delete from Table name where query criteria
import sqlite3
# Connect to the database
con = sqlite3.connect('mrsoft.db')
# Create a cursor object
cursor = con.cursor()
# Delete user with ID 1
cursor.execute('delete from user where id = 1')
# Get all user information
cursor.execute('select * from user')
# Record query results
result = cursor.fetchall()
print(result)
# Close cursor
cursor.close()
# Commit transaction
con.commit()
# Close connection
con.close()
3, Use of MySql database
3.1 install MySql
Enter the select setting type interface. Five types:
- Developer Default: tools required for installing MySql server and developing MySql applications, including GUI platform for developing and managing server, EXCEL plug-ins for accessing operation data, plug-ins developed with Visual Studio integration, connectors for accessing data through NET/Java/C/C++/ODBC, official sample tutorials, and development documents.
- Server only: only install MySql server, which is suitable for deploying MySql server
- Client only: only install the client, which is applicable to MySQL application development based on the existing MySQL server
- Full: install all available components of MySql
- Custom: customize the required components
3.2 setting environment variables
After installation, the default path must be "C:Program FilesMySQL Server 8.0in". Create an environment variable and write the path.
3.3 start MySql
Use the following two commands:
net start mysql # Start mysql server mysql -u root -p # You need to enter a password to enter the MySQL server
Note that entering the above two commands requires administrator permission.
3.4 using navicat for mysql management software
Here I install the cracked version of navicat for mysql
You can customize the installation path:
Then use PatchNavicat.exe to crack. It can be used normally
Link: https://pan.baidu.com/s/1SeCf0BCIO1LHB5-tjPD0OQ?pwd=8x2u Extraction code: 8 x2u --From Baidu online disk super member V2 Share of
Then create a new connection:
Enter the connection name, host name or IP address, fill in: localhost or 127.0.0.1, and enter the login password of the MYsql database. Here is root
Next, use Navicat to create a Mrsoft database, right-click Mr - > select new database - > Enter database information.
3.5 installing PyMysql module
Because the MySQL server runs in an independent process and provides external services through the network, it needs a MySQL driver that supports Python to connect to the MySQL server. There are many modules that support MySQL database in Python. PyMySql is selected here.
The installation commands are as follows:
pip install PyMySQL
3.6 connecting to the database
import pymysql
# Open database connection parameter 1: database domain name or IP; Parameter 2: database account number; Parameter 3: database password parameter 4: database name
db = pymysql.connect(host="localhost",user="root",password="123",database="mrsoft")
# Create a cursor object
cursor = db.cursor()
# Execute SQL query
cursor.execute("SELECT VERSION()")
# Use fetchone() to get a single piece of data
data = cursor.fetchone()
print("DataBase version : %s " % data)
# Close cursor object connection
cursor.close()
# Close database connection
db.close()
The above code is only used to check the version of the database.
The running results are as follows: database version: 5.1.32-community
4, Create data table
Next, create the database table through execute().
4.1 create books table
The books table contains ID (primary key), name (book name), category (book classification), price (book price), and publish_ Time has five fields.
The sql statement is as follows:
CREATE TABLE books(
id int(8) NOT NULL AUTO_INCREMENT,
name varchar(50) NOT NULL,
category varchar(50) NOT NULL,
price decimal(10,2) DEFAULT NULL,
publish_time date DEFAULT NULL,
PRIMARY KEY(id)
)ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
Before creating a database table, test whether the table already exists:
drop table if exists books
It means that if the table books already exists in the mrsoft database, delete books first, and then create the books data table. The specific codes are as follows:
import pymysql
# Open database
db = pymysql.connect(host='localhost',user='root',password='123',database='mrsoft')
# Use cursor() method to execute SQL, and delete the table if it exists
cursor = db.cursor()
# Use execute to execute SQL, and delete the table if it exists
cursor.execute('DROP TABLE IF EXISTS books')
# Create a table using preprocessing statements
sql = """
CREATE TABLE books(
id int(8) NOT NULL AUTO_INCREMENT,
name varchar(50) NOT NULL,
category varchar(50) NOT NULL,
price decimal(10,2) DEFAULT NULL,
publish_time date DEFAULT NULL,
PRIMARY KEY(id)
)ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
"""
# Execute SQL statement
cursor.execute(sql)
db.close()
4.2 operating MySql data tables
CRUD is the main operation of MySQL data table.
Case: batch adding book data:
To insert data, you can use execute() to add a record, or you can use the executemany() method to batch add multiple records. The executemany() syntax is as follows:
executemany(operation,seq_of_params)
Operation: SQL statement of operation
seq_of_params: parameter sequence
Use executemany() to batch add multiple records.
import pymysql
# Open database
db = pymysql.connect(host='localhost',user='root',password='123',database='mrsoft')
# Use cursor() method to execute SQL, and delete the table if it exists
cursor = db.cursor()
data = [("Zero basic learning python",'python','1',"2018-09-10"),("Zero basic learning java",'java','11',"2018-09-11")]
try:
# Execute sql statement to insert multiple pieces of data. The second parameter is the data to be inserted
cursor.executemany("insert into books(name,category,price,publish_time) values(%s,%s,%s,%s)",data)
# Commit transaction
db.commit()
except:
# Rollback when an error occurs
db.rollback()
db.close()
- The connect() method connects to the database and sets charset=utf8 as an additional character set to prevent garbled codes when inserting Chinese.
- When inserting data with Insert statement, using%s as placeholder can prevent SQL injection.
