#!/usr/bin/python3
#coding=utf-8
 
import pymysql
 
# Open database connection
db = pymysql.connect("localhost","root","123456","test" )
 
# prepare a cursor object using cursor() method
cursor = db.cursor()
 
# execute SQL query using execute() method.
cursor.execute("SELECT VERSION()")
 
# Fetch a single row using fetchone() method.
data = cursor.fetchone()
 
print ("Database version : %s " % data)
 
# disconnect from server
db.close()

INSERT

 
#!/usr/bin/python3
#coding=utf-8
 
import pymysql
 
# Open database connection
db = pymysql.connect("localhost","root","123456","test" )
 
# prepare a cursor object using cursor() method
cursor = db.cursor()
 
# Prepare SQL query to INSERT a record into the database.
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
   LAST_NAME, AGE, SEX, INCOME)
   VALUES ('Mac', 'Su', 20, 'M', 5000)"""
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()
 
## 再次插入一条记录
# Prepare SQL query to INSERT a record into the database.
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
   LAST_NAME, AGE, SEX, INCOME)
   VALUES ('Kobe', 'Bryant', 40, 'M', 8000)"""
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()
 
# disconnect from server
db.close()


 
# 传递参数 - ..................................
user_id = "test123"
password = "password"
 
con.execute('insert into Login values("%s", "%s")' % \
             (user_id, password))
 

select

• fetchone() - 它获取查询结果集的下一行。 结果集是当使用游标对象来查询表时返回的对象。
• fetchall() - 它获取结果集中的所有行。 如果已经从结果集中提取了一些行，则从结果集中检索剩余的行。
• rowcount - 这是一个只读属性，并返回受execute()方法影响的行数。

 
#!/usr/bin/python3
#coding=utf-8
 
import pymysql
 
# Open database connection
db = pymysql.connect("localhost","root","123456","test" )
 
# prepare a cursor object using cursor() method
cursor = db.cursor()
# 按字典返回 
# cursor = db.cursor(pymysql.cursors.DictCursor)
 
# Prepare SQL query to select a record from the table.
sql = "SELECT * FROM EMPLOYEE \
       WHERE INCOME > %d" % (1000)
#print (sql)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Fetch all the rows in a list of lists.
   results = cursor.fetchall()
   for row in results:
      #print (row)
      fname = row[1]
      lname = row[2]
      age = row[3]
      sex = row[4]
      income = row[5]
      # Now print fetched result
      print ("name = %s %s,age = %s,sex = %s,income = %s" % \
             (fname, lname, age, sex, income ))
except:
   import traceback
   traceback.print_exc()
 
   print ("Error: unable to fetch data")
 
# disconnect from server
db.close()

UPDATE

 
#!/usr/bin/python3
#coding=utf-8
 
import pymysql
 
# Open database connection
db = pymysql.connect("localhost","root","123456","test" )
 
# prepare a cursor object using cursor() method
#cursor = db.cursor()
cursor = db.cursor(pymysql.cursors.DictCursor)
# prepare a cursor object using cursor() method
cursor = db.cursor()
 
# Prepare SQL query to UPDATE required records
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 \
                          WHERE SEX = '%c'" % ('M')
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()
 
# disconnect from server
db.close()

DELETE

 
#!/usr/bin/python3
#coding=utf-8
 
import pymysql
 
# Open database connection
db = pymysql.connect("localhost","root","123456","test" )
 
# prepare a cursor object using cursor() method
cursor = db.cursor()
 
# Prepare SQL query to DELETE required records
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (40)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()
 
# disconnect from server
db.close()

事务

# Prepare SQL query to DELETE required records
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

json

python使用pymysql查询结果返回json pymysql转json关键参数 cursorclass=cursors.DictCursor

import pymysql
from pymysql import cursors


def sql_json():
    con = pymysql.connect(host=‘127.0.0.1’, user='oo', password='123456', port=3306,db='myuser',cursorclass=cursors.DictCursor)
    cur = con.cursor()
    sql = "select id,username from user_info "
    cur.execute(sql)
    all_obj = cur.fetchall()
    print(all_obj, '\n', type(all_obj[0]))
    cur.close()
    con.close()


if __name__ == '__main__':
    sql_json()