Python连接MySQL并使用fetchall()方法过滤特殊字符
作者：Jacman
字体：[ ] 类型：转载 时间：
这篇文章主要介绍了Python连接MySQL的方法并讲解了如何使用fetchall()方法过滤特殊字符,示例环境为Ubuntu操作系统,需要的朋友可以参考下
来一个简单的例子，看Python如何操作数据库，相比Java的JDBC来说，确实非常简单，省去了很多复杂的重复工作，只关心数据的获取与操作。
需要有相应的环境和模块：
Ubuntu 14.04 64bit
Python 2.7.6
注意:Ubuntu 自带安装了Python，但是要使用Python连接数据库，还需要安装MySQLdb模块，安装方法也很简单：
sudo apt-get install MySQLdb
然后进入Python环境，import这个包，如果没有报错，则安装成功了：
Python 2.7.6 (default, Jun 22 :13)
[GCC 4.8.2] on linux2
Type "help", "copyright", "credits" or "license" for more information.
&&& import MySQLdb
Python标准的数据库接口的Python DB-API（包括Python操作MySQL）。大多数Python数据库接口坚持这个标准。不同的数据库也就需要不同额模块，由于我本机装的是MySQL，所以使用了MySQLdb模块，对不同的数据库而言，只需要更改底层实现了接口的模块，代码不需要改，这就是模块的作用。
Python数据库操作
首先我们需要一个测试表
建表语句：
CREATE DATABASE
DROP TABLE IF EXISTS python_
CREATE TABLE python_demo (
id int NOT NULL AUTO_INCREMENT COMMENT '主键，自增',
user_no int NOT NULL COMMENT '用户编号',
user_name VARBINARY(50) NOT NULL COMMENT '用户名',
password VARBINARY(50) NOT NULL COMMENT '用户密码',
remark VARBINARY(255) NOT NULL COMMENT '用户备注',
PRIMARY KEY (id,user_no)
)ENGINE =innodb DEFAULT CHARSET = utf8 COMMENT '用户测试表';
INSERT INTO python_demo(user_no, user_name, password, remark) VALUES
(1001,'张三01','admin','我是张三');
INSERT INTO python_demo(user_no, user_name, password, remark) VALUES
(1002,'张三02','admin','我是张三');
INSERT INTO python_demo(user_no, user_name, password, remark) VALUES
(1003,'张三03','admin','我是张三');
INSERT INTO python_demo(user_no, user_name, password, remark) VALUES
(1004,'张三04','admin','我是张三');
INSERT INTO python_demo(user_no, user_name, password, remark) VALUES
(1005,'张三05','admin','我是张三');
INSERT INTO python_demo(user_no, user_name, password, remark) VALUES
(1006,'张三06','admin','我是张三');
INSERT INTO python_demo(user_no, user_name, password, remark) VALUES
(1007,'张三07','admin','我是张三');
INSERT INTO python_demo(user_no, user_name, password, remark) VALUES
(1008,'张三08','admin','我是张三');
Python代码
# --coding=utf8--
import ConfigParser
import sys
import MySQLdb
def init_db():
conn = MySQLdb.connect(host=conf.get('Database', 'host'),
user=conf.get('Database', 'user'),
passwd=conf.get('Database', 'passwd'),
db=conf.get('Database', 'db'),
charset='utf8')
return conn
print "Error:数据库连接错误"
return None
def select_demo(conn, sql):
cursor = conn.cursor()
cursor.execute(sql)
return cursor.fetchall()
print "Error:数据库连接错误"
return None
def update_demo():
def delete_demo():
def insert_demo():
if __name__ == '__main__':
conf = ConfigParser.ConfigParser()
conf.read('mysql.conf')
conn = init_db()
sql = "select * from %s" % conf.get('Database', 'table')
data = select_demo(conn, sql)
fetchall()字段特殊字符过滤处理
最近在做数据仓库的迁移工作,之前数据仓库的数据都是用的shell脚本来抽取,后来换了python脚本.
但是在把数据抽取存放到hadoop时,出现了一个问题:
由于数据库字段很多,提前也不知道数据库字段会存储什么内容,hive建表是以\t\n做分隔,这就导致了一个问题,如果mysql字段内容里面本身含有\t\n,那么就会出现字段错位情况,并且很头疼的是mysql有100多个字段,也不知道哪个字段会出现这个问题.
shell脚本里的做法是在需要抽取的字段上用mysql的replace函数对字段进行替换,例如,假设mysql里的字段是column1 varchar(2000),那么很可能就会出现有特殊字符的情况,在查询的sql语句里加上
select replace(replace(replace(column1,'\r',''),'\n',''),'\t','')
之前一直是这么干的,但是这样写sql特别长,特别是有100多个字段,也不知道哪个有特殊字符,只要都加上.
所以在python中对字段不加处理,最终导致hive表字段对应出现偏差,所以在python里从mysql查询到的字段在写到文件之前需要对每个字段进行过滤处理
看个例子,我就以mysql测试为例,首先建一张测试表
CREATE TABLE `filter_fields` (
`field1` varchar(50) DEFAULT NULL,
`field2` varchar(50) DEFAULT NULL,
`field3` varchar(50) DEFAULT NULL,
`field4` varchar(50) DEFAULT NULL,
`field5` varchar(50) DEFAULT NULL,
`field6` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
有六个字段,都是varchar类型,插入新数据可以在里面插入特殊字符.简单插入条数据测试看看:
insert into filter_fields(field1,field2,field3,field4,field5,field6) VALUES
('test01','test02','test03','test04','test05','test06');
insert into filter_fields(field1,field2,field3,field4,field5,field6) VALUES
('test11\ntest11','test12\n\n','test13','test14','test15','test16');
insert into filter_fields(field1,field2,field3,field4,field5,field6) VALUES
('test21\ttest21','test22\ttest22\ttest22','test23\t\t\t','test4','test5','test6');
insert into filter_fields(field1,field2,field3,field4,field5,field6) VALUES
('test21\rest21','test22\r\rest22\r\rest22','test23\r\r\r','test4','test5','test6');
其中数据里插入的特殊字符,可能连在一起,也有不连在一起的.
python测试代码:
# coding=utf-8
import MySQLdb
import sys
db_host = '127.0.0.1'
# 数据库地址
db_port = 3306
# 数据库端口
db_user = 'root'
# mysql用户名
db_pwd = 'yourpassword' # mysql用户密码,换成你的密码
db_name = 'test'
# 数据库名
db_table = 'filter_fields' # 数据库表
# 过滤sql字段结果中的\t\n
def extract_data(table_name):
conn = MySQLdb.connect(host=db_host, port = db_port, user=db_user,
passwd = db_pwd, db = db_name, charset = "utf8")
cursor = conn.cursor()
except MySQLdb.Error, e:
print '数据库连接异常'
sys.exit(1)
sql = 'select * from %s;'%(table_name)
cursor.execute(sql)
rows = cursor.fetchall()
print '====字段未过滤查询结果===='
for row in rows:
print '====字段过滤之后结果===='
rows_list = []
for row in rows:
row_list = []
for column in row:
row_list.append(column.replace('\t', '').replace('\n', '').replace('\r', ''))
rows_list.append(row_list)
print rows_list[-1] # [-1]表示列表最后一个元素
return rows_list
except MySQLdb.Error, e:
print '执行sql语句失败'
cursor.close()
conn.close()
sys.exit(1)
if __name__ == '__main__':
print 'begin:'
rows = extract_data(db_table)
看看输出结果:
字段未过滤查询结果
(u'test01', u'test02', u'test03', u'test04', u'test05', u'test06')
(u'test11\ntest11', u'test12\n\n', u'test13', u'test14', u'test15', u'test16')
(u'test21\ttest21', u'test22\ttest22\ttest22', u'test23\t\t\t', u'test4', u'test5', u'test6')
(u'test21\rest21', u'test22\r\rest22\r\rest22', u'test23\r\r\r', u'test4', u'test5', u'test6')
字段过滤之后结果
[u'test01', u'test02', u'test03', u'test04', u'test05', u'test06']
[u'test11test11', u'test12', u'test13', u'test14', u'test15', u'test16']
[u'test21test21', u'test22test22test22', u'test23', u'test4', u'test5', u'test6']
[u'test21est21', u'test22est22est22', u'test23', u'test4', u'test5', u'test6']
可以看到,制表符,换行符,回车都被过滤了.
建议:最后说点题外话,不要小视\r,回车符.很多人以为回车符就是换行符,其实不是的,\r表示回车符,\n表示新行.之前代码里其实是过滤掉了\t\n的,但是抽取的数据还是不对,后来看了源码之后才发现,原来是没有过滤\r,就这个不同导致了很多数据抽取不对.
您可能感兴趣的文章:
大家感兴趣的内容
12345678910
最近更新的内容
常用在线小工具最近测试的发现有的插入数据库的信息会丢失，一直不清楚怎么回事；今天仔细看了下数据库才知道一些特殊字符是不能直接插入数据库的，比如单引号在数据库语句中表示引用其他字符，如果向数据库直接插入“Tom's cat”这样的字符串就会失败。
那么这种情况要怎么解决呢？查了一下资料一般有两种方案：一种是先对字符串进行转义，然后插入数据库，这是根本的解决方案；还有一种不那么“本质”但是相当漂亮的方法，先对字符串进行编码，然后取出时解码。
第一种方案一下子没有找到iOS版的代码，只有Java版的；继续尝试第二种方案，存入时没什么问题，取出时却是数据对应的字符串，虽然有办法转化，但是感觉还是太麻烦，想想还是自己写第一种方案的代码吧。
代码很简单，参考了网上的转化方式，但是确实可以直接插入数据库了：
&span style=&color:#006600;background-color: rgb(255, 255, 255);&&+ (NSString *)sqlStringFromString:(NSString *)string
NSString *sqlT
if (string.length&0)
sqlText = [string stringByReplacingOccurrencesOfString:@&'& withString:@&''&];
sqlText = [sqlText stringByReplacingOccurrencesOfString:@&/& withString:@&//&];
sqlText = [sqlText stringByReplacingOccurrencesOfString:@&[& withString:@&/[&];
sqlText = [sqlText stringByReplacingOccurrencesOfString:@&]& withString:@&/]&];
sqlText = [sqlText stringByReplacingOccurrencesOfString:@&%& withString:@&/%&];
sqlText = [sqlText stringByReplacingOccurrencesOfString:@&&& withString:@&/&&];
sqlText = [sqlText stringByReplacingOccurrencesOfString:@&_& withString:@&/_&];
sqlText = [sqlText stringByReplacingOccurrencesOfString:@&(& withString:@&/(&];
sqlText = [sqlText stringByReplacingOccurrencesOfString:@&)& withString:@&/)&];
return sqlT
}&/span&插入后直接取出即可。
本文已收录于以下专栏：
相关文章推荐
public class CodeFilter {  
    /** 
     * to db 
Oracle数据有个字段是存储url的，内容有特殊字符&，如果直接想执行查询或插入操作的话有问题，如下：
在plsql里边执行:
update userinfo set pageurl='test...
1、oracle 特殊字符 转义  关键词： oracle    转义                             &...
特殊字符数据库的存取问题第一种方法就是replace   例如在入库的时候可以把(& &)换成(&nbsp:) 把(/r/n)换成()这样的话在自动帮定的时候就可以在数据控件中显示换行和空格了，但是要...
由于特殊字符需要四个字节才能保存，而mysql5.5.3以下的版本中的utf8并不支持四字节的存储，（mysql5.5.3以上才有utf8mb4类型支持存储），此时，如果不能升级数据库，又不考虑过滤掉...
方法一：转义字符
使用单引号作为转义字符，即连续使用两个单引号。
select * from jq_jjjl where bt like '%女子''%'
上述代码会匹配jq_jjjl表中所有bt字...
Oracle中如何插入特殊字符：& 和 ' (多种解决方案)
今天在导入一批数据到Oracle时，碰到了一个问题：Toad提示要给一个自定义变量AMP赋值，一开始我很纳闷，数据是一系列的Inse...
今天在导入一批数据到Oracle时，碰到了一个问题：Toad提示要给一个自定义变量AMP赋值，一开始我很纳闷，数据是一系列的Insert语句，怎么会有自定义变量呢？后来搜索了一下关键字AMP发现，原来...
Oracle中插入特殊字符：&和'的三种方法
他的最新文章
讲师：刘文志
讲师：陈伟
您举报文章：
举报原因：
原文地址：
原因补充：
(最多只允许输入30个字)2013年9月 其他开发语言大版内专家分月排行榜第二2013年8月 其他开发语言大版内专家分月排行榜第二2013年7月 其他开发语言大版内专家分月排行榜第二2011年4月 PHP大版内专家分月排行榜第二
2013年10月 PHP大版内专家分月排行榜第三2013年5月 PHP大版内专家分月排行榜第三2013年4月 其他开发语言大版内专家分月排行榜第三2013年4月 PHP大版内专家分月排行榜第三2013年3月 其他开发语言大版内专家分月排行榜第三2011年5月 PHP大版内专家分月排行榜第三
2013年9月 其他开发语言大版内专家分月排行榜第二2013年8月 其他开发语言大版内专家分月排行榜第二2013年7月 其他开发语言大版内专家分月排行榜第二2011年4月 PHP大版内专家分月排行榜第二
2013年10月 PHP大版内专家分月排行榜第三2013年5月 PHP大版内专家分月排行榜第三2013年4月 其他开发语言大版内专家分月排行榜第三2013年4月 PHP大版内专家分月排行榜第三2013年3月 其他开发语言大版内专家分月排行榜第三2011年5月 PHP大版内专家分月排行榜第三
2013年9月 其他开发语言大版内专家分月排行榜第二2013年8月 其他开发语言大版内专家分月排行榜第二2013年7月 其他开发语言大版内专家分月排行榜第二2011年4月 PHP大版内专家分月排行榜第二
2013年10月 PHP大版内专家分月排行榜第三2013年5月 PHP大版内专家分月排行榜第三2013年4月 其他开发语言大版内专家分月排行榜第三2013年4月 PHP大版内专家分月排行榜第三2013年3月 其他开发语言大版内专家分月排行榜第三2011年5月 PHP大版内专家分月排行榜第三
2013年9月 其他开发语言大版内专家分月排行榜第二2013年8月 其他开发语言大版内专家分月排行榜第二2013年7月 其他开发语言大版内专家分月排行榜第二2011年4月 PHP大版内专家分月排行榜第二
2013年10月 PHP大版内专家分月排行榜第三2013年5月 PHP大版内专家分月排行榜第三2013年4月 其他开发语言大版内专家分月排行榜第三2013年4月 PHP大版内专家分月排行榜第三2013年3月 其他开发语言大版内专家分月排行榜第三2011年5月 PHP大版内专家分月排行榜第三
2013年9月 其他开发语言大版内专家分月排行榜第二2013年8月 其他开发语言大版内专家分月排行榜第二2013年7月 其他开发语言大版内专家分月排行榜第二2011年4月 PHP大版内专家分月排行榜第二
2013年10月 PHP大版内专家分月排行榜第三2013年5月 PHP大版内专家分月排行榜第三2013年4月 其他开发语言大版内专家分月排行榜第三2013年4月 PHP大版内专家分月排行榜第三2013年3月 其他开发语言大版内专家分月排行榜第三2011年5月 PHP大版内专家分月排行榜第三
本帖子已过去太久远了，不再提供回复功能。python2.7如何读取txt每行特殊字符后数据？ - 知乎3被浏览1115分享邀请回答1添加评论分享收藏感谢收起