python3 操作数据库

import pymysql
# 打开数据库连接
     db = pymysql.connect(host='127.0.0.1',user='root',passwd='919824467',db='mysql')
    # 使用 cursor() 方法创建一个游标对象 cursor
    cur = db.cursor()
    #数据库语句
    cur.execute('CREATE DATABASE IF NOT EXISTS code')
    cur.execute('USE code')
    cur.execute('''CREATE TABLE IF NOT EXISTS num(
                    id INT NOT NULL AUTO_INCREMENT,
                    code VARCHAR(32) NOT NULL,
                    PRIMARY KEY(id) )''')
            for num in codelist:
                cur.execute('INSERT INTO num(code) VALUES(%s)',(num))
                cur.connection.commit()
    db.close()

​
import base64
import re
from sqlalchemy import Column, String, DATE, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

database_info = {
    'user': '',
    'passwd': '',
    'ip': '',
    'port': '',
    'database': ''
}


class Coupon(Base):
    __tablename__ = 'coupon'

    id = Column(String(200), primary_key=True)
    deadline = Column(DATE)
    userID = Column(String(200))
    code = Column(String(200))


def make_connect(DB_info):
    connect_str = 'mysql+pymysql://{user}:{passwd}@{ip}:{port}/{database}'.format_map(DB_info)
    engine = create_engine(connect_str)
    DBSession = sessionmaker(engine)
    session = DBSession()
    return session


def parse_coupon(c_code):
    return base64.urlsafe_b64decode(c_code.encode('utf-8'))


def upload_to_database():
    session = make_connect(database_info)
    with open('coupon.txt', 'r') as file:
        for line in file.readlines():
            c_id = re.findall(r'.*/.*:(.*)\'', str(parse_coupon(line)))
            session.add(Coupon(id=c_id.pop(), code=line))
        session.commit()
        session.close()

if __name__ == '__main__':
    upload_to_database()

​