介紹
介紹
今天我們學習如何透過Python操作MySQL資料庫。
在Python2.7下使用的是pymysql這個軟體包，跟著福哥來操作吧。
安裝安裝pymysql
直接使用pip安裝即可
pip insall pymysql授權
MySQL資料庫伺服器預設只能在安裝了資料庫伺服器的本機操作，也就是在TFLinux上操作，但是我們的python是安裝在TFWindows上的啊！所以，我們要授權任何電腦都可以操作TFLinux上的MySQL資料庫。當然了，肯定是需要正確的使用者名稱和密碼的啦！
在TFLinux上登入mysql
[root@tfdev ~]# mysql -uroot -pabcdefmysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 3Server version: 5.7.28 MySQL Community Server (GPL)Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>授權tfse使用者可以在任意電腦上連線MySQL資料庫伺服器，且對資料庫tfse有完全控制權限
mysql> grant all on tfse.* to tfse@'%' identified by 'abcdef';Query OK, 0 rows affected, 1 warning (0.02 sec)建立MySQLDemo.py用來進行pymysql的測試
測試連線
寫入如下程式碼，測試mysql的連線
import pymysqlcn = pymysql.connect("192.168.1.168","tfse","abcdef")如果沒有報錯，證明連線成功了！
測試寫入資料
寫入如下程式碼，向websites表寫入一條資料
import pymysqltry: # connection cn = pymysql.connect("192.168.1.168","tfse","abcdef") cursor = cn.cursor() # select database cn.select_db('tfse')except Exception as e: print e exit()try: # insert data into websites cursor.execute("INSERT INTO websites (domainName, weight, createDT) VALUES (%s,%s,now())", ['tongfu.net', 1]) # commit cn.commit()except Exception as e: print e # rollback cn.rollback()finally: # close cursor.close() cn.close()如果沒有報錯，就表示寫入成功了！
檢視資料
到TFLinux上檢視剛剛寫入的資料
mysql> select * from websites;+-----------+------------+-------+----------+--------+---------------------+-------------+-------------+| websiteId | domainName | title | descript | weight | createDT | lastFetchDT | nextFetchDT |+-----------+------------+-------+----------+--------+---------------------+-------------+-------------+| 3 | tongfu.net | NULL | NULL | 1 | 2020-10-08 12:47:12 | NULL | NULL |+-----------+------------+-------+----------+--------+---------------------+-------------+-------------+1 row in set (0.00 sec)測試查詢資料
寫入如下程式碼，查詢websites裡的資料
import pymysqltry: # connection cn = pymysql.connect("192.168.1.168","tfse","abcdef") cursor = cn.cursor() # select database cn.select_db('tfse')except Exception as e: print e exit()try: # query cursor.execute("SELECT * FROM websites") print ("總記錄數量：" + str(cursor.rowcount)) rows = cursor.fetchall() print ("記錄資訊") for row in rows: print row # commit cn.commit()except Exception as e: print e # rollback cn.rollback()finally: # close cursor.close() cn.close()接下來我們系統學習一下pymysql這個軟體包的功能
寫入操作
寫入操作包括很多，常見的命令包括：CREATE DATABASE/DROP DATABASE、CREATE TABLE/DROP TABLE/ALTER TABLE、INSERT INTO/UPDATE/DELETE等等。
寫入操作都需要透過pymysql的cursor.execute方法來執行，如果執行出現異常會丟擲except，我們捕獲它進行後面的處理即可。
插入單行資料
插入一行資料，強烈建議使用指定欄位名稱的方式寫入資料
import pymysqltry: # connection cn = pymysql.connect("192.168.1.168","tfse","abcdef") cursor = cn.cursor() # select database cn.select_db('tfse')except Exception as e: print e exit()try: # insert data into websites cursor.execute("INSERT INTO websites (domainName, weight, createDT) VALUES (%s,%s,now())", ['www.tongfu.net', 1]) # websiteId of insert data websiteId = cursor.lastrowid print ("插入資料的websiteId是：" + str(websiteId)) # commit cn.commit()except Exception as e: print e # rollback cn.rollback()finally: # close cursor.close() cn.close()插入多行資料
可以傳入陣列到cursor.executemany實現多行資料插入的操作
import pymysqltry: # connection cn = pymysql.connect("192.168.1.168","tfse","abcdef") cursor = cn.cursor() # select database cn.select_db('tfse')except Exception as e: print e exit()try: # insert data into websites rows = [ ('www.baidu.com',1), ('www.taobao.com',1), ('www.jd.com',1) ] cursor.executemany("INSERT INTO websites (domainName, weight, createDT) VALUES (%s,%s,now())", rows) # commit cn.commit()except Exception as e: print e # rollback cn.rollback()finally: # close cursor.close() cn.close()更新資料
使用UPDATE語句進行資料的修改操作
import pymysqltry: # connection cn = pymysql.connect("192.168.1.168","tfse","abcdef") cursor = cn.cursor() # select database cn.select_db('tfse')except Exception as e: print e exit()try: # insert data into websites cursor.execute("UPDATE websites SET weight = 10 WHERE domainName = %s", 'tongfu.net') # commit cn.commit()except Exception as e: print e # rollback cn.rollback()finally: # close cursor.close() cn.close()刪除資料
import pymysqltry: # connection cn = pymysql.connect("192.168.1.168","tfse","abcdef") cursor = cn.cursor() # select database cn.select_db('tfse')except Exception as e: print e exit()try: # insert data into websites cursor.execute("DELETE FROM websites WHERE domainName = %s", 'www.baidu.com') # commit cn.commit()except Exception as e: print e # rollback cn.rollback()finally: # close cursor.close() cn.close()讀取操作
讀取操作只的就是SELECT語句了，這個SELECT語句可以有非常複雜的變化，設計到多表聯查的時候會更加複雜，有時候一個SQL語句幾千個字元都很正常。
讀取一行資料
透過cursor.fetchone讀取一行資料
import pymysqltry: # connection cn = pymysql.connect("192.168.1.168","tfse","abcdef") cursor = cn.cursor() # select database cn.select_db('tfse')except Exception as e: print e exit()try: # insert data into websites cursor.execute("SELECT * FROM websites WHERE domainName = %s", 'tongfu.net') # fetch one result = cursor.fetchone() print result # commit cn.commit()except Exception as e: print e # rollback cn.rollback()finally: # close cursor.close() cn.close()讀取全部資料
透過cursor.fetchall讀取查詢結果集的全部資料
import pymysqltry: # connection cn = pymysql.connect("192.168.1.168","tfse","abcdef") cursor = cn.cursor() # select database cn.select_db('tfse')except Exception as e: print e exit()try: # insert data into websites cursor.execute("SELECT * FROM websites") # fetch all results = cursor.fetchall() for result in results: print result # commit cn.commit()except Exception as e: print e # rollback cn.rollback()finally: # close cursor.close() cn.close()資料分頁
所謂資料翻頁就是實現指定讀取資料的起始位置和讀取資料的條目數的技巧，透過這個技巧可以將一組資料均勻分成若干份，每一份相當於一頁，這樣的設計可以讓使用者像瀏覽圖書一樣的翻頁效果
import pymysqlimport mathtry: # connection cn = pymysql.connect("192.168.1.168","tfse","abcdef") cursor = cn.cursor() # select database cn.select_db('tfse')except Exception as e: print e exit()try: # insert data into websites cursor.execute("SELECT * FROM websites") # pages cursor.scroll(0, mode="absolute") # move to 0 try: # count page numbers totalNum = cursor.rowcount pageNum = 1 pageCount = math.ceil(float(totalNum)/3) while 1: # fetch 2 rows results = cursor.fetchmany(2) print ("頁碼：" + str(pageNum)) for result in results: print result # page number +1 pageNum = pageNum+1 # page number more than page count then break if pageNum > pageCount: break except Exception as e: print e # commit cn.commit()except Exception as e: print e # rollback cn.rollback()finally: # close cursor.close() cn.close()今天我們學習了透過pymysql操作MySQL資料庫的方法，大家課後可以自己多多練習，一定要數量掌握這個技巧才可以哦~~
https://m.tongfu.net/home/35/blog/512799.html
最新評論