Python 与 Mysql交互Python 与Mysql交互
#!/usr/bin/envpython
import MySQLdb
try:
conn = MySQLdb.connect(host='192.168.198.110',user='root',passwd='123456',port=3306,db='mysql')
cur=conn.cursor()
cur.execute('select user,host,password frommysql.user')
qur_result=cur.fetchall()
for record in qur_result:
print record
cur.close()
conn.close()
exceptMySQLdb.Error,e:
print 'MySQL Error Msg:',e
执行：
[root@node110mysql]# python conn_mysql.py
('root','localhost', '*6F8B38B6D5C4BCC76395A41E7DF1A97A1B3CBC9E')
('root','127.0.0.1', '*6F8B38B6D5C4BCC76395A41E7DF1A97A1B3CBC9E')
('dba','localhost', '*6F8B38B6D5C4BCC76395A41E7DF1A97A1B3CBC9E')
('oldgril','localhost', '*7EF5D46C50DD0629925987464C4631CB47D363DF')
('system','localhost', '*6F8B38B6D5C4BCC76395A41E7DF1A97A1B3CBC9E')
('test','10.0.0.%', '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29')
('rsc','localhost', '')
('root', '%','*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9')
('salt', '%','*36F75ABC6D500DFA6E905046FD8BE5E115812DD0')
PythonMysql交互:
#!/usr/bin/envpython
import MySQLdb
try:
conn=MySQLdb.connect(host='192.168.198.110',user='root',passwd='123456',port=3306)
cur=conn.cursor()
cur.execute('create database if not existspython')
conn.select_db('python')
cur.execute('create table test(id int,infovarchar(20))')
value=[1,'hi rollen']
cur.execute('insert into testvalues(%s,%s)',value)
values=[]
for i in range(20):
values.append((i,'hi rollen'+str(i)))
cur.executemany('insert into testvalues(%s,%s)',values)
cur.execute('update test set info="i amrollen" where id=3')
conn.commit()
cur.close()
conn.close()
exceptMySQLdb.Error,e:
print "Mysql Error %d:%s"%(e.args[0],e.args[1])
查看插入的数据：
[root@node110mysql]# cat create_table_select.py
#!/usr/bin/envpython
import MySQLdb
try:
conn =MySQLdb.connect(host='192.168.198.110',user='root',passwd='123456',port=3306,db='python')
cur=conn.cursor()
cur.execute('select * from test')
qur_result=cur.fetchall()
for record in qur_result:
print record
cur.close()
conn.close()
exceptMySQLdb.Error,e:
print 'MySQL Error Msg:',e
[root@node110mysql]# python create_table_select.py
(1L, 'hirollen')
(0L, 'hirollen0')
(1L, 'hirollen1')
(2L, 'hirollen2')
(3L, 'i amrollen')
(4L, 'hirollen4')
(5L, 'hirollen5')
(6L, 'hi rollen6')
(7L, 'hirollen7')
(8L, 'hirollen8')
(9L, 'hirollen9')
(10L, 'hirollen10')
(11L, 'hirollen11')
(12L, 'hirollen12')
(13L, 'hirollen13')
(14L, 'hirollen14')
(15L, 'hirollen15')
(16L, 'hirollen16')
(17L, 'hirollen17')
(18L, 'hirollen18')
(19L, 'hirollen19')
