Shulou(Shulou.com)06/03 Report--

This article will explain in detail how to use the PyMySQL module. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Official tutorial import pymysql.cursors# connection database connection = pymysql.connect (host='localhost', user='user', password='passwd', db='db', charset='utf8mb4' Cursorclass=pymysql.cursors.DictCursor) try: with connection.cursor () as cursor: # add a data sql = "INSERT INTO `users` (`email`, `password`) VALUES (% s,% s)" cursor.execute (sql, ('webmaster@python.org') 'very-secret')) connection.commit () with connection.cursor () as cursor: # query data sql = "SELECT `id`, `password` FROM `users`email` =% s" cursor.execute (sql, (' webmaster@python.org',)) result = cursor.fetchone () print (result) finally: connection.close () for reference # get the cursor object entering the SQL statement Specifies that the returned data format is in dictionary format cursor= conn.cursor (cursor=pymysql.cursors.DictCursor) # execute sql statement cursor.execute (sql, (username) Pwd) # commit transaction conn.commit () # get a single query data ret = cursor.fetchone () # return all data ret = cursor.fetchall () # rollback conn.rollback () basic use # Import module import pymysqlusername = input ("Please enter user name:") pwd = input ("Please enter password:") # Connect database conn = pymysql.connect (host= "localhost", port=3306 Database= "userinfo", user='root', password= "123456", charset= "utf8") # get the cursor object of the input SQL statement cursor = conn.cursor () # define the SQL statement to be executed sql = "select * from info WHERE username=%s and password=%s" "# execute the sql statement and let pymysql help us splice the SQL statement cursor.execute (sql, (username, pwd)) # close the cursor object cursor.close () # close the database connection conn.close ()

Return data in dictionary format

# get the cursor object of the input SQL statement Specifies that the returned data format is in dictionary format cursor= conn.cursor (cursor=pymysql.cursors.DictCursor) add, delete, modify and search operation add import pymysql# to get user input username = input ("Please enter user name:") pwd = input ("Please enter password:") # Connect database conn = pymysql.connect (host= "localhost", port=3306, database= "userinfo" User='root', password= "123456", charset= "utf8") # get the cursor object of the input SQL statement cursor = conn.cursor () # sql statement sql = "insert into info (username, password) VALUES (% s,% s) "# execute sql statement cursor.execute (sql, [username, pwd]) # submit conn.commit () # close the cursor object and connect cursor.close () conn.close ()

Failed to insert data rollback

When performing the add, delete and modify operation, if you do not want to submit the previous operation, you can use rollback () to roll back and cancel the operation.

Import pymysql# gets user input username = input ("Please enter user name:") pwd = input ("Please enter password:) # Connect database conn = pymysql.connect (host=" localhost ", port=3306, database=" userinfo ", user='root', password=" 123456 " Charset= "utf8") # get the cursor object of the input SQL statement cursor = conn.cursor () # splicing sql statement sql = "insert into info (username, password) VALUES (% s,% s) "# execute sql statement # ask pymysql to help us concatenate the SQL statement (the parameters passed in can be lists or meta-ancestors Must be an iterable object) try: cursor.execute (sql, [username, pwd]) # submit conn.commit () except Exception as e: print ("error:", str (e)) conn.rollback () # rollback # close the cursor object and connect cursor.close () conn.close ()

Get the ID of the inserted data (used when associating the operation)

Import pymysql# gets user input username = input ("Please enter user name:") pwd = input ("Please enter password:) # Connect database conn = pymysql.connect (host=" localhost ", port=3306, database=" userinfo ", user='root', password=" 123456 " Charset= "utf8") # get the cursor object of the input SQL statement cursor = conn.cursor () # splicing sql statement sql = "insert into info (username, password) VALUES (% s% s) "# execute sql statement cursor.execute (sql, [username, pwd]) # submit conn.commit () # after submission, IDlast_id = cursor.lastrowid# that gets the data just inserted closes the cursor object and connects cursor.close () conn.close ()

Batch execution

Import pymysql# connection database conn = pymysql.connect (host= "localhost", port=3306, database= "userinfo", user='root', password= "123456" Charset= "utf8") # get cursor object cursor = conn.cursor () data = [("Benz", 123456), ("Baby", 123456), ("Master", 123456)] # sql statement sql = "insert into info (username, password) VALUES (% s,% s)" # insert cursor.executemany in bulk (sql Data) # submit conn.commit () # close cursor object and connection cursor.close () conn.close () delete import pymysql# connection database conn = pymysql.connect (host= "localhost", port=3306, database= "userinfo", user='root', password= "123456" Charset= "utf8") # get cursor object cursor = conn.cursor () # sql statement sql = "delete from info where id=%s" # execute SQL statement cursor.execute (sql, [1]) # commit transaction conn.commit () # close cursor object and connect cursor.close () conn.close () change import pymysql# connection database conn = pymysql.connect (host= "localhost" Port=3306, database= "userinfo", user='root', password= "123456" Charset= "utf8") # get the cursor object of the input SQL statement cursor = conn.cursor () # sql statement sql = "update info set password=%s where id=%s" password= "654321" id= 2cursor.execute (sql, [password, id]) conn.commit () cursor.close () conn.close ()

Query single piece of data

Import pymysql# connects to the database conn = pymysql.connect (host= "localhost", port=3306, database= "userinfo", user='root', password= "123456", charset= "utf8") # to get the cursor object for entering the SQL statement Specifies that the returned data format is in dictionary format cursor= conn.cursor (cursor=pymysql.cursors.DictCursor) sql = "select * from info" # commit transaction cursor.execute (sql) # get a single query data ret = cursor.fetchone () # return all data ret = cursor.fetchall () # close the cursor and connect cursor.close () conn.close () advanced usage

After the query, the cursor will move, and the next query will start a new query after the last query.

# can get a specified amount of data cursor.fetchmany (3) # cursor moves 1cursor.scroll (1, mode= "absolute") # cursor moves according to relative position (current position) 1cursor.scroll (1, mode= "relative") error handling exception description Warning is triggered when there is a serious warning, such as the insertion data is truncated and so on. All other error classes except Error warnings. InterfaceError is triggered when an error occurs in the database interface module itself (not in the database). Triggered when a database-related error occurs in DatabaseError. DataError is triggered when an error occurs when there is a data processing error, such as a zero division error, data out of range, etc. OperationalError refers to errors that occur when manipulating the database, not controlled by the user. For example: unexpected disconnection, database name not found, transaction failure, memory allocation error, and so on, the operation of the database is an error. Errors related to IntegrityError integrity, such as foreign key check failure. Internal errors in the InternalError database, such as cursor failure, transaction synchronization failure, and so on. ProgrammingError program errors, such as data table (table) is not found or already exists, SQL statement syntax error, parameter number error, and so on. NotSupportedError does not support errors, which refers to the use of functions or API that are not supported by the database. For example, use the .rollback () function on a connection object, but the database does not support transactions or transactions have been closed. This is the end of the article on "how to use the PyMySQL module". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.