In the first season of snowball learning python, we have contacted the operation sqlite of Python. This blog introduces the story between python, mysql and nosql.
Before formal study, make sure that mysql is installed on your computer.
mysql installation blog: Supplementary knowledge: the latest windows installation tutorial of mysql in 2021, snowball learning python season 4.
mysql some simple commands
For the detailed study of mysql, we should open a separate series. This flag is established first. In order to learn how python operates mysql database, we can only do some basic groundwork first.
View all databases
show databases;
view help
help
Exit mysql
exit
Create database
create database cadb;
Delete database
drop database cadb;
Use a database
use cadb;
View library table
show tables;
View table structure
desc Table name;
In addition to the above contents, you also need to learn the statement of creating a table, the statement of deleting a table, the statement of inserting data into a table, the statement of deleting data from a table, and the statement of updating data from a table, which will be implemented later in python operation mysql.
python operation mysql
The operation database is generally called CRUD operation (add, Delete, change and query) by programmers, in which each character represents C (Create) new, R (Read) Read, U (Update) Update and D (Delete) Delete.
In python3, it is recommended to use the pymysql module to operate the database. The module uses the command pip install PyMySQL to install.
Before that, create a table through MySQL Command Line Client for testing.
The commands for creating a new library, creating a new table, and inserting data are as follows
show databases; create database cadb; use cadb; -- Create table statement create table user(name varchar(10),uid int,primary key(uid)); desc user;
Next, insert 2 pieces of data into the table.
insert into user(name,uid) values ("eraser",1);
insert into user(name,uid) values ("CSDN",2);
The simple sql query is as follows:
select * from user;
Next, you can write the relevant code to operate the library table in python. The database linking steps are as follows:
- Connect the database and generate the connection object;
- Create a cursor object to access the data table;
- Execute sql statements;
- Close the cursor;
- Close the connection.
python query data
python connects to the database to get data
import pymysql # python connects to the database and returns the database connection object conn = pymysql.connect(host="127.0.0.1", user="root", password="xiangpica", database="cadb", charset="utf8") print(conn)
The output connection objects are as follows:
<pymysql.connections.Connection object at 0x00000000021E2F60>
The parameters related to the connect method can be judged by name.
The next step is to create a cursor and extract data.
# Create a cursor object through cursor() and output the query results in dictionary format cur = conn.cursor(cursor=pymysql.cursors.DictCursor) # Output < pymysql. Cursors. Dictcursor object at 0x0000000002c03b00 >
Next, execute the sql using the execute method of the cursor object cur.
cur.execute("select * from user")
Finally, the query results are obtained through relevant methods. In this case, the fetchall method used by all users is obtained.
# Get query results data = cur.fetchall()
Enter all data retrieved from the database.
[{'name': 'eraser', 'uid': 1}, {'name': 'CSDN', 'uid': 2}]
Close cursor, close connection
# Close cursor cur.close() # Close database connection conn.close()
Next, we modify it more complex, retrieve specific data, such as uid=1, and use the context manager with to close the object.
The first way to write
import pymysql
# python connects to the database and returns the database connection object
conn = pymysql.connect(host="127.0.0.1", user="root", password="xiangpica", database="cadb", charset="utf8")
# print(conn)
with conn.cursor(cursor=pymysql.cursors.DictCursor) as cur:
cur.execute("select * from user where uid = %s", 1)
# Get query results
data = cur.fetchall()
print(data)
# Close database connection
conn.close()
The second way to write
import pymysql
# python connects to the database and returns the database connection object
conn = pymysql.connect(host="127.0.0.1", user="root", password="xiangpica", database="cadb", charset="utf8")
# print(conn)
with conn.cursor(cursor=pymysql.cursors.DictCursor) as cur:
cur.execute("select * from user where uid = %(uid)s", {"uid": 1})
# Get query results
data = cur.fetchall()
print(data)
# Close database connection
conn.close()
For the two writing methods, there are differences in the parameters of the execute method, which can be directly compared and studied.
python update data table data
The next demonstration is to use python to update the table data, such as updating the name column with uid = 2 to Code.
import pymysql
# python connects to the database and returns the database connection object
conn = pymysql.connect(host="127.0.0.1", user="root", password="xiangpica", database="cadb", charset="utf8")
# print(conn)
with conn.cursor(cursor=pymysql.cursors.DictCursor) as cur:
try:
cur.execute("update user set name =%s where uid = %s", ["Code", 2])
conn.commit() # Commit transaction
except Exception as e:
print("Data update exception", e)
conn.rollback() # Data rollback
# Close database connection
conn.close()
The core of the above code is that after executing the SQL operation, you need to use transaction processing, conn.commit, to commit the transaction to ensure that the current modification takes effect. If an exception occurs, you also need to use conn.rollback to roll back the committed transaction.
cursor. execute can return the number of affected rows. You can directly judge whether the modification is successful through this value.
This code also uses the try exception statement. For the built-in exception classes of PyMySQL module, the list is as follows:
- StandardError
- Warning
- Error
- InterfaceError
- DatabaseError
- DataError
- OperationalError
- IntegrityError
- ProgrammingError
- NotSupportedError
New data table data in python
Next, we will demonstrate how to add data. The data inserted this time is hard coded. If you want to create an automatically increasing table field, you need to continue to study the relevant writing methods of sql.
import pymysql
# python connects to the database and returns the database connection object
conn = pymysql.connect(host="127.0.0.1", user="root", password="xiangpica", database="cadb", charset="utf8")
# print(conn)
with conn.cursor(cursor=pymysql.cursors.DictCursor) as cur:
try:
affected = cur.execute("insert into user(name,uid) values (%s,%s)", ["Good", 3])
conn.commit() # Commit transaction
print("Number of rows affected", affected)
except Exception as e:
print("Data insertion exception", e)
conn.rollback() # Data rollback
# Close database connection
conn.close()
python delete table data
The core code is as follows:
affected = cur.execute("delete from user where uid = %s", [3])
conn.commit() # Commit transaction
print("Number of rows affected", affected)
Here, you can complete a simple database operation encapsulation in python by yourself.
dbm of python operating nosql
mysql data relational database corresponds to non relational database, such as the built-in dbm (database manager) in python
The first method to learn is dbm.open. The prototype of the method is as follows:
def open(file, flag='r', mode=0o666):
flag parameters:
- r: read only;
- w: write only;
- n: always create a database, and the opening method is read-write;
- c: if it exists, it will not be created. If it does not exist, it will be created.
After closing the database and using dbm.close(), there is no need to release resources manually after using the with statement.
with dbm.open(xxx) as db: pass
dbm storage is similar to python dictionary in the form of key value pairs.
import dbm
with dbm.open('example.db', 'n') as db:
db['name'] = 'eraser' # Stored value
db['age'] = '18' # values must be bytes or strings
Read the value with db[key]. If there is no key value, you will be prompted with KeyError error.
import dbm
with dbm.open('example.db', 'r') as db:
ca = db["name"]
age = db["age"]
print(ca)
The data stored in the dbm database is of string type or byte sequence type, and the read value is of byte sequence type. It needs to be converted into a string using the decoding function decode.
Write it at the back
The above content is the whole content of this article. I hope it will help you on the way to study~
Today is the 230 / 365 day of continuous writing.
Look forward to attention, praise, comment and collection.
More wonderful