Python *** 作 MySQL

概述本文链接 "Python *** 作 MySQL" 开发环境与配置 win_x64 Ubuntu14.04 Python3.x pip安装pymysql模块 直接使用pip安装 win6

本文链接Python *** 作 MySQL

开发环境与配置

win_x64Ubuntu14.04python3.xpip安装pyMysqL模块

直接使用pip安装 pip install pyMysqL
win64上直接在cmd中执行

连接本地数据库

使用模块pyMysqL连接数据库
本地数据库相关配置请参阅： http://rustfisher.github.io/2017/02/25/backend/MySQL_install/

#!/usr/bin/python# Coding=utf-8import pyMysqL# 连接本地数据库conn = pyMysqL.connect(host='localhost',port=3306,user='root',passwd='yourpwd',db='samp_db1',charset='utf8')cursor = conn.cursor()cursor.execute('select * from bigstu')for row in cursor.fetchall():    print(row)# 查cursor.execute('select ID,name from bigstu where age > 22')for res in cursor.fetchall():    print(str(res[0]) + "," + res[1])cursor.close()print('-- end --')

输出：

(1,'张三','男',24,datetime.date(2017,3,29),'13666665555')(6,'小刚',23,11),'778899888')(8,'小霞','女',20,13),'13712345678')(12,'小智',21,7),'13787654321')1,张三6,小刚-- end --

可以直接执行SQL语句。获得的结果是元组。

sql相似条件查询

SELECT * FROM anindex.subject_basic_table where season_ID having '2018';

增插入数据

插入一条数据，接上面的代码

insertsql = "insert into bigstu (name,sex,age,mobile) values ('%s','%s',%d,'%s') "xiuji = ('秀吉',15,'13400001111')cursor.execute(insertsql % xiuji)conn.commit() # 别忘了提交

添加列

在mobile后面添加一列cash

addCo = "alter table bigstu add cash int after mobile"cursor.execute(addCo)

如果要设置默认值

addCo = "alter table bigstu add cash int default 0 after mobile"cursor.execute(addCo)

删删除数据

删除 name=秀吉 的数据

deletesql = "delete from bigstu where name = '%s'"cursor.execute(deletesql % '秀吉')

删除列

删除cash列

dropCo = "alter table bigstu drop cash"cursor.execute(dropCo)

改修改数据

更新符合条件的数据

updatesql = "update bigstu set sex = '%s' where name = '%s'"updateXiuji = ('秀吉','秀吉') # 秀吉的性别是秀吉cursor.execute(updatesql % updateXiuji)conn.commit()

事物处理

给某个记录的cash增加

table = "bigstu"addCash = "update " + table + " set cash = cash + '%d' where name = '%s'"lucky = (1000,"秀吉")try:    cursor.execute(addCash % lucky)except Exception as e:    conn.rollback()    print("加钱失败了")else:    conn.commit()

直接执行SQL语句，十分方便

代码片段给数据库添加列

从Json中读取需要添加的列名，获取当前2个表中所有的列名
整理得出需要插入的列名，然后将列插入到相应的表中

import pyMysqLimport Jsonimport osimport secureUtilsmapPing_keys = Json.load(open("key_mapPing_db.Json","r"))db_keys = []  # Json中所有的keyfor k in mapPing_keys.values():    db_keys.append(k)conn = pyMysqL.connect(host='localhost',passwd='*****',db='db_name',charset='utf8')cursor = conn.cursor()table_main = "table_main"main_table_keys = []  # 主表的列名cursor.execute("show columns from " + table_main)for row in cursor.fetchall():    main_table_keys.append(row[0])staff_table_keys = []cursor.execute("show columns from table_second")for row in cursor.fetchall():    staff_table_keys.append(row[0])need_to_insert_keys = []for k in db_keys:    if k not in staff_table_keys and k not in main_table_keys and k not in need_to_insert_keys:        need_to_insert_keys.append(k)print("need to insert " + str(len(need_to_insert_keys)))print(need_to_insert_keys)for kn in need_to_insert_keys:    print("add key to db " + kn)    cursor.execute("alter table staff_table add " + kn +" text")conn.close()

将字段字符改变

这里将main_table_keys中的所有字段改为utf8

# change column character set to utf8for co in main_table_keys:    change_sql = "alter table " + table_main + " modify " + co + " text character set utf8"    print(change_sql)    cursor.execute(change_sql)

总结

以上是内存溢出为你收集整理的Python *** 作 MySQL全部内容，希望文章能够帮你解决Python *** 作 MySQL所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。