python操作MySQL
时间：2021-07-01 10:21:17
帮助过：26人阅读
coding=utf-8
import MySQLdb
conn = MySQLdb.connect(host=
‘localhost‘,user=
‘root‘,passwd=
‘123456‘,charset=
‘utf8‘)
cursor =
conn.cursor()
try:
#创建数据库
DB_NAME =
‘test‘
cursor.execute(‘DROP DATABASE IF EXISTS %s‘ %
DB_NAME)
cursor.execute(‘CREATE DATABASE IF NOT EXISTS %s‘ %
DB_NAME)
conn.select_db(DB_NAME)
#创建表
TABLE_NAME =
‘t_user‘
cursor.execute(‘CREATE TABLE %s(id int primary key,name varchar(30))‘ %
TABLE_NAME)
#插入单条数据
value = [1,
‘alexzhou1‘]
cursor.execute(‘INSERT INTO t_user values(%s,%s)‘,value)
#批量插入数据
values =
[]
for i
in range(2,10
):
values.append((i,‘alexzhou%s‘ %
(str(i))))
cursor.executemany(‘INSERT INTO t_user values(%s,%s)‘,values)
#查询记录数量
count = cursor.execute(
‘SELECT * FROM %s‘ %
TABLE_NAME)
print ‘total records: %d‘,count
#查询一条记录
print ‘fetch one record:‘
result =
cursor.fetchone()
print result
print ‘id: %s,name: %s‘ %(result[0],result[1
])
#查询多条记录
print ‘fetch five record:‘
results = cursor.fetchmany(5
)
for r
in results:
print r
#查询所有记录
#重置游标位置，偏移量:大于0向后移动;小于0向前移动，mode默认是relative
#relative:表示从当前所在的行开始移动,absolute:表示从第一行开始移动
cursor.scroll(0,mode=
‘absolute‘)
results =
cursor.fetchall()
for r
in results:
print r
cursor.scroll(-2
)
results =
cursor.fetchall()
for r
in results:
print r
#更新记录
cursor.execute(
‘UPDATE %s SET name = "%s" WHERE id = %s‘ %(TABLE_NAME,
‘zhoujianghai‘,1
))
#删除记录
cursor.execute(
‘DELETE FROM %s WHERE id = %s‘ %(TABLE_NAME,2
))
#必须提交，否则不会插入数据
conn.commit()
except:
import traceback
traceback.print_exc()
conn.rollback()
finally:
cursor.close()
conn.close()
python操作MySQL
标签：mode value 开始 rac lex sts use utf8 var
