Simple encapsulation method for Python's Sql database addition, deletion, modification and query operations-Python Tutorial-php.cn

Simple encapsulation method for Python's Sql database addition, deletion, modification and query operations

高洛峰

Release： 2017-03-04 16:36:29

Original

1742 people have browsed it

The example of this article shares with you how to use Python to simply encapsulate the addition, deletion, modification and query of the database for your reference. The specific content is as follows

1.insert

import mysql.connector
import os
import codecs
#设置数据库用户名和密码
user=&#39;root&#39;;#用户名
pwd=&#39;root&#39;;#密码
host=&#39;localhost&#39;;#ip地址
db=&#39;mysql&#39;;#所要操作数据库名字
charset=&#39;UTF-8&#39;
cnx = mysql.connector.connect(user=user,password=pwd, host=host, database=db)
#设置游标
cursor = cnx.cursor(dictionary=True)
#插入数据
#print(insert(&#39;gelixi_help_type&#39;,{&#39;type_name&#39;:&#39;\&#39;sddfdsfs\&#39;&#39;,&#39;type_sort&#39;:&#39;283&#39;}))
def insert(table_name,insert_dict):
  param=&#39;&#39;;
  value=&#39;&#39;;
  if(isinstance(insert_dict,dict)):
    for key in insert_dict.keys():
      param=param+key+","
      value=value+insert_dict[key]+&#39;,&#39;
    param=param[:-1]
    value=value[:-1]
  sql="insert into %s (%s) values(%s)"%(table_name,param,value)
  cursor.execute(sql)
  id=cursor.lastrowid
  cnx.commit()
  return id

Copy after login

2.delete

def delete(table_name,where=&#39;&#39;):
  if(where!=&#39;&#39;):
    str=&#39;where&#39;
    for key_value in where.keys():
      value=where[key_value]
      str=str+&#39; &#39;+key_value+&#39;=&#39;+value+&#39; &#39;+&#39;and&#39;
    where=str[:-3]
    sql="delete from %s %s"%(table_name,where)
    cursor.execute(sql)
    cnx.commit()

Copy after login

3.select

#取得数据库信息
# print(select({&#39;table&#39;:&#39;gelixi_help_type&#39;,&#39;where&#39;:{&#39;help_show&#39;: &#39;1&#39;}},&#39;type_name,type_id&#39;))
def select(param,fields=&#39;*&#39;):
  table=param[&#39;table&#39;]
  if(&#39;where&#39; in param):
    thewhere=param[&#39;where&#39;]
    if(isinstance (thewhere,dict)):
      keys=thewhere.keys()
      str=&#39;where&#39;;
      for key_value in keys:
        value=thewhere[key_value]
        str=str+&#39; &#39;+key_value+&#39;=&#39;+value+&#39; &#39;+&#39;and&#39;
      where=str[:-3]
  else:
    where=&#39;&#39;
  sql="select %s from %s %s"%(fields,table,where)
  cursor.execute(sql)
  result=cursor.fetchall()
  return result

Copy after login

4.showtable,showcolumns

#显示建表语句
#table string 表名
#return string 建表语句
def showCreateTable(table):
  sql=&#39;show create table %s&#39;%(table)
  cursor.execute(sql)
  result=cursor.fetchall()[0]
  return result[&#39;Create Table&#39;]
#print(showCreateTable(&#39;gelixi_admin&#39;))
#显示表结构语句
def showColumns(table):
  sql=&#39;show columns from %s &#39;%(table)
  print(sql)
  cursor.execute(sql)
  result=cursor.fetchall()
  dict1={}
  for info in result:
    dict1[info[&#39;Field&#39;]]=info
  return dict1

Copy after login

The above is the Python Sql database addition, deletion, modification and query operations Related operations, I hope it will be helpful to everyone's learning.

For more related articles on simple encapsulation methods of Python's Sql database addition, deletion, modification and query operations, please pay attention to the PHP Chinese website!