》已经说明用Python怎么连接MySQL。下面来大致演示插入数据、批量插入数据、更新数据、查询数据的例子(新建D2DB.py):
import MySQLdb
try:
conn=MySQLdb.connect(host='127.0.0.1',user='root',passwd='root',port=3306)
cur=conn.cursor()
cur.execute('create database if not exists python')
conn.select_db('python')
cur.execute('create table PY_Test(id int,info varchar(40))')
value=[1,'hi xsysvermin']
cur.execute('insert into PY_Test values(%s,%s)',value)
values=[]
for i in range(20):
values.append((i,'hi xsysvermin'+str(i)))
cur.executemany('insert into PY_Test values(%s,%s)',values)
cur.execute('update PY_Test set info="I am xsysvermin" where id=3')
conn.commit()
cur.close()
conn.close()
except MySQLdb.Error,e:
print "MySQLdb Error %d: %s " %(e.args[0],e.args[1])
注意:一定要有conn.commit()提交事务,不然不能真正的插入数据。
运行D2DB.py即可看到结果,如下图:
接下来查询MySQL中运行D2DB.py的数据;新建PY文件(QueryDB.py):
import MySQLdb
try:
conn=MySQLdb.connect(host='127.0.0.1',user='root',passwd='mysql2014',port=3306)
cur=conn.cursor()
conn.select_db('python')
count=cur.execute('select * from PY_Test')
print 'there has %s rows recoed' %count
result=cur.fetchone()
print result
print 'ID:%s info %s' % result
results=cur.fetchmany(5)
for r in results:
print r
print '=='*10
cur.scroll(0,mode='absolute')
results=cur.fetchall()
for r in results:
print r[1]
conn.commit()
cur.close()
conn.close()
except MySQLdb.Error,e:
print 'MySQLdb Error %d: %s' % (e.args[0],e.args[1])
运行Query
DB.py即可看到结果,如下图: