热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

Python连接MySQL并使用fetchall()方法过滤特殊字符

这篇文章主要介绍了Python连接MySQL的方法并讲解了如何使用fetchall()方法过滤特殊字符,示例环境为Ubuntu操作系统,需要的朋友可以参考下
来一个简单的例子,看Python如何操作数据库,相比Java的JDBC来说,确实非常简单,省去了很多复杂的重复工作,只关心数据的获取与操作。
准备工作
需要有相应的环境和模块:

  • Ubuntu 14.04 64bit
  • Python 2.7.6
  • MySQLdb

注意:Ubuntu 自带安装了Python,但是要使用Python连接数据库,还需要安装MySQLdb模块,安装方法也很简单:

sudo apt-get install MySQLdb

然后进入Python环境,import这个包,如果没有报错,则安装成功了:

python
Python 2.7.6 (default, Jun 22 2015, 17:58: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 study;
use study;
DROP TABLE IF EXISTS python_demo;
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():
  try:
    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
  except:
    print "Error:数据库连接错误"
    return None

def select_demo(conn, sql):
  try:
    cursor = conn.cursor()
    cursor.execute(sql)
    return cursor.fetchall()
  except:
    print "Error:数据库连接错误"
    return None

def update_demo():
  pass

def delete_demo():
  pass

def insert_demo():
  pass

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)
  pass

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):
  try:
    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)

  try:
    sql = 'select * from %s;'%(table_name)
    cursor.execute(sql)
    rows = cursor.fetchall()

    print '====字段未过滤查询结果===='
    for row in rows:
      print row

    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)
  pass

看看输出结果:

字段未过滤查询结果

(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,就这个不同导致了很多数据抽取不对.

推荐阅读
  • ubuntu用sqoop将数据从hive导入mysql时,命令: ... [详细]
  • 一、Hadoop来历Hadoop的思想来源于Google在做搜索引擎的时候出现一个很大的问题就是这么多网页我如何才能以最快的速度来搜索到,由于这个问题Google发明 ... [详细]
  • 学习SLAM的女生,很酷
    本文介绍了学习SLAM的女生的故事,她们选择SLAM作为研究方向,面临各种学习挑战,但坚持不懈,最终获得成功。文章鼓励未来想走科研道路的女生勇敢追求自己的梦想,同时提到了一位正在英国攻读硕士学位的女生与SLAM结缘的经历。 ... [详细]
  • 本文介绍了在Win10上安装WinPythonHadoop的详细步骤,包括安装Python环境、安装JDK8、安装pyspark、安装Hadoop和Spark、设置环境变量、下载winutils.exe等。同时提醒注意Hadoop版本与pyspark版本的一致性,并建议重启电脑以确保安装成功。 ... [详细]
  • 使用Ubuntu中的Python获取浏览器历史记录原文: ... [详细]
  • 31.项目部署
    目录1一些概念1.1项目部署1.2WSGI1.3uWSGI1.4Nginx2安装环境与迁移项目2.1项目内容2.2项目配置2.2.1DEBUG2.2.2STAT ... [详细]
  • 分享css中提升优先级属性!important的用法总结
    web前端|css教程css!importantweb前端-css教程本文分享css中提升优先级属性!important的用法总结微信门店展示源码,vscode如何管理站点,ubu ... [详细]
  • Postgresql备份和恢复的方法及命令行操作步骤
    本文介绍了使用Postgresql进行备份和恢复的方法及命令行操作步骤。通过使用pg_dump命令进行备份,pg_restore命令进行恢复,并设置-h localhost选项,可以完成数据的备份和恢复操作。此外,本文还提供了参考链接以获取更多详细信息。 ... [详细]
  • 本文介绍了在Web应用系统中,数据库性能是导致系统性能瓶颈最主要的原因之一,尤其是在大规模系统中,数据库集群已经成为必备的配置之一。文章详细介绍了主从数据库架构的好处和实验环境的搭建方法,包括主数据库的配置文件修改和设置需要同步的数据库等内容。MySQL的主从复制功能在国内外大型网站架构体系中被广泛采用,本文总结了作者在实际的Web项目中的实践经验。 ... [详细]
  • PHP函数实现分页含文本分页和数字分页【PHP】
    后端开发|php教程PHP,分页后端开发-php教程最近,在项目中要用到分页。分页功能是经常使用的一个功能,所以,对其以函数形式进行了封装。影视网源码带充值系统,vscode配置根 ... [详细]
  • 本文介绍了Oracle数据库中tnsnames.ora文件的作用和配置方法。tnsnames.ora文件在数据库启动过程中会被读取,用于解析LOCAL_LISTENER,并且与侦听无关。文章还提供了配置LOCAL_LISTENER和1522端口的示例,并展示了listener.ora文件的内容。 ... [详细]
  • 计算机存储系统的层次结构及其优势
    本文介绍了计算机存储系统的层次结构,包括高速缓存、主存储器和辅助存储器三个层次。通过分层存储数据可以提高程序的执行效率。计算机存储系统的层次结构将各种不同存储容量、存取速度和价格的存储器有机组合成整体,形成可寻址存储空间比主存储器空间大得多的存储整体。由于辅助存储器容量大、价格低,使得整体存储系统的平均价格降低。同时,高速缓存的存取速度可以和CPU的工作速度相匹配,进一步提高程序执行效率。 ... [详细]
  • mysql-cluster集群sql节点高可用keepalived的故障处理过程
    本文描述了mysql-cluster集群sql节点高可用keepalived的故障处理过程,包括故障发生时间、故障描述、故障分析等内容。根据keepalived的日志分析,发现bogus VRRP packet received on eth0 !!!等错误信息,进而导致vip地址失效,使得mysql-cluster的api无法访问。针对这个问题,本文提供了相应的解决方案。 ... [详细]
  • 众筹商城与传统商城的区别及php众筹网站的程序源码
    本文介绍了众筹商城与传统商城的区别,包括所售产品和玩法不同以及运营方式不同。同时还提到了php众筹网站的程序源码和方维众筹的安装和环境问题。 ... [详细]
  • MyBatis多表查询与动态SQL使用
    本文介绍了MyBatis多表查询与动态SQL的使用方法,包括一对一查询和一对多查询。同时还介绍了动态SQL的使用,包括if标签、trim标签、where标签、set标签和foreach标签的用法。文章还提供了相关的配置信息和示例代码。 ... [详细]
author-avatar
痴情小猪噜噜1907181048
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有