美文网首页
Python:读取数据库+图片上增加文字

Python:读取数据库+图片上增加文字

作者: 玉米fight | 来源:发表于2020-04-17 11:01 被阅读0次

流程:
1.数据库创建
2.python依赖库安装与连接
3.图片坐标定位与文字填入
4.数据库文字回填
一、数据库创建
使用Navicat来创建库和表。如下图,为Information_DS建立4个表

image.png 终端连接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”。对应主键的数据类型、位数、名称都必须一致 image.png
二、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 image.png
解答:

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

image.png 解答:https://www.jianshu.com/p/da9dd5dd4bd2
2)连接
python中数据库连接,输出单个文字
try:
    conn=pymysql.connect(host='localhost',user='root', password='root',database='Information_DS',charset='utf8')
    sql="select * from People"
    cur=conn.cursor()
    cur.execute(sql)
    data=cur.fetchall()
    conn.close()
    print data[0][1]
except Exception,e:
    print e

Q&A:

1.输出的字符显示\xe7等 image.png 解答:

可能1:数据库编码需要改成utf-8,

  1. Navicat中,选择“设计表”-“选项”,修改默认字符集“utf8”,默认排序规则“utf8_general_ci”,字段页也需要修改对应的字符集。 image.png image.png

    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掉关键字段

image.png image.png 2)查询字段坐标位置
from PIL import Image
import matplotlib.pyplot as plt
#显示图片具体位置坐标
#正面(110,70)姓名  (110,112)性别  (245,112)民族   (110,160)年 (211,160)月 (272,160)日 (110,200)住址 (197,320)证号
#反面 (241,280)机关  (241,327)有效期限
img = Image.open('/Users/funny/Downloads/back.jpg')

plt.figure("Image") # 图像窗口名称
plt.imshow(img)
plt.axis('on') # 关掉坐标轴为 off
plt.show()

3)图片上增加文字
网上下载.ttf格式的字体,双击安装,默认Mac OS文字库路径:/System/Library/Fonts/

im=Image.open('/Users/funny/Downloads/front.jpg')
draw=ImageDraw.Draw(im)
newfont=ImageFont.truetype('/Users/funny/Library/Fonts/微软vista黑体.ttf',20)
draw.text((110,70),'fun',(0,0,0),font=newfont)
draw.text((110,112),'girl',(0,0,0),font=newfont)
draw.text((245,112),'a',(0,0,0),font=newfont)
draw.text((110,160),'1990',(0,0,0),font=newfont)
draw.text((211,160),'01',(0,0,0),font=newfont)
draw.text((272,160),'22',(0,0,0),font=newfont)
draw.text((110,200),'guangdongshenzhen',(0,0,0),font=newfont)
draw.text((197,320),'44522119900122',(0,0,0),font=newfont)
im.show()
im.save('/Users/funny/Downloads/1.jpg')
显示效果: image.png image.png

如果是中文字,需要修改成

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

相关文章

网友评论

      本文标题:Python:读取数据库+图片上增加文字

      本文链接:https://www.haomeiwen.com/subject/erhevhtx.html