Python连接Mysql

来源：互联网发布：欧洲7日游要多少钱知乎编辑：程序博客网时间：2024/05/18 00:12

一.安装MySQL-python模块报错

在Ubuntu16.04上给python2安装扩展

Command "python setup.py egg_info" failed with error code 1 in /tmp/pip-build-3ir0He/MySQL-python/

解决方法

#Ubuntusudo apt-get install libmysqlclient-dev#centosyum install python-devel mysql-devel

二.连接数据库

#!/usr/bin/python#-*- coding:utf-8 -*-import MySQLdb#1.打开数据库db = MySQLdb.connect("localhost",'root','root','test')#2.使用cursor()方法获取操作游标cursor = db.cursor()#3.使用execute("select version()")cursor.execute("select VERSION()")#4.使用fetchone()方法获取一条数据data = cursor.fetchone()print "Database version : %s" % data#5.关闭数据库连接db.close()

三.插入操作

#!/usr/bin/python#-*- coding:utf-8 -*-import MySQLdb#1.打开数据库db = MySQLdb.connect("localhost",'root','root','test')#2.使用cursor()方法获取操作游标cursor = db.cursor()#3.如果数据表已经存在使用execute()方法删除表cursor.execute("drop table if exists employee")#4.创建数据表SQL语句sql = """create table employee(    first_name char(20) not null,    last_name char(20),    age int,    sex char(1),    income float)"""cursor.execute(sql)#5.关闭数据库db.close()

四.更新操作

#!/usr/bin/python#-*- coding: utf-8 -*-import MySQLdb#1.连接数据库db = MySQLdb.connect("localhost",'root','root','test')#2.使用cursor()方法获取游标cursor = db.cursor()#3.准备SQLsql = """update employee set age=8 where id=1"""try:    #执行SQL语句    cursor.execute(sql)    db.commit()except:    db.rollback()#关闭数据连接db.close()

五.查询操作

5.1返回的结果是元组类型(默认)

#返回结果<type 'tuple'>(u'first_name1', u'last_name2', 8L, u'1', 1.23, 1L)

这里写图片描述

#!/usr/bin/python#-*- coding:utf-8 -*-import MySQLdb.cursorsdb = MySQLdb.connect(host = "localhost",            user='root',            passwd='root',            db ='test',            port=3306,            charset="utf8",            cursorclass = MySQLdb.cursors.DictCursor)cursor = db.cursor()sql = """select * from employee"""try:    #执行SQL语句    cursor.execute(sql)    #获取所有记录列表    results = cursor.fetchall()    for row in results:        print type(row)        print rowexcept:    print "Error"db.close()

5.2返回的结果是字典类型

这里写图片描述

#返回结果<type 'dict'>{'first_name': u'first_name1', 'last_name': u'last_name2', 'age': 8L, 'sex': u'1', 'income': 1.23, 'id': 1L}

执行代码

#!/usr/bin/python#-*- coding:utf-8 -*-import MySQLdb.cursorsdb = MySQLdb.connect(host = "localhost",            user='root',            passwd='root',            db ='test',            port=3306,            charset="utf8",            cursorclass = MySQLdb.cursors.DictCursor)cursor = db.cursor()sql = """select * from employee"""try:    #执行SQL语句    cursor.execute(sql)    #获取所有记录列表    results = cursor.fetchall()    for row in results:        print type(row)        print rowexcept:    print "Error"db.close()

阅读全文

0 0