Python 中可以使用 PyMySQL
模块连接到 MySQL 数据库进行读写操作。
安装 PyMySQL :
pip install pymysql
连接到数据库
import pymysql
db = pymysql.connect(host='localhost',
user='user',
password='password',
database='database_name')
执行 SQL 查询
cursor = db.cursor()
sql = "SELECT * FROM EMPLOYEE WHERE AGE >= '%d'" % (20)
try:
cursor.execute(sql)
results = cursor.fetchall()
for row in results:
fname = row[0]
lname = row[1]
age = row[2]
print("fname=%s,lname=%s,age=%d" % \
(fname, lname, age))
except:
print("Error: unable to fetch data")
执行插入操作
cursor = db.cursor()
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
LAST_NAME, AGE, SEX, INCOME)
VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
cursor.execute(sql)
db.commit()
except:
db.rollback()
关闭数据库连接
db.close()
封装函数实例
import pymysql
def rmysql(v_sql):
db = pymysql.connect(host='xx.xx.xx',user='root',password='xxxxx',database='xxxx',autocommit=True)
cursor = db.cursor()
cursor.execute(v_sql)
result=cursor.fetchall()
return result
db.close()
def wmysql(v_sql):
db = pymysql.connect(host='xxx.xxx.xx',user='root',password='xxxx',database='xx',autocommit=True)
cursor = db.cursor()
cursor.execute(v_sql)
db.close()
asql=''' select * from xxxx ; '''
msa=rmysql(v_sql=asql)
for i in range(len(msa)) :
print(message=msa[i][0])
isql =''' insert into tb_xxx (id,name) values ('xxx','xxx'); '''
wmysql(v_sql=isql)