python3 简单操作mysql

#!/usr/local/bin/python3

#-*- coding: UTF-8 -*-

 

import pymysql

import time

 

# 打开数据库连接

db = pymysql.connect(

    host='10.10.11.70',

    port=12336,

    user='root',

    passwd='Dw.2016%',

    db='nr',

    charset='utf8'

)

 

# 使用 cursor() 方法创建一个游标对象 cursor

cursor = db.cursor() 

 

# 插入数据

sql="insert into nr_test(nr_id,customer_id,create_time)values('%s','%s','%s') "

#获取当前时间

curtime=time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())

param = (1, 10, curtime)

# 执行sql语句

cursor.execute(sql % param)

# 提交到数据库执行

db.commit()

# 打印

print('成功插入', cursor.rowcount, '条数据')

 

#查询数据

sql="select test_id,nr_id,customer_id,create_time from nr_test t where t.nr_id='%s' "

param=(1)

cursor.execute(sql % param)

print('共查找出', cursor.rowcount, '条数据')

for row in cursor.fetchall():

  print("test_id:%s\tnr_id:%s\tcust_id:%s\ttime:%s\t" % row)

 

#修改数据

sql = "update nr_test set customer_id = 20 where test_id = '%s' "

param = (2)

cursor.execute(sql % param)

db.commit()

print('成功修改', cursor.rowcount, '条数据')

 

#删除数据

sql = "delete from nr_test where test_id = '%s' "

param = (1)

cursor.execute(sql % param)

db.commit()

print('成功删除', cursor.rowcount, '条数据')

 

#事务处理

sql_1 = "insert into nr_test(nr_id,customer_id,create_time)values(2,20,'2018-10-26 14:05:01') "

sql_2 = "insert into nr_test(nr_id,customer_id,create_time)values(2,20,'2018-10-26 14:05:02') "

sql_3 = "insert into nr_test(nr_id,customer_id,create_time)values(2,20,'2018-10-26 14:05:03') "

 

try:

    cursor.execute(sql_1)

    cursor.execute(sql_2)

    cursor.execute(sql_3)

except Exception as e:

    connect.rollback()  # 事务回滚

    print('事务处理失败', e)

else:

    db.commit()  # 事务提交

    print('事务处理成功', cursor.rowcount)

 

cursor.close()

# 关闭数据库连接

db.close()