python操作mysql

#执行查询

import pymysql
# mysqlTest=pymysql.connect(
#     host='localhost',#获取主机名
#     user='root',#用户名
#     passwd='123456',#密码
#     db='mysql'#数据库名称
# )
mysqlTest=pymysql.connect('localhost','root','123456', 'mysql')
#获取游标对象
cur=mysqlTest.cursor()
#执行SQL语句
cur.execute('select * from db')
#print(cur.fetchone())#打印一条数据
#print(cur.fetchall())#打印查询到的所有数据
for i in cur.fetchall():
    for row in i:
        print(row)
mysqlTest.close()


#建表
import pymysql,time
#连接数据库
ds=pymysql.connect('localhost','root','123456','mysql')
#获取游标对象
cur=ds.cursor()
cur.execute("drop table employee")
sql="""CREATE TABLE EMPLOYEE (
         FIRST_NAME  CHAR(20) NOT NULL,
         LAST_NAME  CHAR(20),
         AGE INT,  
         SEX CHAR(1),
         INCOME FLOAT )"""
cur.execute(sql)
print(cur.execute("select * from  EMPLOYEE"))
ds.close()




#插入数据

import pymysql
client=pymysql.connect('localhost','root','123456','mysql')
#获取游标对象
cur=client.cursor()
#插入语句
# SQL 插入语句
# sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
#        LAST_NAME, AGE, SEX, INCOME) \
#        VALUES ('%s', '%s',  %s,  '%s',  %s)" % \
#        ('Mac', 'Mohan', 20, 'M', 2000)
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
         LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
    #执行sql
    cur.execute(sql)
    #提交到数据库
    client.commit()
except:
    #发生异常回滚
     client.rollback()
#关闭数据库链接
client.close()


#查询EMPLOYEE表中salary（工资）字段大于1000的所有数据
import pymysql
db=pymysql.connect('localhost','root','123456','mysql')
cur=db.cursor()
sql='select * from employee where INCOME  > %s' %(1000)
try:
    cur.execute(sql)
    #获取查询到的所有数据,结果是元祖
    result=cur.fetchall()
    for row in result:
        # print(row)
        FIRST_NAME=row[0]
        LAST_NAME=row[1]
        AGE=row[2]
        SEX=row[3]
        INCOME=row[4]
        print('FIRST_NAME=%s,LAST_NAME=%s,AGE=%s ,sex=%s,INCOME=%s'
              %(FIRST_NAME,LAST_NAME,AGE,SEX,INCOME))
except:
    print("Error: unable to fetch data")

db.close()

#更新数据
import pymysql
 
# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )
 
# 使用cursor()方法获取操作游标 
cursor = db.cursor()
 
# SQL 更新语句
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 提交到数据库执行
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()
 
# 关闭数据库连接
db.close()


#删除数据
import pymysql
 
# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )
 
# 使用cursor()方法获取操作游标 
cursor = db.cursor()
 
# SQL 删除语句
sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 提交修改
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()
 
# 关闭连接
db.close()



https://pypi.org/project/PyMySQL/#documentation 官方pymysql文档