python操作mysql实例

时间：2023-03-08 22:09:27
#coding=utf-8

import MySQLdb

# 打开数据库连接

db = MySQLdb.connect(host='localhost',user='root',passwd='',db ='company')

# 使用cursor()方法获取操作游标

cursor = db.cursor()

# 使用execute方法执行SQL语句

cursor.execute("SELECT VERSION()")

# 使用 fetchone() 方法获取一条数据库

data = cursor.fetchone()

print "Database version : %s" %data

# 这个方法用来打印整张表的信息

def view_table(tablename):

    cursor.execute("SELECT * FROM %s" % (tablename))

    results = cursor.fetchall()

    for row in results:

        i = 0

        while i < len(row):

            print row[i],

            i+=1

        print '\n'

# 创建数据表SQL语句

cursor.execute("DROP TABLE IF EXISTS employee")

table = """CREATE TABLE employee (

first_name char(20) NOT NULL,

last_name char(20),

age int,

sex char(1),

income float

)"""

cursor.execute(table)

cursor.execute("DESCRIBE employee")

#print cursor.fetchall()

#返回的是一个元组

for tup in cursor.fetchall():

    print tup

sql_1 = '''INSERT INTO employee (FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)

                        VALUES ('Micheal', 'Scofield', 21, 'M', 2000)'''

sql_2 = '''INSERT INTO employee (FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)

                        VALUES ('John', 'Abruzzi', 31, 'F', 3500)'''

sql_3 = '''INSERT INTO employee (FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)

                        VALUES ('Alex', 'Mahone', 35, 'M', 2800)'''

sql_4 = '''INSERT INTO employee (FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)

                        VALUES ('Paul', 'Keleman', 28, 'M', 3700)'''

try:

    cursor.execute(sql_1)

    cursor.execute(sql_2)

    cursor.execute(sql_3)

    cursor.execute(sql_4)

    # 提交到数据库执行

    db.commit()

except:

    #如果上面的语句执行出错就回滚

    db.rollback()

view_table('employee')

#cursor.execute("SELECT * FROM employee")

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

#    print row[0],row[1],row[2],row[3],row[4]

sql_5 = """UPDATE employee SET age = age+1 WHERE sex = '%c'""" % ('M')

cursor.execute(sql_5)

db.commit()

view_table('employee')

# 关闭数据库连接

db.close()

相关文章