python - db処理(mysqlclient)

参考

Windowsの場合は、pip の前に「py -m 」を付ける! gammasoft.jp

select
import MySQLdb

db_config = {
    'host': 'localhost',
    'port': 3306,
    'db': 'mytest',
    'user': 'user1',
    'passwd': 'pass',
    'charset': 'utf8',
}

try:
    conn = MySQLdb.connect(
        host=db_config['host'],
        port=db_config['port'],
        db=db_config['db'],
        user=db_config['user'],
        passwd=db_config['passwd'],
        charset=db_config['charset'])

    cur = conn.cursor(MySQLdb.cursors.DictCursor)
    cur.execute('select * from person where id=%s', "2")    # where

    for row in cur:
          print(str(row["id"]) + "/" + row["name"] + "/" + str(row["old"]))

except MySQLdb.Error as ex:
    print('MySQL Error: ', ex)

finally:
    cur.close()
    conn.close()
insert
import MySQLdb

db_config = {
    'host': 'localhost',
    'port': 3306,
    'db': 'mytest',
    'user': 'user1',
    'passwd': 'pass',
    'charset': 'utf8',
}

try:
    conn = MySQLdb.connect(
        host=db_config['host'],
        port=db_config['port'],
        db=db_config['db'],
        user=db_config['user'],
        passwd=db_config['passwd'],
        charset=db_config['charset'])

    conn.autocommit(False)

    cur = conn.cursor(MySQLdb.cursors.DictCursor)
    cur.execute('insert into person values (%(id)s, %(name)s, %(old)s)',
        {'id':4, 'name':'manabe', 'old':40 }
    )

    conn.commit()

except MySQLdb.Error as ex:
    conn.rollback()
    print('MySQL Error: ', ex)
finally:
    cur.close()
    conn.close()
insert(複数)
import MySQLdb

db_config = {
    'host': 'localhost',
    'port': 3306,
    'db': 'mytest',
    'user': 'user1',
    'passwd': 'pass',
    'charset': 'utf8',
}

try:
    conn = MySQLdb.connect(
        host=db_config['host'],
        port=db_config['port'],
        db=db_config['db'],
        user=db_config['user'],
        passwd=db_config['passwd'],
        charset=db_config['charset'])

    conn.autocommit(False)
    cur = conn.cursor(MySQLdb.cursors.DictCursor)

    persons = [
        (6,"mori", 42),
        (7,"yaguchi", 43),
    ]

    cur.executemany('insert into person values (%s, %s, %s)',persons)
    conn.commit()

except MySQLdb.Error as ex:
    conn.rollback()
    print('MySQL Error: ', ex)
finally:
    cur.close()
    conn.close()