Python connects to MySQL database using the Pymysql module

Source: Internet
Author: User
Tags rollback

This article describes the basic use of the third-party library--pymysql that Python3 connects to MySQL.

Pymysql Introduction

Pymysql is a library used in the python3.x version to connect to the MySQL server, and MySQLdb is used in Python2.

You can also use Pymysql to connect to the MySQL database in Django.

Pymysql Installation
Install Pymysql
Connection Database Considerations

Be aware of the following before you proceed with this article:

    • You have a MySQL database and have started.
    • You have a user name and password that you can connect to the database
    • You have a database that has permission to manipulate
Basic use
#Import Pymysql ModuleImportPymysql#Connect to Databaseconn = Pymysql.connect (host= "Your database Address", user= "user name", password= "password", database= "database name", charset="UTF8")#get a Cursor object that can execute SQL statementscursor =conn.cursor ()#define the SQL statement to executesql ="""CREATE TABLE USER1 (id INT auto_increment PRIMARY KEY, name CHAR (TEN) NOT null unique,age TINYINT NOT NULL) Engine=innod b DEFAULT Charset=utf8;"""#Execute SQL statementcursor.execute (SQL)#close the Cursor objectcursor.close ()#To close a database connectionConn.close ()

Returns the dictionary format data:

#Import Pymysql ModuleImportPymysql#Connect to Databaseconn = Pymysql.connect (host= "Your database Address", user= "user name", password= "password", database= "database name", charset="UTF8")#get a cursor that can execute an SQL statement and return the result as a dictionarycursor = Conn.cursor (cursor=pymysql.cursors.DictCursor)#define the SQL statement to executesql ="""CREATE TABLE USER1 (id INT auto_increment PRIMARY KEY, name CHAR (TEN) NOT null unique,age TINYINT NOT NULL) Engine=innod b DEFAULT Charset=utf8;"""#Execute SQL statementcursor.execute (SQL)#close the Cursor objectcursor.close ()#To close a database connectionConn.close ()

Attention:

charset= "UTF8", encoding not written "Utf-8"

Add and delete to change the operation increased
#Import Pymysql ModuleImportPymysql#Connect to Databaseconn = Pymysql.connect (host= "Your database Address", user= "user name", password= "password", database= "database name", charset="UTF8")#get a Cursor object that can execute SQL statementscursor =conn.cursor () SQL="INSERT into USER1 (name, age) VALUES (%s,%s);"username="Alex" Age= 18#Execute SQL statementcursor.execute (SQL, [username, age])#Commit a transactionConn.commit () cursor.close () Conn.close ( )

Insert Data Failure rollback

#Import Pymysql ModuleImportPymysql#Connect to Databaseconn = Pymysql.connect (host= "Your database Address", user= "user name", password= "password", database= "database name", charset="UTF8")#get a Cursor object that can execute SQL statementscursor =conn.cursor () SQL="INSERT into USER1 (name, age) VALUES (%s,%s);"username="Alex" Age= 18Try:    #Execute SQL statementcursor.execute (SQL, [username, age])#Commit a transactionConn.commit ()exceptException as E:#there is an exception, rolling back the transactionConn.rollback () cursor.close () Conn.close ( )

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

#Import Pymysql ModuleImportPymysql#Connect to Databaseconn = Pymysql.connect (host= "Your database Address", user= "user name", password= "password", database= "database name", charset="UTF8")#get a Cursor object that can execute SQL statementscursor =conn.cursor () SQL="INSERT into USER1 (name, age) VALUES (%s,%s);"username="Alex" Age= 18Try:    #Execute SQL statementcursor.execute (SQL, [username, age])#Commit a transactionConn.commit ()#after you commit, get the ID of the data you just insertedlast_id =Cursor.lastrowidexceptException as E:#there is an exception, rolling back the transactionConn.rollback () cursor.close () Conn.close ( )

Batch Execution

#Import Pymysql ModuleImportPymysql#Connect to Databaseconn = Pymysql.connect (host= "Your database Address", user= "user name", password= "password", database= "database name", charset="UTF8")#get a Cursor object that can execute SQL statementscursor =conn.cursor () SQL="INSERT into USER1 (name, age) VALUES (%s,%s);"Data= [("Alex", 18), ("Egon", 20), ("Yuan"21st)]Try:    #batch execution of multiple insert SQL statementscursor.executemany (SQL, data)#Commit a transactionConn.commit ()exceptException as E:#there is an exception, rolling back the transactionConn.rollback () cursor.close () Conn.close ( )
By deleting
#Import Pymysql ModuleImportPymysql#Connect to Databaseconn = Pymysql.connect (host= "Your database Address", user= "user name", password= "password", database= "database name", charset="UTF8")#get a Cursor object that can execute SQL statementscursor =conn.cursor () SQL="DELETE from USER1 WHERE id=%s;"Try: Cursor.execute (SQL, [4])    #Commit a transactionConn.commit ()exceptException as E:#there is an exception, rolling back the transactionConn.rollback () cursor.close () Conn.close ( )
Change
#Import Pymysql ModuleImportPymysql#Connect to Databaseconn = Pymysql.connect (host= "Your database Address", user= "user name", password= "password", database= "database name", charset="UTF8")#get a Cursor object that can execute SQL statementscursor =conn.cursor ()#SQL statement to modify datasql ="UPDATE USER1 SET age=%s WHERE name=%s;"username="Alex" Age= 80Try:    #Execute SQL statementcursor.execute (SQL, [age, username])#Commit a transactionConn.commit ()exceptException as E:#there is an exception, rolling back the transactionConn.rollback () cursor.close () Conn.close ( )

Check

Querying single data

#Import Pymysql ModuleImportPymysql#Connect to Databaseconn = Pymysql.connect (host= "Your database Address", user= "user name", password= "password", database= "database name", charset="UTF8")#get a Cursor object that can execute SQL statementscursor =conn.cursor ()#SQL statement for querying datasql ="SELECT id,name,age from USER1 WHERE id=1;"#Execute SQL statementcursor.execute (SQL)#get a single query dataRET =Cursor.fetchone () cursor.close () Conn.close ( )#Print the results of the queryPrint(ret)

Querying more than one piece of data

#Import Pymysql ModuleImportPymysql#Connect to Databaseconn = Pymysql.connect (host= "Your database Address", user= "user name", password= "password", database= "database name", charset="UTF8")#get a Cursor object that can execute SQL statementscursor =conn.cursor ()#SQL statement for querying datasql ="SELECT id,name,age from USER1;"#Execute SQL statementcursor.execute (SQL)#get more than one query dataRET =Cursor.fetchall () cursor.close () Conn.close ( )#Print the results of the queryPrint(ret)
Advanced usage
# a specified number of data can be obtained Cursor.fetchmany (3)#  cursor is moved by absolute position 1cursor.scroll (1, mode=" Absolute " #  cursor moves in relative position (current position) 1cursor.scroll (1, mode="relative") )

Python connects to MySQL database using the Pymysql module

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.