流程：1.数据库创建
2.python依赖库安装与连接
3.图片坐标定位与文字填入
4.数据库文字回填
一、数据库创建
使用Navicat来创建库和表。如下图，为Information_DS建立4个表
终端连接mysql：mysql -u root -proot （用户名、密码，其中-p后面不接空格）
Q&A：
1.输入字符时，提示Incorrect string value: '\xE4\xBC\x9A\xE5\x91\x98' for column 'recipient' at row 1
解答：修改字段字符集为utf 16
2.外键约束设置
解答：其他表设置外键，删除时“set null”、更新时“Restrict”。对应主键的数据类型、位数、名称都必须一致
二、python依赖库安装与连接
1）安装
终端执行如下4条命令：
#shell终端
〇 xcode-select --install ①ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)" ②brew unlink mysql-connector-c ③brew install mysql
Q&A：
1.fatal error :'my_config.h' file not found
解答：1：安装的mysql库没有加入环境变量，需要找到安装的mysql存放在哪个目录。终端窗口执行:
#shell终端 find / -name mysql_config #查找到.h文件路径 vi ~/.bash_profile export PATH=$PATH:.h文件路径 source ~/.bash_profile
2.MySQL不是单独安装的，是安装的一个MAMP集成开发环境包，在MAMP中PHP和MySQL的头文件都是没有的，所以会报出这个错误。我们有两种解决方式，一种是重新编译MySQL，一种是单独安装mysq-connector-c，因为开发环境包已经自带了MySQL了，我们就不再编译安装了，单独安装就ok。
Mac执行以下语句即可：brew install mysql-connector-c，再次执行pip install MySQL-python，出现报错：File "setup_posix.py",line 8,in dequote
解答：https://www.jianshu.com/p/da9dd5dd4bd2
2）连接
python中数据库连接，输出单个文字
1 try: 2 conn=pymysql.connect(host='localhost',user='root', password='root',database='Information_DS',charset='utf8') 3 sql="select * from People" 4 cur=conn.cursor() 5 cur.execute(sql) 6 data=cur.fetchall() 7 conn.close() 8 print data[0][1] 9 except Exception,e: 10 print e
Q&A：
1.输出的字符显示\xe7等
解答：可能1：数据库编码需要改成utf-8，
1) Navicat中，选择“设计表”-“选项”，修改默认字符集“utf8”,默认排序规则“utf8_general_ci”，字段页也需要修改对应的字符集。
2)终端中，查看和修改字符集如下：
#shell终端 mysql -u root -proot
use Information_DS;
show full columns from 表名; alter table 表名 convert to character set 字符集;
可能2：不支持中文，需要单个输出才能显示中文如：people[0][0]
三、图片坐标定位与文字填入
1）身份证ps掉关键字段
2）查询字段坐标位置
1 from PIL import Image 2 import matplotlib.pyplot as plt 3 #显示图片具体位置坐标 4 #正面（110,70）姓名 （110,112）性别 (245,112)民族 (110,160)年 (211,160)月 (272,160)日 (110,200)住址 (197,320)证号 5 #反面 （241，280）机关 （241，327）有效期限 6 img = Image.open('/Users/funny/Downloads/back.jpg') 7 8 plt.figure("Image") # 图像窗口名称 9 plt.imshow(img) 10 plt.axis('on') # 关掉坐标轴为 off 11 plt.show()
3）图片上增加文字
网上下载.ttf格式的字体，双击安装，默认Mac OS文字库路径：/System/Library/Fonts/
1 im=Image.open('/Users/funny/Downloads/front.jpg') 2 draw=ImageDraw.Draw(im) 3 newfont=ImageFont.truetype('/Users/funny/Library/Fonts/微软vista黑体.ttf',20) 4 draw.text((110,70),'fun',(0,0,0),font=newfont) 5 draw.text((110,112),'girl',(0,0,0),font=newfont) 6 draw.text((245,112),'a',(0,0,0),font=newfont) 7 draw.text((110,160),'1990',(0,0,0),font=newfont) 8 draw.text((211,160),'01',(0,0,0),font=newfont) 9 draw.text((272,160),'22',(0,0,0),font=newfont) 10 draw.text((110,200),'guangdongshenzhen',(0,0,0),font=newfont) 11 draw.text((197,320),'44522119900122',(0,0,0),font=newfont) 12 im.show() 13 im.save('/Users/funny/Downloads/1.jpg')
显示效果：
如果是中文字，需要修改成
draw.text((110,70),u'程程',(0,0,0),font=newfont)
四、数据库文字回填
#!/usr/bin/python # -*- coding: UTF-8 -*- import os from PIL import Image,ImageFont,ImageDraw import pymysql import sys import datetime import cv2 reload(sys) sys.setdefaultencoding('utf8') def DrawID(): conn = pymysql.connect(host='localhost', user='root', password='root', database='Information_DS', charset='utf8') sql = "select People.idCard,name,sex,ethnicity,birth,idAddress,authority,beginDate,endDate from People,IDcard where People.idCard =IDcard.idCard" cur = conn.cursor() cur.execute(sql) data = cur.fetchall() conn.close() for row in data: idCard = row[0] name = row[1] sex = row[2] ethnicity = row[3] birth = row[4] birth_Y = str(birth)[0:4] birth_M = str(birth)[5:7] birth_D = str(birth)[8:10] idAddress = row[5] Add_front = idAddress[:15] Add_back = "\n"+idAddress[15:] authority = row[6] beginDate = row[7] begin_Y = str(beginDate)[0:4] begin_M = str(beginDate)[5:7] begin_D = str(beginDate)[8:10] benginDateformat = begin_Y + "." + begin_M + "." + begin_D endDate = row[8] end_Y = str(endDate)[0:4] end_M = str(endDate)[5:7] end_D = str(endDate)[8:10] endDateformat = end_Y + "." + end_M + "." + end_D expireDate = benginDateformat + '-' + endDateformat im=Image.open('/Users/funny/Downloads/front.jpg') newfont = ImageFont.truetype('/Users/funny/Library/Fonts/微软vista黑体.ttf', 20) draw=ImageDraw.Draw(im) draw.text((110,70),name,(0,0,0),font=newfont) draw.text((110,112),sex,(0,0,0),font=newfont) draw.text((245,112),ethnicity,(0,0,0),font=newfont) draw.text((110,160),birth_Y,(0,0,0),font=newfont) draw.text((211,160),birth_M,(0,0,0),font=newfont) draw.text((272,160),birth_D,(0,0,0),font=newfont) draw.text((110,200),Add_front,(0,0,0),font=newfont) draw.text((110,205),Add_back, (0, 0, 0), font=newfont) draw.text((197,320),idCard,(0,0,0),font=newfont) #im.show() im.save('/Users/funny/Downloads/身份证/'+name+'_正面.jpg') im2=Image.open('/Users/funny/Downloads/back.jpg') draw=ImageDraw.Draw(im2) newfont=ImageFont.truetype('/Users/funny/Library/Fonts/微软vista黑体.ttf',20) draw.text((241,268),authority,(0,0,0),font=newfont) draw.text((241,315),expireDate,(0,0,0),font=newfont) #im2.show() im2.save('/Users/funny/Downloads/身份证/'+name+'_反面.jpg') if __name__=="__main__": DrawID()
