流程: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
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 exceptException,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 columnsfrom表名;
alter table 表名 convert to character set 字符集;
可能2:不支持中文,需要单个输出才能显示中文如:people[0][0]
三、图片坐标定位与文字填入
1)身份证ps掉关键字段
2)查询字段坐标位置
1 from PIL importImage2 importmatplotlib.pyplot as plt3 #显示图片具体位置坐标
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 -*-
importosfrom PIL importImage,ImageFont,ImageDrawimportpymysqlimportsysimportdatetimeimportcv2
reload(sys)
sys.setdefaultencoding('utf8')defDrawID():
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 indata:
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()