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()