python 连接数据库操作

import mysql

#打开数据库连接(用户名，密码,数据库名)
db = mysql.connect("localhost","testuser","test123","testdb")

#使用cursor（）方法获取游标操作
cursor = db.cursor()

#使用exectue()执行sql语句操作
cursor.excetue("select name from tmp where sex = "男")

#使用fetchone（）方法获得一条数据
data = fetcone()
print data
db.close()

python 连接创建数据库表

db = connect("host","testuser","passwd","testdb")#连接数据库

cursor = db.cursor()#连接游标



sql = """crate table employee(

FIRST_NAME CHAR(20) NOT NULL,

LAST_NAME CHAR(20),

AGE IN,

SEX CHAR(1),

INCOME FLOAT)""" 

cursor.execute(sql)#执行SQL语句

db.close()# 关闭数据库连接

python 数据库插入操作

import mysql

db = mysql.connect("host","testuser","passwd","testdb"）#连接数据库

cursor = db.cursor()#获取游标

sql = """
insert into EMPLOYEE (FIRST_NAME,LAST_NAME,AGE,SEX,INCOME)
VALUES("MAC","Mohan",20,"M",20000)
"""
try:
　　cursor.exceute(sql)#提行SQL语句
　　db.commit()
except:
　　db.rollback()#发生错误回滚

db.close()#关闭数据库连接

数据库查询操作

import mysql
db = connect("host","testuser","passwd","testdb")
cursor = db.cursor()
#查询工资大于1000的员工信息
sql = "select * from employee where sal>%d"%(1000)
try:
    cursor.exceute(sql)#执行SQL语句

    results = cursor.fetchall#获取所有列表记录
    from row in results:
        fname = row[0]     
        lname = row[1]
        age = row[2]
        sex = row[2]
       ncome = rowp[4]

    print "fname = %s,lname = %s",age= %d,income = %d"
    %(fname,lname,age,sex,income)
except:
    print "Error:unable to facth data"
db.close()

数据库更新操作

import mysql

db = connect ("host","testuser","passwd","testdb")#数据库连接
surosr = db.cursor()#连接游标
sql = "UPDATE EMPLOYEE" SET AGE = AGE +1 WHERE SEX = "%C"%(M)

try:
　　sursor.execute(sql)#执行数据库操作
　　db.commit()
except:
　　db.rolloback()#发生错误时回滚
db.close()#关闭数据库连接

删除除操作

import MYSQLdb

db = connect("host","testuser","passwd","testdb")
cursor = db.cursor()#连接游标
sql = "DELETE FROM EMPLOYEE WHERE AGE >20"#删除年龄大于20岁的员工信息

try:
　　cursor.execute(sql)#执行SQL语句
　　db.commit()
except:
　　db.rollback()#发生错误时回滚

db.close()