Python connects to MySQL database (using pymysql)

this article python version 3.5.3,mysq version 5.7.23

Basic use

# Imports Pymysql Module Import pymysql# Connection Database conn = Pymysql.connect (    database= "database name", user= "    username",    password= "password",    host= "The address of the data,    port=3306, # port 3306 is fixed    charset=" UTF8 ")  # can only be UTF8, must not be utf-8. Only utf8# in the database Get a Cursor object that can execute SQL cursor = conn.cursor () #定义需要执行的SQL语句sql = "" "CREATE table User (id int auto_increment primary key,name Char (ten) NOT null Unique,age tinyint NOT NULL) engine=innodb default Charset=utf8; "" #执行语句cursor. Execute (SQL) #关闭游标对象cursor. Close () #关闭数据库连接conn. Close ()

Data to be increased and censored

# Importing Pymysql module import pymysql# Connection Database conn = Pymysql.connect (    database= "db",    user= "root",    password= "123456" ,    host= "localhost",    port=3306,    charset= "UTF8") #得到一个可以执行SQL的游标对象cursor = Conn.cursor () # Defines the SQL statement that needs to be executed sql= "" INSERT into User (Name,age) values (%s,%s); "" " Name = "Caocao", age = 34data = [("AAA"), ("BBB", +), ("CCCC", +)]try:    print (SQL)    #插入一条数据SQL语句, insert only a single    # curso R.execute (SQL, (name,age)) # prevents SQL injection    #批量插入多天SQL语句    cursor.executemany (sql,data)  #第二个参数可以是列表或数组    # COMMIT TRANSACTION)    Conn.commit () except Exception as E:    # with exception, ROLLBACK TRANSACTION    conn.rollback ()    print (e) # Close the Cursor object Cursor.close () #关闭数据库连接conn. Close ()

By deleting

# Importing Pymysql module import pymysql# Connection Database conn = Pymysql.connect (    database= "db",    user= "root",    password= "123456" ,    host= "localhost",    port=3306,    charset= "UTF8") #得到一个可以执行SQL的游标对象cursor = Conn.cursor () # Define the SQL statement that needs to be executed sql= "" Delete from user where id=%s "" "Try:    Cursor.execute (sql,[4])    # COMMIT TRANSACTION    Conn.commit () Except Exception as E:    # has an exception, ROLLBACK TRANSACTION    conn.rollback ()    print (e) #关闭游标对象cursor. Close () #关闭数据库连接conn. Close ()

Change

# Importing Pymysql module import pymysql# Connection Database conn = Pymysql.connect (database= "db", user= "root", password= "123456", host= " localhost ", port=3306,charset=" UTF8 ") #得到一个可以执行SQL的游标对象cursor = Conn.cursor () #定义需要执行的SQL语句sql =" "" Update user set age= %s where name=%s; "" Name= "Liu Bei" Age=18try:cursor.execute (SQL, (Age,name)) # COMMIT TRANSACTION Conn.commit () except Exception as e:# with exception, ROLLBACK TRANSACTION conn.rollback () Print (e) #关闭游标对象cursor. Close () #关闭数据库连接conn. Close ()

　　

Check

# Importing Pymysql module import pymysql# Connection Database conn = Pymysql.connect (    database= "db",    user= "root",    password= "123456" ,    host= "localhost",    port=3306,    charset= "UTF8") # Get a Cursor object that can execute SQL # add parameter after result returns the data in dictionary format cursor = conn.cursor (cursor=pymysql.cursors.dictcursor) #定义需要执行的SQL语句sql = "" "Select * from user where id=2;" " sql1= "" "SELECT * from User;" " # SQL statement that executes query data cursor.execute (SQL) # Gets a single query data ret = Cursor.fetchone () # gets multiple query data cursor.execute (SQL1) Ret1 = Cursor.fetchall () #关闭游标对象cursor. Close () #关闭数据库连接conn. Close () # Prints Query results print (ret) print (RET1)

The obtained data is a dictionary type of data, in the transmission process of the data is better

# can get a specified amount of data

Cursor.fetchmany (4)

Python connects to MySQL database (using pymysql)