前言

我們經(jīng)常需要將大量數(shù)據(jù)保存起來以備后續(xù)使用，數(shù)據(jù)庫是一個很好的解決方案。在眾多數(shù)據(jù)庫中，MySQL數(shù)據(jù)庫算是入門比較簡單、語法比較簡單，同時也比較實用的一個。本文主要介紹了Python接入MySQL實現(xiàn)增刪改查的相關(guān)內(nèi)容，下面話不多說，一起來看看詳細的介紹吧

打開數(shù)據(jù)庫連接，創(chuàng)建數(shù)據(jù)庫和表

基本語法如下：

execute(query, args=None)# query為字符串類型的sql語句# args：可選的序列或映射，用于query的參數(shù)值。# 如果args為序列，query中必須使用%s做占位符；# 如果args為映射，query中必須使用%(key)s做占位符

案例：數(shù)據(jù)庫名learning，表名houses，字段name house_location purchasing_year

import pymysqldb = pymysql.connect(’localhost’, ’root’, 'password') # 打開數(shù)據(jù)庫連接，password替換為本機數(shù)據(jù)庫密碼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)) # 插入單條數(shù)據(jù)cursor.executemany(sql_insert,[(’紫英’,’蜀山’,1996),(’天河’,’石沉’,1997),(’菱紗’,’溪洞’,1998)]) # 插入多條數(shù)據(jù)

查詢

sql_select = '''select * from houses'''# 單條查詢cursor.execute(sql_select)while 1: result = cursor.fetchone() if result is None: # 取完所有結(jié)果 break print(result)# 多條查詢，取3條數(shù)據(jù)cursor.execute(sql_select)Result = cursor.fetchmany(3)for res in Result: print(res)# 多條查詢，取所有數(shù)據(jù)cursor.execute(sql_select)Result = cursor.fetchall()for res in Result: print(res)

更新

# 更新一條數(shù)據(jù)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)# 更新多條數(shù)據(jù)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)# 回滾事務(wù)db.rollback()cursor.execute(sql_select)Result = cursor.fetchall()for res in Result: print(res)

刪除

# 刪除1條數(shù)據(jù)sql_delete = '''delete from houses where name=’夢璃’;'''cursor.execute(sql_delete)cursor.execute(sql_select)Result = cursor.fetchall()for res in Result: print(res)# 刪除多條數(shù)據(jù)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)

關(guān)閉游標(biāo)，關(guān)閉數(shù)據(jù)庫連接

cursor.close() # 關(guān)閉游標(biāo)db.commit()db.close() # 關(guān)閉數(shù)據(jù)庫連接print(’sql執(zhí)行成功’)

總結(jié)

到此這篇關(guān)于Python接入MySQL實現(xiàn)增刪改查的文章就介紹到這了,更多相關(guān)Python MySQL增刪改查內(nèi)容請搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持好吧啦網(wǎng)！