Python Basic Learning 6-mongodb, SYS, interface development, operating Excel

1 MySQL Supplement

cur = conn.cursor (cursor=pymysql.cursors.dictcursor) #直接获取的数据转换为字典格式的

Cur.description #直接获取的描述信息

Fileds = [filed[0] for filed in Cur.description] #列表生成式, gets all fields to the first row

Import PYMYSQL,XLWT
conn = Pymysql.connect (host= ' 1.1.1.1 ', user= ' jxz ', password= ' 123456 ', db= ' jxz ', port=3306,charset= ' UTF8 ')
# cur = conn.cursor (cursor=pymysql.cursors.dictcursor) #直接获取的数据转换为字典格式的
cur = conn.cursor () #写excel用默认的二维数组即可, because the order of the dictionary is not fixed, the data will be written wrong
Cur.execute (' select * from app_student limit 10 ')
Print (cur.description) #表的信息
Fileds = [filed[0] for filed in Cur.description] #列表生成式, get to all fields
Print (fileds)
# Print (Cur.fetchall ())
data = List (Cur.fetchall ())
Data. Insert (0,fileds) #insert只是插入指定位置的数据
Print (data)
Book = XLWT. Workbook ()
Sheet = book.add_sheet (' Sheet1 ')
#方法一:
# for Index,line_data in Enumerate (data):
# for Index2,col_data in Enumerate (line_data):
# Sheet.write (Index,index2,col_data)
#一个一个单元格写, write the first row of each cell first
#写每一行的时候, the line number is constant and the column is

#方法二: This method requires the definition of a row and a column to control
Line = 0 #行号

For d in data: #控制行
col = 0 # column number
For Col_data in D: #控制列
Sheet.write (Line,col,col_data)
Col+=1
Line+=1

Book.save (' Ex.xls ')

Cur.close ()
Conn.close ()

Import PYMYSQL,XLWT
def mydb (SQL):
conn = Pymysql.connect (host= ' 1.1.1.1 ', user= ' jxz ', password= ' 123456 ', port=3306,db= ' jxz ', charset= ' UTF8 ', autocommit= True)
cur = conn.cursor ()
Cur.execute (SQL)
Fileds = [filed[0] for filed in Cur.description] #列表生成式, gets the first row of header data information
data = List (Cur.fetchall ()) #将获取的数据直接转换成元组
Data.insert (0,fileds) #将第一行获取到的列表插入到data列表的第1个位置
Return data
Print (MyDB (' select * from app_student limit 10 '))

def write_excel (excel_name):
Book = XLWT. Workbook
Sheet = book.add_sheet (' Sheet1 ')
A = ' select * from App_student '
For Index,line_data in Enumerate (MyDB (sql=a)):
For Index2,col_data in Enumerate (line_data):
Sheet.write (Index,index2,col_data)
Book.save (Excel_name)

2 SYS Module

Import Sys

Print (SYS.ARGV) #把运行python文件的时候, the incoming parameters are put into argv

If Len (command) >1:
Excel = Command[1]
Print (' Use case already running ', Excel)
Else
Print (' When running a python file, you need to pass in a use case name, for example: Python run_case.py case.xls ')

Order of import modules (emphasis)

1. Find python files from the current directory

2, if not in the current directory, then go to the python environment variable (here is not the operating system that set the environment variable) inside to find this Python file, print (Sys.path) #查看python自己的环境变量

The essence of the Import module (emphasis)

The code of this module is executed from beginning to end

Name = ' hahaha '

def conn_db (name):

Print (' This is a funcation ', name)

Print (__name__)

if __name__ = = ' __main__ ': #在其他python文件中导入该模块时, it will not execute if __name__ = = ' __main__ ' below the code

conn_db (' B file ')

Sys.path.append (R ' D:\pythonscript\day7\tools ') #如果创建的python文件不在环境变量中, you can use this method to temporarily set environment variables

3 MongoDB

Import Pymongo

Client = Pymongo. Mongoclient (host= ' 118.24.3.40 ', port=27017)

db = Client[' Zhouyifan '] #选择数据库, if there is no directly created

Collection = Db[' Stu_info ']

Db.zhouyifan.insert ({' name ': ' test1 ', ' sex ': ' Male ', ' age ': 18})

db[' Stu_info '].insert ({' URL ': ' http://www.jd.com ', ' title ': ' JD.com ', ' addr ': ' Beijing '}) #插入

Print (List (db[' Stu_info '].find ({' title ': ' Baidu '})) #查找 and need to be converted to a list for printing

For d in db[' Stu_info '].find ({' title ': ' Baidu '}): #使用循环打印出来的是字典

Print (d)

Collection.delete_one ({' title ': ' Baidu '}) #如果有多条的话 will delete only one

Collection.delete_many () #会删除多条

Print (collection)

Collection.update ({' URL ': ' http://www.jd.com ', ' title ': ' www.jd.com '},{' url ': ' http://www.jd.com ', ' title ': ' Www.jd.com ', ' addr ': ' Yizhuang '}) #更改

4 Interface Development

The role of interface development:

1, know how the interface is developed

2. Prevent others from manipulating your database

3, mock service (that is, to simulate a service, to ensure that the test process smoothly)

Import Sys
Print (Sys.path) #查看python自己的环境变量

The python environment variable is set as follows:

Method One:

Quickly set the entire directory to Python's environment variables (the following method can be used only in Pycharm, if the code is copied to others, the environment variables set will not take effect):

The second way to set environment variables is as follows:

Import Sys,os

Base_path = Os.path.dirname (Os.path.dirname (Os.path.abspath (__file__))) #环境变量设置必须在导入自己写的模块之前, automatically gets the path of the current file up to level two folder

Sys.path.insert (0,base_path) #将动态获取到的环境变量插入到最前面, so the import module is faster

Import Sys,os
Base_path = Os.path.dirname (Os.path.dirname (Os.path.abspath (__file__))) #__file__是当前这个python文件的绝对路径,
# Os.path.abspath (__file__) converts the obtained path delimiter by the Abspath method
#再通过两个dirname获取到父目录
Print (Base_path)
Sys.path.insert (0,base_path)

Here is the module to import the interface module from its own writing:

From Lib.interface Import server

From conf.setting import Server_port

Server.run (host= ' 0.0.0.0 ', port=server_port,debug=true) #设置host =0.0.0.0 can be accessed from other computers on the same LAN, but only native access if you do not specify host

Name = ' hahaha '
def conn_db (name):
Print (' This is a funcation ', name)

Print (' __name__ ', __name__)
if __name__ = = ' __main__ ': #在其他python文件中导入该模块时, it will not execute if __name__ = = ' __main__ ' below the code
conn_db (' B file ')

Interface Development Examples:

Import Flask
Import JSON
Import Pymysql
Import Hashlib
Server = Flask. Flask (__name__) #把当前的python文件当做一个服务, __name__ means main

def my_db (sql,port=3306,charset= ' UTF8 '):
IP, user, passwd, db = ' 1.1.1.1 ', ' jxz ', ' 123456 ', ' jxz '
conn = Pymysql.connect (Host=ip,user=user,
Password=passwd,db=db,
Port=port,charset=charset,autocommit=true)
cur = conn.cursor ()
Sql=sql.strip ()
Cur.execute (SQL)
Sqlstart = Sql[:6].lower () #取sql的开头6位, converted to lowercase
If Sqlstart.startswith (' select ') or Sqlstart.startswith (' Insert '): #判断是selec或show的语句获取对应结果
data = Cur.fetchall ()
else: #加else是为了下面的return不报错
data = ' OK '
Cur.close ()
Conn.close ()
Return data

def my_md5 (s):
m = Hashlib.md5 (S.encode ())
Return M.hexdigest ()

@server. Route ('/login ', methods=[' get ', ' post ')
def login ():
Username = flask.request.values.get (' username ') #是从客户端发送过来的数据
PWD = flask.request.values.get (' password ')
# flask.request.cookies.get (' ssss ') #获取cookie
# flask.request.headers.get (' tttt ') #获取headers
# json_data = Flask.request.json #获取入参是json类型的数据
If username== ' test1 ' and pwd== ' 123456 ':
res = {"Code": +, "MSG": "Login Successful", "Sing": "Dfsdfsdfsdfsdf"}
Else
res = {"Code": $, "MSG": "Account/Password Error"}
Return Json.dumps (res, ensure_ascii=false)

@server. Route ('/register ', methods=[' post ')
DEF reg ():
Username = flask.request.values.get (' username ')
PWD = flask.request.values.get (' password ')
Cpwd = Flask.request.values.get (' Cpassword ')

If username and pwd and cpwd:
SQL1 = ' SELECT * from Nhy where name= '%s '; '%username
Sql_res = my_db (SQL1)
If Sql_res:
res = {"Code": 2001, "MSG": "The user is registered"}
Else
If pwd==cpwd:
Md_pwd = MY_MD5 (pwd)
SQL2 = ' INSERT INTO Nhy (name,pwd) value ("%s", "%s"); '% (USERNAME,MD_PWD)
Sql_res = my_db (SQL2)
res = {"Code": +, "MSG": "Registered successfully"}
Else
res = {"Code": 2002, "MSG": "Two times password input is not the same"}
Else
res = {"Code": 2002, "MSG": "Required parameter is not filled, please check the interface documentation!" "}
Return Json.dumps (res, ensure_ascii=false)

Server.run (port=8989,debug=true) #启动服务, this need to note that after starting the service if the modified script to run a direct restart, you can not click the right button again to run, otherwise the interface conflict will be reported, because the original running service does not stop

5 modifying Excel

Import xlrd

From xlutils import Copy #修改excel需要导入该模块

Book1 = Xlrd.open_workbook (' Ex.xls ') #1, open the original Excel

New_book = Copy.copy (Book1) #2, copy a new Excel

Sheet = new_book.get_sheet (0) #3, get the first sheet page

Sheet.write (1, 3, ' 18 ')

Sheet.write ("Xiaobai")

New_book.save (' Ex.xls ')

6 manipulating Excel

#xlwt can only write Excel

#xlrd can only read Excel

Import xlrd

Book = Xlrd.open_workbook (' Ex.xls ')

Sheet = book.sheet_by_index (0) #通过sheet索引获取sheet

# sheet = book.sheet_by_name (' Sheet1 ') #通过sheet名称获取sheet

Print (book.nsheets) #获取到excel中总共有多少个sheet页

Print (Sheet.cell (0,0). Value) #指定行和列获取某个单元格里的内容

Print (Sheet.cell (0,1). Value)

Print (sheet.row_values (0)) #获取某一行的数据

Print (sheet.nrows) #获取excel表中总共有多少行

Python Basic Learning 6-mongodb, SYS, interface development, operating Excel