Python接入MySQL實作增刪改查
-
打開資料庫連接，創建資料庫和表
基本語法如下：execute(query, args=None) # query為字串型別的sql陳述句 # args：可選的序列或映射，用于query的引數值， # 如果args為序列，query中必須使用%s做占位符； # 如果args為映射，query中必須使用%(key)s做占位符案例：資料庫名learning，表名houses，欄位name house_location purchasing_year
import pymysql db = pymysql.connect('localhost', 'root', "password") # 打開資料庫連接，password替換為本機資料庫密碼 cursor = db.cursor() cursor.execute('drop database learning;') cursor.execute('create database learning;') cursor.execute('use learning') sql_create = """create table houses (name VARCHAR(100) NOT NULL, house_location VARCHAR(100) NOT NULL, purchasing_year VARCHAR(100) NOT NULL);""" cursor.execute(sql_create) -
插入
# 插入 sql_insert = """insert into houses values(%s,%s,%s);""" cursor.execute(sql_insert,('夢璃','南天門',1995)) # 插入單條資料 cursor.executemany(sql_insert,[('紫英','蜀山',1996),('天河','石沉',1997),('菱紗','溪洞',1998)]) # 插入多條資料 -
查詢
sql_select = """select * from houses""" # 單條查詢 cursor.execute(sql_select) while 1: result = cursor.fetchone() if result is None: # 取完所有結果 break print(result) # 多條查詢，取3條資料 cursor.execute(sql_select) Result = cursor.fetchmany(3) for res in Result: print(res) # 多條查詢，取所有資料 cursor.execute(sql_select) Result = cursor.fetchall() for res in Result: print(res) -
更新
# 更新一條資料 sql_update = """update houses set purchasing_year=2000 where name='菱紗';""" cursor.execute(sql_update) cursor.execute(sql_select) Result = cursor.fetchall() for res in Result: print(res) # 更新多條資料 sql_update = """update houses set purchasing_year=%s where name=%s;""" cursor.executemany(sql_update,[(2018,'夢璃'),(2019,'紫英')]) cursor.execute(sql_select) Result = cursor.fetchall() for res in Result: print(res) # 回滾事務 db.rollback() cursor.execute(sql_select) Result = cursor.fetchall() for res in Result: print(res) -
洗掉
# 洗掉1條資料 sql_delete = """delete from houses where name='夢璃';""" cursor.execute(sql_delete) cursor.execute(sql_select) Result = cursor.fetchall() for res in Result: print(res) # 洗掉多條資料 sql_delete = """delete from houses where name=%s;""" cursor.executemany(sql_delete,[('天河'),('紫英')]) cursor.execute(sql_select) Result = cursor.fetchall() for res in Result: print(res) -
關閉游標，關閉資料庫連接
cursor.close() # 關閉游標 db.commit() db.close() # 關閉資料庫連接 print('sql執行成功')
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/266430.html
標籤：其他