Tag: MySQL, PyMySQL, Python

準備條件

1. Python 2.7
2. MySQL 5.5
3. 安裝 PyMySQL pip install PyMySQL

放碼過來

創建一數據表

CREATE TABLE `users` (`id` int(11) NOT NULL AUTO_INCREMENT,`email` varchar(255) COLLATE utf8_bin NOT NULL,`password` varchar(255) COLLATE utf8_bin NOT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
AUTO_INCREMENT=1 ;

連接數據庫并進行CRUD操作

import pymysql.cursors# Connect to the database
connection = pymysql.connect(host='localhost',user='user',password='passwd',db='db',charset='utf8mb4',cursorclass=pymysql.cursors.DictCursor)try:with connection.cursor() as cursor:# Create a new recordsql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"cursor.execute(sql, ('abc@163.org', '123456'))# connection is not autocommit by default. So you must commit to save# your changes.connection.commit()with connection.cursor() as cursor:# Read a single recordsql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"cursor.execute(sql, ('abc@163.org',))result = cursor.fetchone()print(result)
finally:connection.close()

參考資料

1. PyMySQL · PyPI
2. Welcome to PyMySQL’s documentation