python3只支持pymysql(cpython >= 2.6 or >= 3.3,mysql >= 4.1),python2支持mysqldb。
两个例子:
import pymysql
db = pymysql.connect('localhost', 'root', '123456', 'crawlsql')
cursor = db.cursor()
try:
create_table_sql = "CREATE TABLE IF NOT EXISTS `table` (" \
"`id` int(11) NOT NULL AUTO_INCREMENT," \
"`url` varchar(255) NOT NULL," \
"`path` varchar(255) NOT NULL," \
"`size` float NOT NULL," \
"PRIMARY KEY(`id`)" \
") ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;"
cursor.execute(create_table_sql)
db.commit()
show_table_sql = "SELECT `table` FROM information_schema.tables WHERE table_schema = `crawlsql`;"
cursor.execute(show_table_sql)
result = cursor.fetchone()
print(result)
finally:
db.close()
注意在创建名加上`而不是'(我经历的一个小坑)。
这个例子实现了连接mysql中的crawlsql数据库以及创建张名为'table'的表。查看crawlsql数据库下的table表语句(第17行)报错误:
pymysql.err.InternalError: (1054, "Unknown column 'table' in 'field list'")
官方例子(稍微做了一点修改以满足官方结果):
import pymysql.cursors
# Connect to the database
connection = pymysql.connect(host='localhost',
user='root',
password='123456',
db='crawlsql',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
try:
with connection.cursor() as cursor:
sql = "CREATE TABLE IF NOT EXISTS `table` (" \
"`id` int(11) NOT NULL AUTO_INCREMENT," \
"`email` varchar(50) NOT NULL," \
"`password` varchar(30) NOT NULL," \
"PRIMARY KEY(`id`)" \
") ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1"
cursor.execute(sql)
connection.commit()
with connection.cursor() as cursor:
# Create a new record
sql = "INSERT INTO `table` (`email`, `password`) VALUES (%s, %s)"
cursor.execute(sql, ('webmaster@python.org', 'very-secret'))
connection.commit()
with connection.cursor() as cursor:
# Read a single record
sql = "SELECT `id`, `password` FROM `table` WHERE `email`=%s"
cursor.execute(sql, ('webmaster@python.org',))
result = cursor.fetchone()
print(result)
finally:
connection.close()
运行结果:
{'id': 1, 'password': 'very-secret'}
Process finished with exit code 0
再修改一点点(没有意义):
import pymysql.cursors
# Connect to the database
connection = pymysql.connect(host='localhost',
user='root',
password='123456',
db='crawlsql',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
try:
with connection.cursor() as cursor:
sql = "CREATE TABLE IF NOT EXISTS `table` (" \
"`id` int(11) NOT NULL AUTO_INCREMENT," \
"`email` varchar(50) NOT NULL," \
"`password` varchar(30) NOT NULL," \
"PRIMARY KEY(`id`)" \
") ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1"
cursor.execute(sql)
connection.commit()
with connection.cursor() as cursor:
# Create a new record
sql = "INSERT INTO `table` (`email`, `password`) VALUES (%s, %s)"
cursor.execute(sql, ('webmaster@python.org', 'very-secret'))
connection.commit()
with connection.cursor() as cursor:
# Read a single record
sql = "SELECT * FROM `table`"
cursor.execute(sql)
result = cursor.fetchone()
print(result)
finally:
connection.close()
运行结果:
{'id': 1, 'email': 'webmaster@python.org', 'password': 'very-secret'}
Process finished with exit code 0