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','我是张三');
Nach dem Login kopieren
# --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
Nach dem Login kopieren
fetchall()字段特殊字符过滤处理 最近在做数据仓库的迁移工作,之前数据仓库的数据都是用的shell脚本来抽取,后来换了python脚本.
select replace(replace(replace(column1,'\r',''),'\n',''),'\t','')
Nach dem Login kopieren
之前一直是这么干的,但是这样写sql特别长,特别是有100多个字段,也不知道哪个有特殊字符,只要都加上.
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;
Nach dem Login kopieren
有六个字段,都是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');
Nach dem Login kopieren
其中数据里插入的特殊字符,可能连在一起,也有不连在一起的.
# 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
Nach dem Login kopieren
看看输出结果:
字段未过滤查询结果
(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')
Nach dem Login kopieren
字段过滤之后结果
[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']
Nach dem Login kopieren
可以看到,制表符,换行符,回车都被过滤了.
Erklärung dieser Website
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Neueste Artikel des Autors
2024-09-20 15:51:33
2024-09-20 15:47:32
2024-09-20 15:45:32
2024-09-20 15:39:32
2024-09-20 15:38:32
2024-09-20 15:27:32
2024-09-20 15:26:32
2024-09-20 15:12:32
2024-09-20 14:53:01
2024-09-20 14:51:01
