||
1. 要想使Python可以操作MySQL数据库,首先需要安装MySQL-python包,在CentOS上可以使用一下命令来安装
yum install -y MySQL-python
2. 下面的程序创建了一个到mysql数据库的连接,然后执行了一个简单的查询,并打印查询结果
#!/usr/bin/python
import MySQLdb
#start connect mysql,先创建连接
conn = MySQLdb.connect(host = "127.0.0.1",user = "root",passwd = "***",db = "mysql")
cursor = conn.cursor()
#查询mysql数据的版本
cursor.execute("select version()")
row = cursor.fetchone()
print "mysql Version:",row[0]
#close connection
cursor.close()
conn.close ()
执行结果:
# python a
mysql Version: 5.5.8
3. 下面看一个数据库建表和插入操作
#!/usr/bin/python
import MySQLdb
#start connect mysql
conn = MySQLdb.connect(host = "127.0.0.1",user = "root",passwd = "***",db = "mysql")
cursor = conn.cursor()
cursor.execute("""create database test5""")
cursor.execute("""use test5 """)
cursor.execute("""create table test(id int,col1 varchar(40),col2 varchar(40),col3 varchar(40)) """)
cursor.execute("""insert into test(id,col1,col2,col3) values(1,'a','b','c'),(2,'aa','bb','cc'),(3,'aaa','bbb','ccc') """)
conn.commit()
cursor.close()
conn.close ()
执行结果:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| test1 |
| test2 |
| test3 |
| test4 |
| test5 |
+--------------------+
9 rows in set (0.00 sec)
3. 下面再来看看查询,查询通常有两种方式:
一种是使用cursor.fetchall()获取所有查询结果,然后再一行一行的迭代;
另一种每次通过cursor.fetchone()获取一条记录,直到获取的结果为空为止。
看一下下面的例子:
先看cursor.fetchall()的:
#!/usr/bin/python
import MySQLdb
#start connect mysql
conn = MySQLdb.connect(host = "127.0.0.1",user = "root",passwd = "***",db = "mysql")
cursor = conn.cursor()
cursor.execute("use test5")
cursor.execute("select * from test")
rows=cursor.fetchall()
for row in rows: print "%d,%s,%s,%s"% (row[0],row[1],row[2],row[3])
print "Number of rows returned:%d"% cursor.rowcount
cursor.close()
conn.close ()
执行结果:
# python c
1,a,b,c
2,aa,bb,cc
3,aaa,bbb,ccc
Number of rows returned:3
再看cursor.fetchone()的:
#!/usr/bin/python
import MySQLdb
#start connect mysql
conn = MySQLdb.connect(host = "127.0.0.1",user = "root",passwd = "Exia@OO",db = "mysql")
cursor = conn.cursor()
cursor.execute("use test5")
cursor.execute("select * from test")
while(True):
row=cursor.fetchone()
if row == None :
break
print "%d,%s,%s,%s"% (row[0],row[1],row[2],row[3])
print "Number of rows returned:%d"% cursor.rowcount
#close connection
cursor.close()
conn.close ()
执行结果:
# python d
1,a,b,c
2,aa,bb,cc
3,aaa,bbb,ccc
Number of rows returned:3
Archiver|手机版|感谢所有关心和支持过LinuxTone的朋友们 转载本站内容请注明原作者名及出处 ( 京ICP备08103151 ) |
GMT+8, 2021-2-27 03:24 , Processed in 0.008778 second(s), 9 queries , Apc On.
Powered by Discuz! X2 Licensed
© 2001-2011 Comsenz Inc.