Database Introduction
concept
A Database for storing data, which organizes, stores and manages data according to a certain data structure.
classification
Relational databases: mysql, sql server, oracle, DB2, etc.
Non-relational databases: redis, etc.
How python operates on databases
pymysql: pure Python development, supporting python2 and python3, easy to use
Basic database operations
Basic process:
Code implementation steps
pymysql connection database
# Guide Pack
import pymysql
#Create Connection
conn = pymysql.connect(host="localhost",
port=3307,
user="root",
password="root",
database="books")
# Get Cursor
cursor = conn.cursor()
# Execute sql
cursor.execute("select version()")
result = cursor.fetchall()
print(result)
# Close Cursor
cursor.close()
# Close Connection
conn.close()
Run result:
pymysql query operation
The database code used here is as follows:
CREATE DATABASE if not EXISTS books DEFAULT CHARSET utf8;
use books;
Drop TABLE if EXISTS `t_book`;
CREATE TABLE `t_book`(
`id` INT(11) not NULL auto_increment,
`title` VARCHAR(20) NOT NULL COMMENT 'Book Name',
`put_date` date not null COMMENT 'Release date',
`read` int(11) not null DEFAULT '0' COMMENT 'Reading volume',
`comment` int(11) not null default '0' comment 'Comments',
`is_delete` TINYINT(1) not null DEFAULT '0' COMMENT 'Logical Delete',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='Booklist';
INSERT into `t_book` VALUES('1','Heroes of Architecture','1980-05-01','12','34','0');
INSERT into `t_book` VALUES('2','Eight parts of Dragon','1986-07-24','36','40','0');
INSERT into `t_book` VALUES('3','Xiao Ao Jiang Hu','1995-12-24','20','80','0');
DROP TABLE if EXISTS `t_hero`;
CREATE TABLE `t_hero`(
`id` int(11) not null auto_increment,
`name` VARCHAR(20) not NULL COMMENT 'Full name',
`gender` SMALLINT(6) not null COMMENT'Gender',
`description` VARCHAR(200) DEFAULT NULL COMMENT 'describe',
`is_delete` TINYINT(1) not NULL DEFAULT '0' COMMENT 'Logical Delete',
`book_id` int(11) not null COMMENT 'Subordinate Books ID',
PRIMARY KEY(`id`),
key `t_hero_book_id`(`book_id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='Character of Heroes';
INSERT into `t_hero` VALUES('1','Guo Jing','1','beating dragon 18 palms','0','1');
INSERT into `t_hero` VALUES('2','Rongcheng','0','Dog Stick','0','1');
INSERT into `t_hero` VALUES('3','Look crazy','1','beating dragon 18 palms','0','2');
INSERT into `t_hero` VALUES('4','Linghu Chong','1','Du Gu Nine Swords','0','3');
INSERT into `t_hero` VALUES('5','Ren Ying Ying','0','Play the piano','0','3');
The chart is shown in Fig.
Query operation:
"""
1.Connect to the database ( host:localhost user:root password:root database:books)
2.Query the data of the book list (including: books) id,Book Name, Read, Comment)
3.Total number of records to obtain query results
4.Get the first data of the query result
5.Get all query results
"""
import pymysql
# Create Connection
conn = pymysql.connect(host="localhost",
port=3307,
user="root",
password="root",
database="books"
)
# Get Cursor
cursor = conn.cursor()
# Execute sql
# 2. Query the data of the book list
sql = "select id, title, `read`, `comment` from t_book;"
cursor.execute(sql)
# 3. Total number of records to obtain query results
print("The number of rows of query result records obtained is:", cursor.rowcount)
# # 4. The first data to get the query result
# print(cursor.fetchone())
# 5. Get all query results
print(cursor.fetchall())
# Close Cursor
cursor.close()
# Close Connection
Run result:
The code above commented out 4. Get the first data of the query result
Run without commenting on this statement as shown in the figure:
Why does this happen?
Because of cursors.
Cursors are a data access mechanism for SQL, and cursors are a way to process data.
The result of a select query operation using SQL is a dataset containing one or more rows. If we want to query the results of the query again, such as (see the first row, the next row, the last row, the first ten rows of the results, and so on) simply through the select statement cannot be achieved, because the result of the query is not a data table, but a result set that has already been queried.
Cursors occur for this situation
We can simply think of a cursor as a pointer to a result set, scrolling back and forth over the result set as needed to explore the data we need.
How can I solve this problem?
Reset cursor position.
"""
1.Connect to the database ( host:localhost user:root password:root database:books)
2.Query the data of the book list (including: books) id,Book Name, Read, Comment)
3.Total number of records to obtain query results
4.Get the first data of the query result
5.Get all query results
"""
import pymysql
# Create Connection
conn = pymysql.connect(host="localhost",
port=3307,
user="root",
password="root",
database="books"
)
# Get Cursor
cursor = conn.cursor()
# Execute sql
# 2. Query the data of the book list
sql = "select id, title, `read`, `comment` from t_book;"
cursor.execute(sql)
# 3. Total number of records to obtain query results
print("The number of rows of query result records obtained is:", cursor.rowcount)
# 4. The first data to get the query result
print(cursor.fetchone())
# 5. Get all query results
# Reset Cursor Position
cursor.rownumber = 0
print(cursor.fetchall())
# Close Cursor
cursor.close()
# Close Connection
conn.close()
Use print(cursor.rownumber) to see the cursor location first
Then reset the location cursor.rownumber = 0
The results are:
pymysql insert operation
import pymysql
# Create Connection
conn = pymysql.connect(host="localhost",
port=3307,
user="root",
password="root",
database="books",
autocommit=True
)
# Get Cursor
cursor = conn.cursor()
# Execute sql
# 2. Add a new book data
sql = "INSERT into t_book(id, title, put_date) VALUES(4, 'Journey to the West', '1986-01-01');"
cursor.execute(sql)
# 3. Get the number of result records affected
print("The number of records to obtain affected results is:", cursor.rowcount)
# Close Cursor
cursor.close()
# Close Connection
conn.close()
Run result:
Return to the database to view:
pymysql update operation
# Guide Pack
import pymysql
# Create Connection
conn = pymysql.connect(host="localhost",
port=3307,
user="root",
password="root",
database="books",
autocommit=True)
# Get Cursor
cursor = conn.cursor()
# Execute sql
sql = "UPDATE t_book set title='Journey to the East' WHERE title='Journey to the West';"
cursor.execute(sql)
print(cursor.rowcount)
# Close Cursor
cursor.close()
# Close Connection
conn.close()
Run result:
Return to the database to view:
pymysql delete operation
# Guide Pack
import pymysql
# Create Connection
conn = pymysql.connect(host="localhost",
port=3307,
user="root",
password="root",
database="books",
autocommit=True)
# Get Cursor
cursor = conn.cursor()
# Execute sql
sql = "DELETE from t_book where title ='Journey to the East';"
cursor.execute(sql)
print(cursor.rowcount)
# Close Cursor
cursor.close()
# Close Connection
conn.close()
Run result:
Back to database view
Similarities and differences between query and non-query operations
-
Same point: the basic process is the same (create connection, get cursor, execute sql, close cursor, close connection)
-
Difference
Different sql statements to execute
A non-query operation requires a transaction to be opened (specify the parameter autocommit=True when creating a connection)