9 与python2交互，,1.创建外键# 创建
9 与python2交互，,1.创建外键# 创建
1.创建外键
# 创建room表mysql> create table rooms(id int primary key not null,title varchar(10));Query OK, 0 rows affected (0.01 sec)#创建学生表mysql> create table stu( -> id int primary key auto_increment not null, -> name varchar(10), -> roomid int);#添加外键mysql> alter table stu add constraint stu_room foreign key(roomid) references rooms(id); #添加数据mysql> insert into stu values(0,‘郭靖‘,314);ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`py31`.`stu`, CONSTRAINT `stu_room` FOREIGN KEY (`roomid`) REFERENCES `rooms` (`id`))mysql> insert into rooms values(314,‘聚义堂‘);
2. python2安装引入模块
python@ubuntu:~$ sudo apt-get install python-mysql #包名错误正在读取软件包列表... 完成正在分析软件包的依赖关系树 正在读取状态信息... 完成 E: 无法定位软件包 python-mysql
#安装mysql模块python@ubuntu:~$ sudo apt-get install python-mysqldb
正在读取软件包列表... 完成正在分析软件包的依赖关系树 正在读取状态信息... 完成 python-mysqldb 已经是最新版 (1.3.7-1build2)。下列软件包是自动安装的并且现在不需要了： linux-headers-4.4.0-22 linux-headers-4.4.0-22-generic linux-image-4.4.0-22-generic linux-image-extra-4.4.0-22-generic使用‘sudo apt autoremove‘来卸载它(它们)。升级了 0 个软件包，新安装了 0 个软件包，要卸载 0 个软件包，有 395 个软件包未被升级。在文件中引入模块
import Mysqldb
3.交互类型
（1）Connection对象
用于建立与数据库的连接创建对象：调用connect()方法conn=connect(参数列表)参数host：连接的mysql主机，如果本机是‘localhost‘参数port：连接的mysql主机的端口，默认是3306参数db：数据库的名称参数user：连接的用户名参数password：连接的密码参数charset：通信采用的编码方式，默认是‘gb2312‘，要求与数据库创建时指定的编码一致，否则中文会乱码
对象的方法
close()关闭连接commit()事务，所以需要提交才会生效rollback()事务，放弃之前的操作cursor()返回Cursor对象，用于执行sql语句并获得结果（2）Cursor对象
执行sql语句创建对象：调用Connection对象的cursor()方法cursor1=conn.cursor()
对象的方法
close()关闭execute(operation [, parameters ])执行语句，返回受影响的行数fetchone()执行查询语句时，获取查询结果集的第一个行数据，返回一个元组next()执行查询语句时，获取当前行的下一行fetchall()执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回scroll(value[,mode])将行指针移动到某个位置mode表示移动的方式mode的默认值为relative，表示基于当前行移动到value，value为正则向下移动，value为负则向上移动mode的值为absolute，表示基于第一条数据的位置，第一条数据的位置为0对象的属性
rowcount只读属性，表示最近一次execute()执行后受影响的行数connection获得当前连接对象4.增删改查
（1）增加
# -*- coding :utf-8 -*-from MySQLdb import * #导入包try: conn = connect(host="localhost", port=3306, user="root", passwd="mysql", db="py31", charset="utf8") #Connection对象 cursor1 = conn.cursor() #Cursor对象 sql = ‘insert into students(name) values("alex")‘ #sql语句 cursor1.execute(sql) #执行 conn.commit() #提交 cursor1.close() conn.close() #关闭except Exception as e: print(e.message)| 13 | alex | | NULL | |+----+-----------+--------+---------------------+----------+
（2）修改数据
sql = ‘update students set name="jack" where id=10‘
（3）删除数据
sql = ‘delete from students where id=9‘
5.sql语句参数化
用户输入:a‘or 1=1 or‘分号会影响sql语句select * from students where name=@name
（1）参数化
# -*- coding:utf-8 -*-from MySQLdb import *try: conn = connect(host="localhost", port=3306, user="root", passwd="mysql", db="py31", charset="utf8") cursor1 = conn.cursor() name = raw_input("请输入名字:") p_name = [name] #sql = ‘insert into students(name) values(%s)‘%p_name #cursor1.execute() cursor1.execute(‘insert into students(name) values(%s)‘,p_name) conn.commit() cursor1.close() conn.close() print("----ok---")except Exception as e: print(e.message)| 15 | ‘lala‘haha | | NULL | |+----+------------+--------+---------------------+----------+
（2） 列表作为参数
# -*- coding:utf-8 -*-from MySQLdb import *try: name = raw_input("请输入名字:") conn = connect(host="localhost", port=3306, user="root", passwd="mysql", db="py31", charset="utf8") cursor1 = conn.cursor() sql = ‘insert into students(name) values(%s)‘ cursor1.execute(sql,[name]) conn.commit() cursor1.close() conn.close() print("----ok---")except Exception as e: print(e.message)6.查询
（1）查询一条学生信息
# -*- coding:utf-8 -*-from MySQLdb import *try: #name = raw_input("请输入名字:") conn = connect(host="localhost", port=3306, user="root", passwd="mysql", db="py31", charset="utf8") cursor1 = conn.cursor() #sql = ‘insert into students(name) values("alex")‘ #sql = ‘update students set name="jack" where id=10‘ #sql = ‘delete from students where id=9‘ #sql = ‘insert into students(name) values(%s)‘ #cursor1.execute(sql,[name]) sql = ‘select * from students where id=4‘ cursor1.execute(sql) result = cursor1.fetchone() print(result) cursor1.close() conn.close() print("----ok---")except Exception as e: print(e.message)(4L, u‘\u5c0f\u7c73‘, ‘\x01‘, None, ‘\x00‘)----ok---
（2）查询多行数据
# -*- coding:utf-8 -*-from MySQLdb import *try: #name = raw_input("请输入名字:") conn = connect(host="localhost", port=3306, user="root", passwd="mysql", db="py31", charset="utf8") cursor1 = conn.cursor() #sql = ‘insert into students(name) values("alex")‘ #sql = ‘update students set name="jack" where id=10‘ #sql = ‘delete from students where id=9‘ #sql = ‘insert into students(name) values(%s)‘ #cursor1.execute(sql,[name]) sql = ‘select * from students‘ cursor1.execute(sql) result = cursor1.fetchall() print(result) cursor1.close() conn.close() print("----ok---")except Exception as e: print(e.message)((1L, u‘\u5c0f\u90ed‘, ‘\x01‘, datetime.datetime(1999, 9, 9, 0, 0), ‘\x00‘), (2L, u‘\u817e\u65ed‘, ‘\x01‘, datetime.datetime(1990, 2, 2, 0, 0), ‘\x00‘), (3L, u‘\u7f51\u6613‘, ‘\x01‘, None, ‘\x00‘), (4L, u‘\u5c0f\u7c73‘, ‘\x01‘, None, ‘\x00‘), (6L, u‘\u9177\u72d7‘, ‘\x00‘, datetime.datetime(2017, 2, 13, 0, 0), ‘\x01‘), (7L, u‘QQ‘, ‘\x01‘, None, ‘\x00‘), (8L, u‘\u817e\u8baf\u4e91‘, ‘\x01‘, None, ‘\x00‘), (10L, u‘jack‘, ‘\x01‘, None, ‘\x00‘), (11L, u‘\u5fae\u535a‘, ‘\x01‘, None, ‘\x00‘), (12L, u‘\u5fae\u4fe1‘, ‘\x01‘, None, ‘\x00‘), (13L, u‘alex‘, ‘\x01‘, None, ‘\x00‘), (14L, u‘lalal‘, ‘\x01‘, None, ‘\x00‘), (15L, u"‘lala‘haha", ‘\x01‘, None, ‘\x00‘), (16L, u"‘‘tae", ‘\x01‘, None, ‘\x00‘))----ok---
(3) 格式化输出数据
# 执行SQL语句 cursor.execute(sql) # 获取所有记录列表 results = cursor.fetchall() for row in results: fname = row[0] lname = row[1] age = row[2] sex = row[3] income = row[4] # 打印结果 print "fname=%s,lname=%s,age=%d,sex=%s,income=%d" % (fname, lname, age, sex, income )
9 与python2交互
评论关闭