1.連接數(shù)據(jù)庫(kù)
import pymysql
#連接數(shù)據(jù)庫(kù)
connection = pymysql.connect(host = 'localhost', #訪(fǎng)問(wèn)地址
port=3306, #訪(fǎng)問(wèn)端口
user = 'root' , #登錄名
password = 'root', #訪(fǎng)問(wèn)密碼
db = 'test' #庫(kù)名
)
#創(chuàng)建游標(biāo)
cur=connection.cursor()
#查詢(xún)庫(kù)中某表的數(shù)據(jù)
cur.execute("select * from ttt")
#fetch_ 獲取數(shù)據(jù)
#fetchone獲取一行數(shù)據(jù) fetchall() 獲取表中所有數(shù)據(jù)
ret1=cur.fetchmany(3)
print(ret1)
#查詢(xún)數(shù)據(jù)庫(kù)版本
cur.execute("select version()")
data = cur.fetchone()
print(" Database Version:%s" % data)
#關(guān)閉指針對(duì)象
cur.close()
#關(guān)閉連接的數(shù)據(jù)庫(kù)
connection.close()
2.查詢(xún)操作
import pymysql
#連接數(shù)據(jù)庫(kù)
db = pymysql.connect(host = 'localhost', #訪(fǎng)問(wèn)地址
port=3306, #訪(fǎng)問(wèn)端口
user = 'root' , #登錄名
password = 'root', #訪(fǎng)問(wèn)密碼
db = 'test' #庫(kù)名
)
#創(chuàng)建游標(biāo)
cur=db.cursor()
#查詢(xún)庫(kù)中某表的數(shù)據(jù)
sql="select * from ttt"
try:
cur.execute(sql) #執(zhí)行sql語(yǔ)句
results=cur.fetchmany(10)
print("id","date","password") #設(shè)置字段名
#遍歷結(jié)果
for row in results :
id =row[0]
date =row[1]
password =row[2]
print(id,date,password)
except exception as e:
raise e
finally:
db.close
3.數(shù)據(jù)庫(kù)表的新建荷憋、插入、更新邻悬、刪除 操作
import pymysql
#連接數(shù)據(jù)庫(kù)
db = pymysql.connect(host = 'localhost', #訪(fǎng)問(wèn)地址
port=3306, #訪(fǎng)問(wèn)端口
user = 'root' , #登錄名
password = 'root', #訪(fǎng)問(wèn)密碼
db = 'test' #庫(kù)名
)
#使用cursor()方法創(chuàng)建游標(biāo)對(duì)象cur
cur=db.cursor()
#------------1.創(chuàng)建數(shù)據(jù)庫(kù)表------------
#使用execute()方法執(zhí)行SQL修然，如果表存在，則刪除
cur.execute("DROP TABLE IF EXISTS YUBG")
#使用預(yù)處理語(yǔ)句創(chuàng)建表
sql="""create table yubg(name char(20) not null,nickname char(20),age int,sex char(1),income float)"""
cur.execute(sql)
# 關(guān)閉數(shù)據(jù)庫(kù)連接
db.close
#------------2.數(shù)據(jù)庫(kù)表插入記錄------------
sql_insert="insert into yubg(name,nickname,age,sex,income) values('張三','小張',16,'男2',300)"
try:
cur.execute(sql_insert)
db.commit() #提交數(shù)據(jù)庫(kù)執(zhí)行
except Exception as e:
db.rollback() #如果發(fā)生錯(cuò)誤艰猬，進(jìn)行回滾
finally:
db.close()
#------------3.數(shù)據(jù)庫(kù)表更新數(shù)據(jù)------------
sql_update="update yubg set income=%d where name= '%s' "
try:
cur.execute(sql_update % (600,"張三")) #向sql語(yǔ)句傳遞參數(shù)
db.commit() #提交數(shù)據(jù)庫(kù)執(zhí)行
except Exception as e:
db.rollback()
finally:
db.close()
#------------4.數(shù)據(jù)庫(kù)表刪除記錄------------
sql_delete="delete from yubg where name='%s'"
try:
cur.execute(sql_delete % ("張三"))
db.commit()
except Exception as e:
db.rollback()
finally:
db.close()
