import pymysql

#建立数据库连接
connection = pymysql.connect(host = 'localhost',
        user = 'root',
        password = '123456',
        database = 'mydb',
        charset = 'utf8'
)
#print(connection)

#建立数据库连接
connection = pymysql.connect(host = 'localhost',
        user = 'root',
        password = '123456',
        database = 'mydb',
        charset = 'utf8'
)
#print(connection)
try:
 #创建游标对象
 with connection.cursor() as cursor:
  #执行SQL操作
  sql = 'select name, userid from user where userid >%(id)s'
  cursor.execute(sql, {'id':0})
  #提取数据集
  result_set = cursor.fetchall()
  for row in result_set:
   print('id:{0} - name:{1}'.format(row[1],row[0]))
  #游标自动关闭
finally:
 #关闭连接
 connection.close()

#数据增加
connection = pymysql.connect(host = 'localhost',
        user = 'root',
        password = '123456',
        database = 'mydb',
        charset = 'utf8'
)
try:
 with connection.cursor() as cursor:
  sql = 'insert into user (userid,name) values (%s,%s)'
  cursor.execute(sql,(3,'cc'))
  #affectcount = cursor.execute(sql,(3,'cc'))
  #print('影响的数据行数：{0}'.format(affectcount))
  #提交数据库事务
  connection.commit()
except pymysql.DatabaseError:
 #数据库事务回滚
 connection.rollback()
finally:
 connection.close()

#数据更新
connection = pymysql.connect(host = 'localhost',
        user = 'root',
        password = '123456',
        database = 'mydb',
        charset = 'utf8'
)
#print(connection)
try:
 with connection.cursor() as cursor:
  sql = 'update user set name = %s where userid > %s'
  cursor.execute(sql,('Tom',2))
  #提交事务
  connection.commit()
  print('更新成功')
except pymysql.DatabaseError as e:
 connection.rollback()
 print(e)
finally:
 connection.close()

#数据删除
connection = pymysql.connect(host = 'localhost',
        user = 'root',
        password = '123456',
        database = 'mydb',
        charset = 'utf8'
        )
try:
 with connection.cursor() as cursor:
  sql = 'delete from user where userid = %s'
  cursor.execute(sql,(1))
  #提交事务
  connection.commit()
  print("删除成功")
except pymysql.DatabaseError as e:
 connection.rollback()
 print(e)
finally:
 connection.close()