python pymysql insert_[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才生效