sqlalchemy_test

from sqlalchemy import create_engine, MetaData, Table, Column, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.engine.url import URL
import warnings


mysql_url_params = {
    'drivername': 'mysql+pymysql',
    'username': '',
    'password': '',
    'host': '',
    'port': 3306,
    'database': ''
}

engine = create_engine(URL(**mysql_url_params), encoding='utf-8')
metadata = MetaData(engine, reflect=True)

blogrolls = Table(
    'BlogRolls', metadata,
    Column('blogger', String(40), primary_key=True),
    Column('href', String(200), nullable=False)
)

if 'blogrolls' in metadata.tables:
    blogrolls.drop(engine)
blogrolls.create(engine)

session = sessionmaker(bind=engine)()

with warnings.catch_warnings():
    warnings.simplefilter("ignore")
    insert_result = session.execute(blogrolls.insert().prefix_with('IGNORE'), [
        {'blogger': 'Hortencia Pickrell', 'href': 'http://transportative.com/repeatedly/hydromys'},
        {'blogger': 'Krystin Shellhamer', 'href': 'http://mare.com/periphyllum/uncrushed'},
        {'blogger': 'Meghan Ballreich', 'href': 'https://gainst.com/trifle/pyrotherium'},
        {'blogger': 'Adriana Piatkowski', 'href': 'https://seminally.com/undiatonic/peridotitic'},
        {'blogger': 'Karrie Ritrovato', 'href': 'http://foreadvise.com/precant/flirtationless'},
        {'blogger': 'Letitia Lukow', 'href': 'https://overidle.com/unidactyl/anemonal'},
        {'blogger': 'Genoveva Kosmala', 'href': 'https://jutty.com/baryton/undertime'},
        {'blogger': 'Dona Catala', 'href': 'http://plowbote.com/plasm/autoxidation'},
        {'blogger': 'Christy Kriesel', 'href': 'http://apotactici.com/incinerable/teutonist'},
        {'blogger': 'Erwin Hatteyer', 'href': 'http://hyperphoria.com/reachless/canary'},
        {'blogger': 'Erwin Hatteyer', 'href': '-- Duplicate Record --'},
        {'blogger': 'Rubbish', 'href': None},
    ])
    session.commit()

print('更新记录数:', insert_result.rowcount)
for i, (blogger, href) in enumerate(engine.execute('SELECT * FROM blogrolls').fetchall(), 1):
    print(f'{i:>2} | {blogger:18} | {href}')