MySQL adapter PyMySQL and mysqlpymysql

MySQL adapter PyMySQL and mysqlpymysql

In this article, we will introduce Python3 to connect to the database using PyMySQL, and implement simple addition, deletion, modification, and query.

What is PyMySQL?

PyMySQL is a database used to connect to the MySQL server in Python3.x, and mysqldb is used in Python2.

PyMySQL complies with the Python database API v2.0 specification and contains the pure-Python MySQL client library.

Install PyMySQL
Before using PyMySQL, make sure that PyMySQL is installed.

PyMySQL: https://github.com/pymysql/pymysql.

If not, run the following command to install the latest version of PyMySQL:

$ pip install PyMySQL

If your system does not support the pip command, you can install it in the following ways:

1. Use the git command to download the installation package (you can also manually download it ):

$ git clone https://github.com/PyMySQL/PyMySQL$ cd PyMySQL/$ python3 setup.py install

2. Operate the instance in the database and directly add the code.

Import pymysqlimport datainfoimport time # obtain the parameter host = datainfo. hostusername = datainfo. usernamepassword = datainfo. passworddatabase = datainfo. dbprint () # test the database connection def testconnect (): # Open the database link db = pymysql. connect (host, username, password, database) # Use the cursor () method to create a cursor object cursor = db. cursor () # Use the execute () method to execute SQL query cursor.exe cute ("select version ()") # Use fetchone () to obtain a single data entry = cursor. fetchone () print (data) db. close () # insert database def InsertDate (): # Open Database Link db = pymysql. connect (host, username, password, database, charset = 'utf8') # Use the cursor () method to create a cursor object cursor = db. cursor () create_time = time. strftime ('% Y-% m-% d % H: % M: % s') update_time = time. strftime ('% Y-% m-% d % H: % M: % s') start_time = time. strftime ('% Y-% m-% d % H: % M: % s') end_time = time. strftime ('% Y-% m-% d % H: % M: % s') remark = "test insertion information" print ("START ") # SQL insert statement SQL = "insert into demo (start_time, end_time, creat_time, update_time, remark)" \ "VALUES ('% s',' % s ', '% s',' % s', '% s') "\ % (start_time, end_time, create_time, update_time, remark) try: # Execute SQL print ("execute Insert") tt = cursor.exe cute (SQL) print (tt) db. commit () commit t UnicodeEncodeError as e: # Roll Back print (e) db when an error occurs. rollback () db. close () # query operation def selectData (): db = pymysql. connect (host, username, password, database, charset = 'utf8') # Use the cursor () method to create a cursor object cursor = db. cursor () SQL = "select * from demo where id> = '% d'" % (1) try: # Execute SQL print ("execute query") cursor.exe cute (SQL) results = cursor. fetchall () for row in results: id = row [0] start_time = row [1] end_time = row [2] create_time = row [3] update_time = row [4] remark = row [5] # print the result print ("id = % d, start_time = % s, end_time = % s, create_time = % s, update_time = % s, remark = % s "% (id, start_time, end_time, create_time, update_time, remark )) db. commit () commit t UnicodeEncodeError as e: # Roll Back print (e) db when an error occurs. close () # update def update_data (): db = pymysql. connect (host, username, password, database, charset = 'utf8') # Use the cursor () method to create a cursor object cursor = db. cursor () update_time = time. strftime ('% Y-% m-% d % H: % M: % s ') SQL = "update demo set update_time = '% s' where id> =' % d'" % (update_time, 1) try: # Execute SQL print ("execute update ") cursor.exe cute (SQL) db. commit () commit t UnicodeEncodeError as e: # Roll Back print (e) db when an error occurs. rollback () db. close () # delete operation def delete_Date (): db = pymysql. connect (host, username, password, database, charset = 'utf8') # Use the cursor () method to create a cursor object cursor = db. cursor () SQL = "delete from demo where id <'% d'" % (1) try: # run SQL print ("delete") cursor.exe cute (SQL) db. commit () commit t UnicodeEncodeError as e: # Roll Back print (e) db when an error occurs. rollback () db. close () if _ name _ = '_ main _': testconnect () InsertDate () selectData () update_data () delete_Date ()

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.