python mysql应用

pyhton里面我用于连接mysql数据库：【pymysql】这个库

首先需要下载pymysql这个库

创建打开mysql数据方法：

 1 import pymysql
 2 
 3 host = 'localhost'
 4 port = 3306
 5 user = 'root'
 6 pw = '123456'
 7 database = 'yourdatabase'
 8 
 9 def get_connection():
10     conn = pymysql.connect(host=host, user=user, password=pw, port=port,database=database)
11     return conn

查询：

 1 def selectdb(sql):
 2     db = get_connection()
 3     cursor = db.cursor()
 4     # 执行查询语句
 5     cursor.execute(sql)
 6     # 获取查询结果
 7     results = cursor.fetchall()
 8     cursor.close()
 9     db.close()
10     return results

新增：

 1 # tablename 表名，fieldname为新增那些字段，value 新增字段对应的值（元组）
 2 def insertdb(tablename, fieldname, value):
 3     db = get_connection()
 4     cursor = db.cursor()
 5     # 拼接查询sql
 6     sql = 'insert into ' + tablename + ' (' + fieldname + ') values ('
 7     # 判断有多少个字段需要新增值
 8     for item in fieldname.split(','):
 9         sql += '%s, '
10     sql = sql[0:len(sql) - 2] + ')'
11     id = 0
12     try:
13         cursor.execute(sql, value)
14         # 执行上面的sql
15         db.commit()
16         # 获取自增长的ID
17         id = cursor.lastrowid
18         print('插入成功' + tablename)
19     except Exception as e:
20         # 报错回滚sql
21         db.rollback()
22         print('插入失败' + tablename)
23         print(e.value)
24     finally:
25         cursor.close()
26         db.close()
27         return id

批量新增：

 1 def batchinserdb(tablename, fieldname, value):
 2     db = get_connection()
 3     cursor = db.cursor()
 4     sql = 'insert into ' + tablename + ' (' + fieldname + ') values ('
 5     for item in fieldname.split(','):
 6         sql += '%s, '
 7     sql = sql[0:len(sql) - 2] + ')'
 8     try:
 9         # 批量新增
10         for item1 in value:
11             cursor.execute(sql, tuple(item1.values()))
12         db.commit()
13         print('插入成功' + tablename)
14     except Exception as e:
15         db.rollback()
16         print('插入失败' + tablename)
17         print(e)
18     finally:
19         cursor.close()
20         db.close()

更新：

 1 # sql 更新语句， val更新值和条件值元组
 2 def updatedb(sql,val):
 3     db = get_connection()
 4     cursor = db.cursor()
 5     try:
 6         # sql = "UPDATE customers SET address = %s WHERE address = %s"
 7         cursor.execute(sql, val)
 8         db.commit()
 9         print('更新成功')
10     except:
11         db.rollback()
12         print('更新失败')
13     finally:
14         cursor.close()
15         db.close()