Python operation database

Hardware and software Environment

  • windows 10 64bit
  • Python 3.7.4
  • Mysql 8.0.20
  • pymysql

In the development often involves the use of database, and mysql is the most widely used relational database, because it is open source, free. This article will use the Python language to operate mysql database.

Windows installation mysql

To download the installation package on the official website of mysql dev.mysql.com/downloads/i…

Select what you want to install on the left and click the arrow to the right. In the box to the right are the components you want to install. Here we only install the Server and administrative Tools Workbench

The default port number is 3306

Set the password of the root User. To Add a common User, click Add User

Once installed, use workbench to create a database connection, filling in the IP, port, and user name

Log in to the database as user root

Create a database named testDB with utF8 character encoding

Next, create a new table named TestTable with three fields

  • id, the data type isINT(11), set primary key, unique, non-null,UNSIGNED,AUTO INCREMENT
  • name, the data type isVARCHAR(45), set it to non-null
  • sex, the data type isVARCHAR(45), set it to non-null

Example source code

Here we use the Pymysql library to manipulate the database, first installed via PIP

pip install pymysql
Copy the code

The basic operations of the database are Create (Create), Retrieve (Retrieve), Update (Update) and Delete (Delete), the so-called CRUD, which are operated one by one by instance

Import pymysql # connect db = pymysql.connect("localhost", "root", "toor", "testDB ") cursor = db.cursor() # try: INSERT INTO testtable(name, sex) VALUES(' XGX ', 'male')") Rollback (); rollback(); Cursor. execute("SELECT * FROM testtable") Fetchmany () results = cursor.fetchall() for row in results: Print ("name: {}, sex: {}". Format (name, sex)) except: print(" Error: Unable to fetch data") # unable to fetch data SQL = cursor.execute("UPDATE testtable SET sex='female' WHERE id=4") Rollback db. Rollback () SQL = cursor.execute("DELETE FROME testtable WHERE id=4") Rollback () # close db. Close ()Copy the code