Python learning note-day7-"Python Operations Database"

Last time we talked about the Python operations database, we continue to complement the Python operations database, get all the field values in the table, and export the data to Excel as an Excel table header.

One, the last time I said to encapsulate MySQL operation

1. Install functions perform database operations

 #封装一下函数执行数据库操作 
 def my_db (Host,user, passwd, Db,sql, port=3306, charset= ' UTF8 '): 
 Import PYMYSQL 
 Conn = Pymysql.connect (user=user,host=host,port=port,passwd=passwd,db=db,charset=charset) 
 cur = conn.cursor () #建立游标 
 Cur.execute (SQL) #执行mysql 
 # res =cur.execute (' select * from Nhy WHERE username= "xxx" and passwd= "12345" ')  username = ' xxxxxx ' 
 passwd = ' 123456 ' 
 sql = ' SELECT * FROM table_name where name= '%s ' and passwd= '%s ' % (username, passwd) 
 res = cur.execute (sql) 
 if Sql.strip () [: 6].upper () = = ' SELECT ': 
 res = Cur.fe Tchall () #res返回的是一个list 
 If res: 
 Print (' User already exists. ') 
 Else: 
 Print (' user does not exist ') 
 Else: 
 Conn.commit () 
 res = ' OK ' 
 Cur.clo SE () 
 Conn.close () 
 return res 
 
 Here res = Cur.fetchall () #res返回的是一个list 
 If you need to get a result set, you need to traverse the entire two-dimensional array. 
 
 Second, you can specify the result type that is returned, you need to specify the Dict type when the cursor is established, as follows: 

cur = conn.cursor (cursor=pymysql.cursors.dictcursor) #建立游标的时候, cursor type specified, return is a dictionary

1. Specific usage

Import Pymysql
def my_db (SQL, port=3306, charset= ' UTF8 '):
Import Pymysql
Host, user, passwd, db = ' 127.0.0.1 ', ' xxx ', ' xxxxx ', ' xxxx '

conn = Pymysql.connect (user=user,host=host,port=port,passwd=passwd,db=db,charset=charset)
cur = conn.cursor (cursor=pymysql.cursors.dictcursor) #建立游标的时候, cursor type specified, return is a dictionary
Cur.execute (SQL) #执行mysql
If Sql.strip () [: 6].upper () = = ' SELECT ':
res = '
res = Cur.fetchall () #res返回的是一个list
Print (RES)
# res = Cur.fetchone ()
# Print (Cur.fetchone ())
# Print (Cur.fetchone ())
# Cur.fetchmany () #能传入一个数, returns how many data

  # Fetchall () #获取到这个sql执行的全部结果, it puts every row of data in the database into a list
# [[' 1 ', ' 2 ', ' 3 ']] [{} { } {}]
#fetchone () #获取到这个sql执行的一条结果, it returns just one piece of data
#如果sql语句执行的结果是多条数据的时候, then use Fetchall ()
#如果能确定sql执行的结果是只有一条数据的时候, then with Fetchone ()

       # Print (cur.description) # Gets the field description of the data table
# (' ID ', 3, none, one, one, 0, true), (' Name ', 253, none,, 0, True), (' Sex ', 253, none,, 0, True))
#取表头
# fileds = []
# for filed in Cur.description:
# fileds.append (Filed[0])
     fileds = [filed[0] for filed in Cur.description] #列表生成式, as shown above
print (fileds)
# return Fileds
return res

Else
Conn.commit ()
res = ' OK '
Cur.close ()
Conn.close ()
return res

sql= ' select * from Stu limit; '
res = my_db (SQL)
Print (RES)

The results are as follows:

  res = Cur.fetchall () #res返回的是一个list
  Print (RES)

[{' id ': 1, ' name ': ' Nana ', ' sex ': ' Female '}, {' id ': 1, ' name ': ' Nana ', ' sex ': ' Female '}]

Fileds = [filed[0] for filed in Cur.description] #列表生成式, as shown above
Print (fileds)
The results are as follows:

[' id ', ' name ', ' sex ']

2. View the Help method of ontology

Print
    Dir (pymysql.cursors) #这个可以打印出来ymysql. Cursors what are the methods
)

Results:
[' Cursor ', ' dictcursor ', ' dictcursormixin ', ' PY2 ', ' re_insert_values ', ' sscursor ', ' ssdictcursor ', ' __builtins__ ', ' __ Cached__ ', ' __doc__ ', ' __file__ ', ' __loader__ ', ' __name__ ', ' __package__ ', ' __spec__ ', ' absolute_import ', ' err ', ' Partial ', ' print_function ', ' range_type ', ' re ', ' text_type ', ' warnings ']

Python learning note-day7-"Python Operations Database"