[Python] 使用pymysql操作mysql數據庫 (create table/select/insert/update/delete)

發佈日期: 作者:

先安裝pymysql套件

pip install pymysql

數據庫連接

import pymysql
import logging

def connectDb(dbName):
    try:
        mysqldb = pymysql.connect(
                host="127.0.0.1",
                user="root",
                passwd="123456",
                database=dbName)
        return mysqldb
    except Exception as e:
        logging.error('Fail to connection mysql {}'.format(str(e)))
    return None

select語句

    conn = connectDb('world')
    cur = None
    if conn is not None:
        cur = conn.cursor()    
    sql = 'select * from city'
    if cur is not None:
        cur.execute(sql)
        result_one = cur.fetchone()
        result_many = cur.fetchmany(5) #獲取5筆記錄
        resultall = cur.fetchall()
        print 'Fetch one row:'
        print result_one[1] #數字代表對應column, 由0開始，即0=第一column
        print '\nFetch many(5) rows:'
        for row in result_many:
            print row[1] #數字代表column
        
        print '\nFetch all rows:'
        for row in resultall:
            print row[1] #數字代表column

cursor每一次fetch都會向下移，所以fetch_one()之後執行fetch_all()並不會取得第一筆record “Kabul”

Create table語句

conn = connectDb('school')
cur = None
if conn is not None:
    cur = conn.cursor()
if cur is not None:
    sql = 'CREATE TABLE IF NOT EXISTS `teacher`( \
    `TEACHER_ID` INT(2) NOT NULL, \
    `FIRST_NAME` VARCHAR(20) NOT NULL, \
    `LAST_NAME` VARCHAR(20) NOT NULL, \
    `AGE` VARCHAR(2) NOT NULL \
    )ENGINE=InnoDB'
    cur.execute(sql)

Insert/update/delete語句

conn = connectDb('school')
cur = None
if conn is not None:
    cur = conn.cursor()
if cur is not None:
    sql = 'INSERT INTO `school`.`teacher` (`TEACHER_ID`, `FIRST_NAME`, `LAST_NAME`, `AGE`) VALUES (1, \'TOM\', \'LEE\', 25)'
    cur.execute(sql)
    conn.commit()
conn = connectDb('school')
cur = None
if conn is not None:
    cur = conn.cursor()
if cur is not None:
    sql = 'UPDATE TEACHER SET AGE=28 WHERE TEACHER_ID=1'
    cur.execute(sql)
    conn.commit()
if __name__ == '__main__':
conn = connectDb('school')
cur = None
if conn is not None:
    cur = conn.cursor()
if cur is not None:
    sql = 'DELETE FROM TEACHER WHERE TEACHER_ID=1'
    cur.execute(sql)
    conn.commit()

insert/update/delete語句需要commit才生效

相關文章