PySe-009-测试辅助示例应用数据库更新语句创建

 1 #!/usr/bin/env python
 2 # -*- coding: UTF-8 -*-
 3 
 4 import time
 5 import DbsUtil
 6 
 7 if __name__ == '__main__':
 8 
 9     TIME_FORMAT_STYLE='%Y-%m-%d %X'
10 
11     time_start = '1900-01-01 18:43:49'
12     time_end = '2016-12-21 00:00:00'
13 
14     conn_dict = {"server": "127.0.0.1,1433", "db_name": "pythonMssql", "user_name": "ffp", "pwd": "ffp123"}
15 
16     time_stamp_start = time.mktime(time.strptime(time_start, TIME_FORMAT_STYLE))
17     time_stamp_end = time.mktime(time.strptime(time_end, TIME_FORMAT_STYLE))
18 
19     timestamp_step = 60
20 
21     update_time = time.strftime(TIME_FORMAT_STYLE, time.localtime(time_stamp_start))
22 
23     row_num_start = 1
24     row_num_step  = 2000
25     row_num_end   = 2000
26 
27     ms = DbsUtil.MSSQL(host="127.0.0.1",user="ffp",pwd="ffp123",db="pythonMssql")
28     resList = ms.ExecQuery("SELECT count(*) as counts_up from C_Application")
29     print resList
30     CYCLE_MAXa = int(resList[0][0]) / row_num_step + 1
31     print CYCLE_MAXa
32     CYCLE_MAX = int(1874826 / row_num_step + 1)
33 
34     print CYCLE_MAX
35     CYCLE_MIN = 0
36 
37     while CYCLE_MIN < CYCLE_MAX:
38         row_num_start = 1 + CYCLE_MIN * row_num_step
39         row_num_end   = (CYCLE_MIN + 1) * row_num_step
40         update_time   = time.strftime(TIME_FORMAT_STYLE, time.localtime(time_stamp_start + CYCLE_MIN * timestamp_step))
41 
42         update_sql = "update C_Application set load_start_time = '" + update_time + "' where AppKey in ((select AppKey from (select row_number() over (order by AppKey) as rowId, AppKey from C_Application where Load_Date = '2016-12-21 00:00:00' and load_start_time = '1900-01-01 18:43:49') as t where rowId between " + str(row_num_start) +  " and " + str(row_num_end) + "))"
43 
44         print update_sql
45         CYCLE_MIN = CYCLE_MIN + 1

 1 create proc page_update(
 2     @TableName varchar(50),            -- 表名
 3     @ReFieldsStr varchar(200),         -- 字段名(全部字段为*)
 4     @OrderString varchar(200),         -- 排序字段(必须!支持多字段不用加order by)
 5     @WhereString varchar(500) = N'',   -- 条件语句(不用加where)
 6     @PageSize int,                     -- 每页多少条记录
 7     @PageIndex int = 1 ,               -- 指定当前为第几页
 8     @TotalRecord int output            -- 返回执行结果总记录数
 9 )
10 as
11 begin
12     -- 处理开始点和结束点
13     Declare @StartRecord int;
14     Declare @EndRecord int; 
15     Declare @TotalCountSql nvarchar(500); 
16     Declare @SqlString nvarchar(2000);    
17     
18         SET @StartRecord = (@PageIndex-1)*@PageSize + 1
19     SET @EndRecord = @StartRecord + @PageSize - 1 
20         -- 总记录数语句
21     SET @TotalCountSql = N'select @TotalRecord = count(*) from ' + @TableName; 
22         -- 查询语句
23     SET @SqlString = N'(select row_number() over (order by '+ @OrderString +') as rowId,'+@ReFieldsStr+' from '+ @TableName;
24     
25     IF (@WhereString != '' or @WhereString != null)
26     BEGIN
27         SET @TotalCountSql=@TotalCountSql + '  where '+ @WhereString;
28         SET @SqlString =@SqlString+ '  where '+ @WhereString;            
29     END
30 
31         -- 返回总记录数
32     EXEC sp_executesql @totalCountSql,N'@TotalRecord int out',@TotalRecord output;
33 
34     -- 执行主语句
35     SET @SqlString ='select ' + @ReFieldsStr + ' from ' + @SqlString + ') as t where rowId between ' + ltrim(str(@StartRecord)) + ' and ' +  ltrim(str(@EndRecord));
36     print @SqlString
37 END
38 
39 
40 
41 --调用分页存储过程 page_update
42 --exec page_update 'C_Application','AppKey','AppKey',"Load_Date = '2016-12-21 00:00:00' and load_start_time = '1900-01-01 18:43:49'",2000,1,0;
43 --exec page_update 'C_Application','*','AppKey',"Load_Date = '2016-12-21 00:00:00' and load_start_time = '1900-01-01 18:43:49'",2000,2,0;
44 
45 --
46 --declare @totalCount int
47 --exec page_update 'C_Application','*','AppKey',"Load_Date = '2016-12-21 00:00:00' and load_start_time = '1900-01-01 18:43:49'",1000,2,@totalCount
48 --select @totalCount as totalCount
49 
50 
51 GO