Python日期和时间 数据类型 及对应数据库操作

本文讲述，如何使用python3将日期类型数据写入mysql或oracle

Python日期模块datetime

import datetime
today = datetime.date.today()
now = datetime.datetime.now()
print(today, type(today))
print(now, type(now))

打印结果

2020-02-16 <class 'datetime.date'>
2020-02-16 21:16:15.487948 <class 'datetime.datetime'>

Oracle

SQL

CREATE TABLE teacher(
tid           NUMBER,
collect_date  DATE
);
-- 插入
INSERT INTO teacher(tid,collect_date)VALUES(2,DATE'2019-08-23');
INSERT INTO teacher(tid,collect_date)VALUES(1,DATE'2020-2-16');
-- 查询
SELECT * FROM teacher;
-- 删表
DROP TABLE teacher PURGE;

Python

# 连接Oracle
from sqlalchemy import create_engine
from config import conn_oracle
engine = create_engine(conn_oracle, encoding='utf-8')

# 建表
from sqlalchemy import Column, Integer, Date
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()  # 创建表基类
class Student(Base):
    __tablename__ = 'teacher'
    tid = Column(Integer, primary_key=True)
    collect_date = Column(Date)
Base.metadata.create_all(bind=engine)  # 建表操作

# 时间和日期
import datetime
today = datetime.date.today()
now = datetime.datetime.now()

# 写数据
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)  # 创建ORM基类
session = Session()  # 创建ORM对象
session.add(Student(tid=0, collect_date=today))  # 插入数据
session.add(Student(tid=1, collect_date=now))  # 插入数据
session.commit()  # 提交
session.close()

# 读数据
data = engine.execute('SELECT * FROM teacher').fetchall()
for tid, collect_date in data:
    print(collect_date, type(collect_date))

# 删表
engine.execute('DROP TABLE teacher PURGE')

打印结果

2020-02-16 00:00:00 <class 'datetime.datetime'>
2020-02-16 21:38:25 <class 'datetime.datetime'>

MySQL

SQL

CREATE TABLE tb_name(
pid INT(9) PRIMARY KEY AUTO_INCREMENT COMMENT '编号',
public_time DATETIME COMMENT '发布时间'
);
INSERT tb_name (pid,public_time)VALUES(1,'2019-08-04');
SELECT * FROM tb_name;
DROP TABLE tb_name;

Python

# 连接Oracle
from sqlalchemy import create_engine
from config import conn_mysql
engine = create_engine(conn_mysql, encoding='utf-8')

# 建表
from sqlalchemy import Column, Integer, Date
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()  # 创建表基类
class Student(Base):
    __tablename__ = 'teacher'
    tid = Column(Integer, primary_key=True)
    collect_date = Column(Date)
Base.metadata.create_all(bind=engine)  # 建表操作

# 时间和日期（字符串和datetime.date都可）
import datetime, time
today = datetime.date.today()
# now = datetime.datetime.now()
# today = time.strftime('%Y-%m-%d')
now = time.strftime('%Y-%m-%d %H:%M:%S')

# 写数据
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)  # 创建ORM基类
session = Session()  # 创建ORM对象
session.add(Student(tid=2, collect_date=today))  # 插入数据
session.add(Student(tid=1, collect_date=now))  # 插入数据
session.commit()  # 提交
session.close()

# 读数据
data = engine.execute('SELECT * FROM teacher').fetchall()
for tid, collect_date in data:
    print(collect_date, type(collect_date))

# 删表
engine.execute('DROP TABLE teacher')

打印结果

2020-02-16 <class 'datetime.date'>
2020-02-16 <class 'datetime.date'>