1.系统必须安装MySQL-python软件，否则python没有连接的模块(在Linux系统)
[root@node1 python]# yum install MySQL-python
2.安装mysql数据库
[root@node1 python]# yum install mysql-server mysql
[root@node1 python]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@node1 python]#
3.用python创建数据库和表,并插入数据
[root@node1 python]# vim mysql1.py
#!/bin/env python
#!-*- coding:UTF-8 -*-
import MySQLdb,exceptions
try:
conn=MySQLdb.connect(host='localhost',user='root',passwd='system',port=3306) #连接数据库
cur=conn.cursor() #打开数据库
cur.execute('create database if not exists tong') #创建数据库
cur.execute('use tong')
cur.execute('create table if not exists t (a int ,b int)') #创建表
cur.execute('insert into tii values(1,2),(3,4)') #插入数据
cur.execute('commit') #提交事物
cur.close()
conn.close()
except MySQLdb.Error,e:
print "MySQL Error %d: %s" %(e.args[0],e.args[1])
[root@node1 python]# ./mysql1.py
[root@node1 python]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 282
Server version: 5.6.27-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select * from tong.t; #查询数据
+------+------+
| a | b |
+------+------+
| 1 | 2 |
| 3 | 4 |
+------+------+
2 rows in set (0.00 sec)
mysql>
4.利用python读取数据(和上面数据有重复的)
#!/bin/env python
#!-*- coding:UTF-8 -*-
import MySQLdb,exceptions
try:
conn=MySQLdb.connect(host='localhost',user='root',passwd='system',port=3306)
cur=conn.cursor()
cur.execute('create database if not exists tong')
cur.execute('use tong')
cur.execute('create table if not exists t (a int ,b int)')
cur.execute('insert into t values(1,2),(3,4)')
cur.execute('commit')
cur.close()
conn.close()
except MySQLdb.Error,e:
print "MySQL Error %d: %s" %(e.args[0],e.args[1])
try:
conn=MySQLdb.connect(host='localhost',user='root',passwd='system',db='tong',port=3306)
cur=conn.cursor()
rows=cur.execute('select * from t')
print "there has %s rows record" %rows
result=cur.fetchone() #获取下一个查询结果集
print result
print "A字段: %s B字段：%s" %result
results1=cur.fetchmany(2) #获取指定的行数
for i,j in results1:
print "字段A的值: %s 字段B的值:%s" %(i,j)
results2=cur.fetchall() #获取全部行数
print "字段A所有值 字段B所有值"
for i,j in results2:
print "%-20s %-20s" %(i,j)
except MySQLdb.Error,e:
print "MySQL Error %d: %s" %(e.args[0],e.args[1])
[root@node1 python]# chmod 755 mysql1.py
[root@node1 python]# ./mysql1.py
查询t表中所有行:10
(1L, 2L)
A字段: 1 B字段：2
获取两行数据: 3 4
获取两行数据: 1 2
获取所有行 藜取所有行
3 4
1 2
3 4
1 2
3 4
1 2
3 4
[root@node1 python]#
