python练习21 -- pysql模块

练习：将一个英英字典存入到一个数据库中

英英字典下载地址：英英文本字典，txt文本-数据集文档类资源-CSDN下载

1） 在mysql命令行客户端，创建一个名为dict的数据库，然后再dict中创建一个名为dictionary数据表：

mysql> create database dict;              /* 创建数据库 */
Query OK, 1 row affected (0.01 sec)

mysql> use dict;                          /* 使用dict数据库 */
Database changed
mysql> create table dictionary(           /* 创建数据表dictionary */
    -> id int primary key auto_increment,
    -> word varchar(50) not null,
    -> meaning text)engine=innodb charset=utf8;
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> show tables;
+----------------+
| Tables_in_dict |
+----------------+
| dictionary     |
+----------------+
1 row in set (0.00 sec)

mysql> desc dictionary;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int         | NO   | PRI | NULL    | auto_increment |
| word    | varchar(50) | NO   |     | NULL    |                |
| meaning | text        | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

2 ) 编写将dict.txt中数据插入到数据库中的insert_word脚本：

import pymysql
import time

fp = open("dict.txt", "r")

line = fp.readline()
i = 1
# 连接数据库
db = pymysql.connect(host='192.168.0.10',
                     port=3306,
                     user='dbuser',
                     password='@abcde12345',
                     database='dict',
                     charset="utf8mb4")

# 获取游标(操作数据库， 执行sql语句)
cur = db.cursor()

sql = 'insert into dictionary (word, meaning) values ("%s", "%s");'

# 循环插入数据
while line:
    i += 1
    line = line.strip('\n')
    line = line.split(' ', 1)
    word = line[0]
    meaning = line[1].strip(' ')
    meaning.replace('"', "'")

    sqlstring = sql % (word, meaning)
    print(sqlstring)
    cur.execute(sqlstring)

    if i > 100:  
        db.commit()      # 每一百个数据，提交一次
        i = 0
        time.sleep(0.5)
    line = fp.readline()

db.commit()
db.close()
fp.close()
cur.close()
db.close()

3） 执行以上python脚本，进行数据插入后检测数据库中的记录数目：

mysql> select count(*) from dictionary;     # 查看数据表中总的记录数
+----------+
| count(*) |
+----------+
|    19658 |
+----------+
1 row in set (0.10 sec)

mysql> select * from dictionary limit 10;   # 查看前10条数据
+----+-------------+--------------------------------------------------------------------------------------------------------------------------------+
| id | word        | meaning                                                                                                                        |
+----+-------------+--------------------------------------------------------------------------------------------------------------------------------+
|  1 | a           | indef art one                                                                                                                  |
|  2 | abacus      | n.frame with beads that slide along parallel rods, used for teaching numbers to children, and (in some countries) for counting |
|  3 | abandon     | v.  go away from (a person or thing or place) not intending to return; forsake; desert                                         |
|  4 | abandonment | n.  abandoning                                                                                                                 |
|  5 | abase       | v. ~ oneself/sb lower oneself/sb in dignity; degrade oneself/sb ;                                                              |
|  6 | abash       | to destroy the self-possession or self-confidence of:disconcert                                                                |
|  7 | abashed     | adj. ~ embarrassed; ashamed                                                                                                    |
|  8 | abate       | v. make or become less                                                                                                         |
|  9 | abattoir    | n. = slaughterhouse (slaughter)                                                                                                |
| 10 | abbess      | n. woman who is head of a convent or nunnery                                                                                   |
+----+-------------+--------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)