Python 操作 MySQL

發表於 2016-01-06   |   分類於 Python   |  

安装数据库驱动

1
sudo pip install MySQL-python

检查是否安装成功

1
2
~/python
>>> import MySQLdb

基本操作

数据库连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
# -*- coding:utf-8 -*-
import MySQLdb
conn= MySQLdb.connect(
        host='localhost',
        port = 3306,
        user='root',
        passwd='123456',
        db ='test',
        )
cur = conn.cursor()
#创建数据表
#cur.execute("create table student(id int ,name varchar(20),class varchar(30),age varchar(10))")
#插入一条数据
#cur.execute("insert into student values('2','Tom','3 year 2 class','9')")
#修改查询条件的数据
#cur.execute("update student set class='3 year 1 class' where name = 'Tom'")
#删除查询条件的数据
#cur.execute("delete from student where age='9'")
cur.close()
conn.commit()
conn.close()

插入数据

单条数据

1
2
3
#插入一条数据
sqli="insert into student values(%s,%s,%s,%s)"
cur.execute(sqli,('3','Huhu','2 year 1 class','7'))

多条数据

1
2
3
4
5
6
sqli="insert into student values(%s,%s,%s,%s)"
cur.executemany(sqli,[
    ('3','Tom','1 year 1 class','6'),
    ('3','Jack','2 year 1 class','7'),
    ('3','Yaheng','2 year 2 class','7'),
    ])

查询数据

1
2
3
4
5
6
7
8
9
10
11
12
13
>>> import MySQLdb
>>> conn = MySQLdb.connect(host='localhost',port = 3306,user='root',    passwd='123456',db ='test',)
>>> cur = conn.cursor()
>>> cur.execute("select * from student")
5L
>>> cur.fetchone()
(1L, 'Alen', '1 year 2 class', '6')
>>> cur.fetchone()
(3L, 'Huhu', '2 year 1 class', '7')
>>> cur.fetchone()
(3L, 'Tom', '1 year 1 class', '6')
...
>>>cur.scroll(0,'absolute')

获得表中的多条数据并打印

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#coding=utf-8
import MySQLdb
conn= MySQLdb.connect(
        host='localhost',
        port = 3306,
        user='root',
        passwd='123456',
        db ='test',
        )
cur = conn.cursor()
#获得表中有多少条数据
aa=cur.execute("select * from student")
print aa
#打印表中的多少数据
info = cur.fetchmany(aa)
for ii in info:
    print ii
cur.close()
conn.commit()
conn.close()

特殊符号处理

练习文本

1
2
3
4
5
6
7
8
9
10
11
12
13
WEIPEI559720
20080811
460228214
feng66532008
7404850554
0.0123456789
huang.512yang.
hanlei@19940403
shuidongwo'520
zxy/.,\';][
zxy/.,\\';][
fz62wangyong1983

注意空行，'与\等特殊符号的处理

已试验5540000行

处理脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
# -*- coding:utf-8 -*-
import MySQLdb
import re
# http://zhidao.baidu.com/link?url=i7cXwh-3I_JeQl0-zn4cPLT8oc7UMM199ECqc7En1vq7Uzf1_7wKIREY15bvlBdFLCBlbYLhdwwRPY-dyS8G-a
debug_mode = False
def washdata():
    # 连接数据库
    conn= MySQLdb.connect(
            host='localhost',
            port = 3306,
            user='root',
            passwd='toor',
            db ='passdict',
            )
    cur = conn.cursor()
    # 正则清理
    specific_symbol_1 = r"'"
    specific_symbol_2 = r'\\'
    replacer = re.compile(specific_symbol_1)
    finder = re.compile(specific_symbol_2)
    # filename = '一亿多QQ-Harry.txt.out'
    filename = 'test.txt.out.norep'
    line_number = 0
    # 打开文件
    with open(filename,'r') as reader:
        for line in reader:
            # 空行跳过
            if len(line) == 1:
                print 'null line'
                continue
            # '\' 处理
            if re.search(finder,line) is not None:
                if debug_mode:
                    print 'before:',line
                
                line = finder.sub(r"\\\\",line)
                
                if debug_mode:
                    print 'after:',line
            # "'"处理
            line = replacer.sub("\\'",line)
            # 处理换行符号
            line = cutline(line)
            # 确定行号
            line_number += 1
            
            if debug_mode:
                if line_number > 100:
                    break
                print [line]
            sql = "insert into password(id,password) values("+ str(line_number) +",'"+ line +"')"
            
            if debug_mode:
                print sql
            # 插入一条数据
            try:
                cur.execute(sql)
            except Exception, e:
                print "发生错误的sql是:"
                print sql
            else:
                pass
            finally:
                pass
    print '完成...'
    cur.close()
    conn.commit()
    conn.close()
# 处理换行符号
def cutline(line):
    new_line = ''
    for ch in line:
        if ch != '\n':
            new_line += ch
        else:
            break
    return new_line
#修改查询条件的数据
#cur.execute("update student set class='3 year 1 class' where name = 'Tom'")
#删除查询条件的数据
#cur.execute("delete from student where age='9'")
if __name__ == '__main__':
    washdata()

数据库目录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
java搜索
http://www.sharck2.org/article/1392544368.html
各种
http://pan.baidu.com/share/home?uk=3844799271
转换工具
http:/pan.baidu.com/s/1ntO4v4d 密码:64zn
SQLite 如家
http://pan.baidu.com/s/1qXskzSS 密码: 22a4
QQ群关系 圆通
http://pan.baidu.com/s/1hrkrbgw 密码：5y0w
SQL Server 如家汉庭
http://pan.baidu.com/s/1gdVzUk7 密码:qo71

参考资料

[1] python使用mysql数据库

#python #数据库
Veil 使用笔记
Linux 全目录全文搜索