python操作mysql数据库

MySQLdb

MySQLdb 是用于Python链接Mysql数据库的接口，它实现了 Python 数据库 API 规范 V2.0，基于 MySQL C API 上建立的。

查看数据

import MySQLdb

conn = MySQLdb.connect(host = '127.0.0.1',user = 'root',passwd = 'redhat',db = 'test') ##或者host=localhost

cur = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)
recont = cur.execute('select * from new_table')

data = cur.fetchall()
cur.close()
conn.close()
print recont
print data

1
({'qq': 1L, 'username': 100L, '12': 0L},)

增添数据

import MySQLdb

conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='dd',db='python')
cur = conn.cursor()
sql = 'insert into usermg(id,name,address) values(%s,%s,%s)'
params = ('1','dd','usa')
recount = cur.execute(sql,params)
conn.commit()

cur.close()
conn.close()
print recount

删除操作

import MySQLdb

conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='dd',db='python')
cur = conn.cursor()
sql = 'delete from usermg where id = %s'
params = (1,)
recount = cur.execute(sql,params)
conn.commit()

cur.close()
conn.close()
print recount

插入多条数据

import MySQLdb

conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='dd',db='python')

cur = conn.cursor()

# sql = 'update usermg set name = %s where id = %s'
# params = ('uu','1',)
li = [
    ('2','hahaha','wwww'),
    ('3','lalala','qqqq'),
]
recount = cur.executemany('insert into usermg(id,name,address) values(%s,%s,%s)',li)
conn.commit()
cur.close()
conn.close()
print recount

修改数据库

conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='dd',db='python')
cur = conn.cursor()
sql = 'update usermg set name = %s where id = %s'
params = ('uu','1',)
recount = cur.execute(sql,params)
conn.commit()
cur.close()
conn.close()
print recount

提交和回滚 在数据库里交事务操作

import MySQLdb
conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='dd',db='python')
cur = conn.cursor()

sql = 'update money set money = %s where id = 1'
params = ('0',)
recount = cur.execute(sql,params)

sql = 'update money sett money = %s where id = 2'
param = ('100',)
recount = cur.execute(sql,param)
conn.commit()