python串連mysql資料庫

標籤：python串連mysql資料庫

1.系統必須安裝MySQL-python軟體，否則python沒有串連的模組(在Linux系統)

yum install MySQL-python

2.安裝mysql資料庫

yum install mysql-server mysql

[[email protected] ~]# /etc/init.d/mysqld restart

Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

[[email protected] ~]#

3.在mysql中建立資料庫和表

[[email protected] ~]# mysql -u root -p
Enter password:
mysql: Unknown OS character set ‘GB18030‘.
mysql: Switching to the default character set ‘latin1‘.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 474
Server version: 5.6.11 Source distribution

Copyright (c) 2000, 2013, 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> create database tong;               --建立資料庫

Query OK, 1 row affected (0.00 sec)

mysql> \u tong
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table t(a int);             --建立表
Query OK, 0 rows affected (0.06 sec)

mysql>
4.用python語言插入資料和查詢資料

vim 1.py

import os,sys
import MySQLdb
try:
        conn=MySQLdb.connect(host="localhost",user="root",passwd="archermind",db="tong")  --輸出資料庫的使用者名稱,密碼,資料庫名
except Exception,e:
        print(e)
        sys.exit()
cursor=conn.cursor()
sql1="insert into t values(1),(2)"    --要執行的sql語句
sql2="commit"                         --sql語句執行後必須提交事物
try:
        cursor.execute(sql1)          --執行sql語句
        cursor.execute(sql2)
except Exception,e:
        print(e)
sql3="select * from t"     --查詢資料
cursor.execute(sql3)       --執行查詢的語句
row=cursor.fetchall()
if row:
        for x in row:
                print(x[0])      --把值輸出到螢幕上
        cursor.close()
        conn.close()

5.執行python程式,測試是否成功

[[email protected] script]# python 8.py     --驗證成功
1
2

[[email protected] script]# mysql -u root -p tong
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 522
Server version: 5.6.11 Source distribution

Copyright (c) 2000, 2013, 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 t;            --資料庫裡也有資料
+------+
| a    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql>

 

本文出自 “一起走過的日子” 部落格，轉載請與作者聯絡！