读取mysql表身份证字段并解读为生日，性别后重新入库--python

1.连接数据库，执行sql脚本

readMysqlData.py

#-*- coding:utf-8 -*-
__author__ = 'grit'
__time__ = '2019-12-02'

import mysql.connector


def connect_db():
    print('开始连接数据库')
    #打开数据库
    db = mysql.connector.connect(
        host='127.*.*.*',         # 数据库主机地址
        user='***',              # 数据库用户名
        passwd='***',          # 数据库密码
        database='enterprise_db_test',   # 连接已有数据库，如果不存在则报错
        autocommit=True
     )
    #sql = 'SELECT ID_CARD_NO FROM `bus_user` where EMP_ID < %s' % (101)
    sql = 'SELECT EMP_ID,ID_CARD_NO FROM `bus_user` where EMP_ID < %s' % (3)
    cursor = db.cursor()
    cursor.execute(sql)
    data_sql = cursor.fetchall()
    print(data_sql)
    for row in data_sql:
        print(row[1])

    cursor.close()
    db.close()
    print('ok')

#
# def read_sql_data():
#


if __name__ == "__main__":
    # read_sql_data()
    connect_db()

D:\Software\Python35\python3.exe D:/Software/python/PycharmProjects/TestDb/venv/information/readMysqlData.py
开始连接数据库
[(1, '150922195804171319'), (2, '220582201801181811')]
150922195804171319
220582201801181811
ok

Process finished with exit code 0

拓展：

1、fetchone()与fetchall()的区别
fetchone():

 sql = 'SELECT EMP_ID,ID_CARD_NO FROM `bus_user` where EMP_ID < %s' % (3)
    cursor = db.cursor()
    cursor.execute(sql)
    data_sql = cursor.fetchone()

这时候取出来的是一维数组，也就是单条记录，(1, ‘150922195804171319’)，可以用data_sql[0],data_sql[1]分别来访问。
print(data_sql[0]), print(data_sql[1])

1
150922195804171319

fetchall()

 sql = 'SELECT EMP_ID,ID_CARD_NO FROM `bus_user` where EMP_ID < %s' % (3)
    cursor = db.cursor()
    cursor.execute(sql)
    data_sql = cursor.fetchall()

返回多个记录，[(1, ‘150922195804171319’), (2, ‘220582201801181811’)]，访问的时候可以用
for row in data_sql:
print(row[0],row[1])来分别访问。
输出结果

1 150922195804171319
2 220582201801181811

fetchmany(x)
获取结果集的下行（指定打印出多少行数据）如： data_sql = cursor.fetchmany(2)
返回2个记录，[(1, ‘150922195804171319’), (2, ‘220582201801181811’)]，访问的时候可以用
for row in data_sql:
print(row[0],row[1])来分别访问。

  sql = 'SELECT EMP_ID,ID_CARD_NO FROM `bus_user` where EMP_ID < %s' % (3)
    cursor = db.cursor()
    cursor.execute(sql)
    data_sql = cursor.fetchmany(2)
    # print(data_sql)
    for row in data_sql:
        print(row[1])

输出结果：

150922195804171319
220582201801181811

可借鉴：此篇文章

2.身份证字段值获取生日及性别

GetInformation.py

import datetime


class GetInformation(object):

    def __init__(self, id):
        self.id = id
        self.birth_year = int(self.id[6:10])
        self.birth_month = int(self.id[10:12])
        self.birth_day = int(self.id[12:14])

    def get_birthday(self):
        """通过身份证号获取出生日期"""
        birthday = "{0}-{1}-{2}".format(self.birth_year, self.birth_month, self.birth_day)
        return birthday

    def get_sex(self):
        """男生：1 女生：2"""
        num = int(self.id[16:17])
        if num % 2 == 0:
            return 2
        else:
            return 1

    def get_age(self):
        """通过身份证号获取年龄"""
        now = (datetime.datetime.now() + datetime.timedelta(days=1))
        year = now.year
        month = now.month
        day = now.day

        if year == self.birth_year:
            return 0
        else:
            if self.birth_month > month or (self.birth_month == month and self.birth_day > day):
                return year - self.birth_year - 1
            else:
                return year - self.birth_year