Pymysql模块使用操作

一、pymysql模块安装

二、测试数据库连接

'''
测试数据库连接.py
'''from pymysql import Connectioncon = None
try:# 创建数据库连接con = Connection(host="localhost",port=3306,user="root",password="XXXXX")# 测试链接print(con.get_host_info())print(con.get_server_info())
except Exception as e:print("异常", e)
finally:if con:# 关闭连接con.close();

运行之后返回信息，则成功 

三、 创建t_user表

'''
pymysql执行DDL.py
'''from pymysql import Connectioncon = None
try:# 创建数据库连接con = Connection(host="localhost",port=3306,user="root",password="XXXX",database="db_pymysql")# 创建游标对象cursor = con.cursor()# 创建sqlsql = """CREATE TABLE t_user (id INT(11) NOT NULL AUTO_INCREMENT,name VARCHAR(10) NOT NULL,age INT(11) NOT NULL,PRIMARY KEY (id)) ENGINE=InnoDB default charset=utf8"""cursor.execute(sql)
except Exception as e:print("异常", e)
finally:if con:# 关闭连接con.close();

执行结果

四、 执行insert，新增数据

'''
pymasq执行insert操作.py
'''from pymysql import Connectioncon = None
try:# 创建数据库连接con = Connection(host="localhost",port=3306,user="root",password="XXXX",database="db_pymysql",autocommit=True)# 创建游标对象cursor = con.cursor()sql = """insert into t_user value(null,'kaka',43)"""cursor.execute(sql)except Exception as e:print("异常", e)
finally:if con:# 关闭连接con.close();

执行结果

五、执行update，更新数据

'''
pymasq执行update操作.py
'''from pymysql import Connectioncon = None
try:# 创建数据库连接con = Connection(host="localhost",port=3306,user="root",password="XXXX",database="db_pymysql",autocommit=True)# 创建游标对象cursor = con.cursor()sql = "update t_user set age=99 where id=1"cursor.execute(sql)except Exception as e:print("异常", e)
finally:if con:# 关闭连接con.close();

 执行结果

六、 执行update，删除数据

'''
pymasq执行delete操作.py
'''from pymysql import Connectioncon = None
try:# 创建数据库连接con = Connection(host="localhost",port=3306,user="root",password="XXXX",database="db_pymysql",autocommit=True)# 创建游标对象cursor = con.cursor()sql = "delete from  t_user  where id=1"cursor.execute(sql)
except Exception as e:print("异常", e)
finally:if con:# 关闭连接con.close();

执行结果

七、  执行select，查询数据

 先增加3条数据messi，wangs，zada

'''
pymasq执行select查询.py
'''from pymysql import Connectioncon = None
try:# 创建数据库连接con = Connection(host="localhost",port=3306,user="root",password="XXXX",database="db_pymysql")# 创建游标对象cursor = con.cursor()sql = "select * from t_user"cursor.execute(sql)result = cursor.fetchall()# 打印查询结果for i in result:print(i)
except Exception as e:print("异常", e)
finally:if con:# 关闭连接con.close();

执行结果