python mysql example_Python_Example_ Pycharm(python) 与 数据库(MySQL) 连接学习/示例

#coding=utf-8#---------------------------------

'''# Author : chu ge

# Function:

#'''

#---------------------------------

importpymysql'''#---------

一、创建表

二、查询表

SQL_table_read = "show tables;"

三、修改表

SQL_table_update = "alter table add|change|drop ;"

四、删除表

SQL_table_drop = "drop table ;"

#---------

# CRUD 数据

增加 create

查询 read

修改 update

删除 delete

一、增加

SQL_data_insert_all = "insert into values(。。。);" # 全插入

SQL_data_insert_single = "insert into values(。。。);"# 缺省插入 单行单列

SQL_data_insert_single_1 = "insert into values(。。。),(。。。),(。。。);"# 缺省插入 多行单列

SQL_data_insert_single_2 = "insert into values(。。。),(。。。),(。。。);" # 缺省插入 多行多列

二、查询

SQL_data_read = "select * from <> where "

三、修改

SQL_data_update = " update set = where "

SQL_data_update_1 = " update isdelete=1 where " # 逻辑删除

四、删除

SQL_data_delete = "delete from where "'''

#------------------

classClass_Mysql_Helper(object):def __init__(self,host,port,user,passwd,db,chaerset='utf8'):

self.host= host #localhost

self.port = port #3306

self.user = user #root

self.passwd = passwd #123

self.db = db #python3

self.charset = chaerset #固定

defMethods_Open(self):#用于建立与数据库的连接

self.conn = pymysql.connect(host =self.host,

port=self.port,

user=self.user,

passwd=self.passwd,

db=self.db

)#操作数据库的游标

self.cursor =self.conn.cursor()defMethods_Close(self):

self.cursor.close()#关闭数据表

self.conn.close() #关闭数据库

defMethods_Data_CURD(self,sql,params):try:

self.Methods_Open()

self.cursor.execute(sql,params)

self.conn.commit()

self.Methods_Close()print("OK")except(Exception,error):print(error.message)#重复使用代码 使用封装

def Methods_All(self,sql,params=[]):try:

self.Methods_Open()

self.cursor.execute(sql, params)

result=self.cursor.fetchall()

self.Methods_Close()returnresultexcept(Exception, error):print(error.message)'''# ============================================================================

# 测试专用

# ============================================================================'''

if __name__ == "__main__":

NAME=input('请输入用户姓名:')

ID=input('请输入用户编号:')#调用 对象

SQL_help = Class_Mysql_Helper("localhost", 3306, "root", "123", "python3")#修改 update

Sql = 'update aa set name=%s where id=%s'Params=[NAME,ID]

SQL_help.Methods_Data_CURD(Sql,Params)#查询 ead

Sql_read = 'select id,name from aa where id<3'Result=SQL_help.Methods_All(Sql_read)print(Result)