建立資料表
import pymysql
db = pymysql.connect("localhost","root","","hank")
cursor = db.cursor()
cursor.execute("DROP TABLE IF EXISTS leo")
sql = """CREATE TABLE leo (
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
INCOME FLOAT)"""
cursor.execute(sql)
db.close()
新增資料
import pymysql
db = pymysql.connect("localhost","root","","hank")
cursor = db.cursor()
sql = """INSERT INTO leo(
FIRST_NAME,LAST_NAME,AGE,SEX,INCOME)
VALUES('JOE','B',20,'F',4000) """
try:
#執行sql語句
cursor.execute(sql)
#送出到資料庫執行
db.commit()
except:
#如果發生資料庫執行
db.rollback()
db.close()
mysql> select * from leo;
+------------+-----------+------+------+--------+
| FIRST_NAME | LAST_NAME | AGE | SEX | INCOME |
| Mike | A | 30 | M | 6000 |
| JOE | B | 20 | F | 4000 |
2 rows in set (0.00 sec)
查詢
import pymysql
db = pymysql.connect("localhost","root","","hank")
cursor = db.cursor()
sql = "SELECT * FROM leo "
try:
#執行SQL語句
cursor.execute(sql)
#擷取所有記錄清單
results = cursor.fetchall()
for row in results:
fname = row[0]
lname = row[1]
age = row[2]
sex = row[3]
income = row[4]
#列印結果
print("fname=%s,lname=%s,age=%d,sex=%s,income=%d" % (fname,lname,age,sex,income))
except:
print("Error:unable to fetch data")
fname=Mike,lname=A,age=30,sex=M,income=6000
fname=JOE,lname=B,age=20,sex=F,income=4000
資料庫更改
import pymysql
db = pymysql.connect("localhost","root","","hank")
cursor = db.cursor()
sql = "UPDATE leo SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M') #找到sex性别為M的更新其年齡+1
try:
cursor.execute(sql)
db.commit()
except:
db.rollback()
db.close()
| Mike | A | 31 | M | 6000 |
删除操作
import pymysql
db = pymysql.connect("localhost","root","","hank")
cursor = db.cursor()
sql = "delete from leo where name = hankleo"
try:
cursor.execute(sql)
db.commit()
except:
db.rollback()
db.close()
知識在于點滴積累