基于Python的SQL Server数据库实现对象同步轻量级

这篇文章主要介绍了基于Python的SQL Server数据库对象同步轻量级实现方法,非常不错,具有一定的参考借鉴价值,需要的朋友可以参考下

缘由

日常工作中经常遇到类似的问题:把某个服务器上的某些指定的表同步到另外一台服务器。

类似需求用SSIS或者其他ETL工作很容易实现,比如用SSIS的话,就会会存在相当一部分反复的手工操作。

建源的数据库信息,目标的数据库信息,如果是多个表,需要一个一个地拉source和target,然后一个一个地mapping,然后运行实现数据同步。

然后很可能,这个workflow使用也就这么一次,就寿终正寝了,却一样要浪费时间去做这个ETL。

快速数据同步实现

于是在想,可不可能快速实现类似需求,尽最大程度减少重复的手工操作?类似基于命令行的方式,简单快捷,不需要太多的手动操作。

于是就有了本文,基于Python(目的是顺便熟悉一下Python的语法),快速实现SQL Server的数据库之间的数据同步操作,后面又稍微扩展了一下,可以实现不同服务器的数据库之间的表结构,表对应的数据,存储过程,函数,用户自定义类型表(user define table type)的同步

目前支持在两个SQL Server数据源之间:每次同步一张或者多张表/存储过程,也可以同步整个数据库的所有表/存储过程(以及表/存储过程依赖的其他数据库对象)。

支持sqlserver2012以上版本

需要考虑到一些基本的校验问题:在源服务器上,需要同步的对象是否存在,或者输入的对象是否存在于源服务器的数据库里。

在目标服务器上,对于表的同步:

1,表的存在依赖于schema,需要考虑到表的schema是否存在,如果不存在先在target库上创建表对应的schema

2,target表中是否有数据?如果有数据,是否以覆盖的方式执行

对于存储过程的同步:

1,类似于表,需要考虑存储过程的schema是否存在,如果不存在先在target库上创建表对应的schema
2,类似于表,arget数据库中是否已经存在对应的存储过程,是否以覆盖的方式执行
3,存储过程可能依赖于b表,某些函数,用户自定义表变量等等,同步存储过程的时候需要先同步依赖的对象,这一点比较复杂,实现过程中遇到在很多很多的坑

  可能存在对象A依赖于对象B,对象B依赖于对象C……,这里有点递归的意思

  这一点导致了重构大量的代码,一开始都是直来直去的同步,无法实现这个逻辑,切实体会到代码的“单一职责”原则

参数说明

参数说明如下,大的包括四类:

1,源服务器信息 (服务器地址,实例名,数据库名称,用户名,密码),没有用户名密码的情况下,使用windows身份认证模式

2,目标服务器信息(服务器地址,实例名,数据库名称,用户名,密码),没有用户名密码的情况下,使用windows身份认证模式

3,同步的对象类型以及对象

4,同步的对象在目标服务器上存在的情况下,是否强制覆盖

基于Python的SQL Server数据库实现对象同步轻量级

其实在同步数据的时候,也可以把需要同步的行数提取出来做参数,比较简单,这里暂时没有做。

比如需要快速搭建一个测试环境,需要同步所有的表结构和每个表的一部分数据即可。

表以及数据同步

表同步的原理是,创建目标表,遍历源数据的表,生成insert into values(***),(***),(***)格式的sql,然后插入目标数据库,这里大概步骤如下:

1,表依赖于schema,所以同步表之前先同步schema

2,强制覆盖的情况下,会drop掉目标表(如果存在的话),防止目标表与源表结构不一致,非强制覆盖的情况下,如果字段不一致,则抛出异常

3,同步表结构,包括字段,索引,约束等等,但是无法支持外键,刻意去掉了外键,想想为什么?因吹斯汀。

4,需要筛选出来非计算列字段,insert语句只能是非计算列字段(又导致重构了部分代码)

5,转义处理,在拼凑SQL的时候,需要进行转义处理,否则会导致SQL语句错误,目前处理了字符串中的'字符,二进制字段,时间字段的转义处理(最容易发生问题的地方)

6,鉴于insert into values(***),(***),(***)语法上允许的最大值是1000,因此每生成1000条数据,就同步一次

7,自增列的identity_insert 标识打开与关闭处理

使用如下参数,同步源数据库的三张表到目标数据库,因为这里是在本机命名实例下测试,因此实例名和端口号输入

执行同步的效果

基于Python的SQL Server数据库实现对象同步轻量级

说明:

1,如果输入obj_type="tab" 且-obj=为None的情况下,会同步源数据库中的所有表。
2,这个效率取决于机器性能和网络传输,本机测试的话,每秒中可以提交3到4次,也就是每秒钟可以提交3000~4000行左右的数据。

已知的问题:

1,当表的索引为filter index的时候,无法生成包含where条件的索引创建语句,那个看起来蛋疼的表结构导出语句,暂时没时间改它。
2,暂时不支持其他少用的类型字段,比如地理空间字段什么的。

存储过程对象的同步

存储过程同步的原理是,在源数据库上生成创建存储过程的语句,然后写入目标库,这里大概步骤如下:
1,存储过程依赖于schema,所以同步存储过程之前先同步schema(同表)
2,同步的过程会检查依赖对象,如果依赖其他对象,暂停当前对象同步,先同步依赖对象
3,重复第二步骤,直至完成
4,对于存储过程的同步,如果是强制覆盖的话,强制覆盖仅仅对存储过程自己生效(删除&重建),对依赖对象并不生效,如果依赖对象不存在,就创建,否则不做任何事情

使用如下参数,同步源数据库的两个存储过程到目标数据库,因为这里是在本机命名实例下测试,因此实例名和端口号输入

说明:测试要同步的存储过程之一为[dbo].[sp_test01],它依赖于其他两个对象:dbo.table01和dbo.fn_test01()

?

1

2

3

4

5

6

7

create proc [dbo].[sp_test01]

as

begin

set no count on;

delete from dbo.table01 where id = 1000

select dbo.fn_test01()

end

而dbo.fn_test01()的如下,依赖于另外一个对象:dbo.table02

?

1

2

3

4

5

6

7

8

9

10

create function [dbo].[fn_test01]

(

)

RETURNS int

AS

BEGIN

declare @count int = 0

select @count = count(1) from dbo.table02

return @count

END

因此,这个测试的[dbo].[sp_test01]就依赖于其他对象,如果其依赖的对象不存在,同步的时候,仅仅同步这个存储过程本身,是没有意义的

同步某一个对象的依赖对象,使用如下SQL查出来对象依赖信息,因此这里就层层深入,同步依赖对象。
这里就类似于同步A的时候,A依赖于B和C,然后停止同步A,先同步B和C,同步B或者C的时候,可能又依赖于其他对象,然后继续先同步其依赖对象。

基于Python的SQL Server数据库实现对象同步轻量级

效果如下

基于Python的SQL Server数据库实现对象同步轻量级

如果输入obj_type="sp" 且-obj=为None的情况下,会同步源数据库中的所有存储过程以及其依赖对象

已知的问题:

1,加密的存储过程或者函数是无法实现同步的,因为无法生成创建对象的脚本
1,table type的同步也是一个蛋疼的过程,目前支持,但是支持的并不好,原因是创建table type之前,先删除依赖于table type的对象,否则无法删除与创建。

特别说明

依赖对象的解决,还是比较蛋疼的

如果在默认schema为dbo的对象,在存储过程或者函数中没有写schema(参考如下修改后的sp,不写相关表的schema dbo,dbo.test01==>test01),
使用 sys.dm_sql_referenced_entities这个系统函数是无法找到其依赖的对象的,奇葩的是可以找到schema的类型,却没有返回对象本身。

这一点导致在代码中层层深入,进行了长时间的debug,完全没有想到这个函数是这个鸟样子,因为这里找到依赖对象的类型,却找不到对象本身,次奥!!!
另外一种情况就是动态SQL了,无法使用 sys.dm_sql_referenced_entities这个系统函数找到其依赖的对象。

基于Python的SQL Server数据库实现对象同步轻量级

其他对象的同步

  支持其他数据库对象的同步,比如function,table type等,因为可以在同步其他存储过程对象的时候附带的同步function,table type,这个与表或者存储过程类似,不做过多说明。 

已知问题:

1,201906122030:经测试,目前暂时不支持Sequence对象的同步。

需要改进的地方

1,代码结构优化,更加清晰和条例的结构(一开始用最直接简单粗暴的方式快速实现,后面重构了很多代码,现在自己看起来还有很多不舒服的痕迹)
2,数据同步的效率问题,对于多表的导入导出操作,依赖于单线程,多个大表导出串行的话,可能存在效率上的瓶颈,如何根据表的数据量,尽可能平均地分配多多个线程中,提升效率
3,更加友好清晰的异常提示以及日志记录,生成导出日志信息。
4,异构数据同步,MySQL《==》SQL Server《==》Oracle《==》PGSQL

代码端午节写好了,这几天抽空进行了一些测试以及bug fix,应该还潜在不少未知的bug,工作量比想象中的大的多了去了。

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

493

494

495

496

497

498

499

500

501

502

503

504

505

506

507

508

509

510

511

512

513

514

515

516

517

518

519

520

521

522

523

524

525

526

527

528

529

530

531

532

533

534

535

536

537

538

539

540

541

542

543

544

545

546

547

548

549

550

551

552

553

554

555

556

557

558

559

560

561

562

563

564

565

566

567

568

569

570

571

572

573

574

575

576

577

578

579

580

581

582

583

584

585

586

587

588

589

590

591

592

593

594

595

596

597

598

599

600

601

602

603

604

605

606

607

608

609

610

611

612

613

614

615

616

617

618

619

620

621

622

623

624

625

626

627

628

629

630

631

632

633

634

635

636

637

638

639

640

641

642

643

644

645

646

647

648

649

650

651

652

653

654

655

656

657

658

659

660

661

662

663

664

665

666

667

668

669

670

671

672

673

674

675

676

677

678

679

680

681

682

683

684

685

686

687

688

689

690

691

692

693

694

695

696

697

698

699

700

701

702

703

704

705

706

707

708

709

710

711

712

713

714

715

716

717

718

719

720

721

722

723

724

725

726

727

728

729

730

731

732

733

734

735

736

737

738

739

740

741

742

743

744

745

746

747

748

749

750

751

752

753

754

755

756

757

758

759

760

761

762

763

764

765

766

767

768

769

770

771

772

773

774

775

776

777

778

779

780

781

782

783

784

785

786

787

788

789

790

791

792

793

794

795

796

797

798

799

800

801

802

803

804

805

806

807

808

809

810

811

812

813

814

815

816

817

818

819

820

821

822

823

824

825

826

827

828

829

830

831

832

833

834

835

836

837

838

839

840

841

842

843

844

845

846

847

848

849

850

851

852

853

854

855

856

857

858

859

860

861

862

863

864

865

866

867

868

869

870

871

872

873

874

875

876

877

878

879

880

881

882

883

884

885

886

887

888

889

890

891

892

893

894

895

896

897

898

899

900

901

902

903

904

905

906

907

908

909

910

911

912

913

914

915

916

917

918

919

920

921

922

923

924

925

926

927

928

929

930

931

932

933

934

935

936

937

938

939

940

941

942

943

944

945

946

947

948

949

950

951

952

953

954

955

956

957

958

959

960

961

962

963

964

965

966

967

968

969

970

971

972

973

974

975

976

977

978

979

980

981

982

983

984

985

986

987

988

989

990

991

992

993

994

995

996

997

998

999

1000

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

1011

1012

1013

1014

1015

1016

1017

1018

1019

1020

1021

1022

1023

1024

1025

1026

1027

1028

1029

1030

1031

1032

1033

1034

1035

1036

1037

1038

1039

1040

1041

1042

1043

1044

1045

1046

1047

1048

1049

# -*- coding: utf-8 -*-

# !/usr/bin/env python3

__author__ = 'MSSQL123'

__date__ = '2019-06-07 09:36'

import os

import sys

import time

import datetime

import pymssql

from decimal import Decimal

usage = '''

—–parameter explain—–

source database parameter

-s_h : soure database host —– must require parameter

-s_i : soure database instace name —– default instance name MSSQL

-s_d : soure database name —– must require parameter

-s_u : soure database login —– default windows identifier

-s_p : soure database login password —– must require when s_u is not null

-s_P : soure database instance port —– default port 1433

target database parameter

-t_h : target database host —– must require parameter

-t_i : target database instace name —– default instance name MSSQL

-t_d : target database name —– must require parameter

-t_u : target database login —– default windows identifier

-t_p : target database login password —– must require when s_u is not null

-t_P : target database instance port —– default port 1433

sync object parameter

-obj_type : table or sp or function or other databse object —– tab or sp or fn or tp

-obj : table|sp|function|type name —– whick table or sp sync

overwirte parameter

-f : force overwirte target database object —– F or N

–help: help document

Example:

python DataTransfer.py -s_h=127.0.0.1 -s_P=1433 -s_i="MSSQL" -s_d="DB01" -obj_type="tab" -obj="dbo.t1,dbo.t2" -t_h=127.0.0.1 -t_P=1433 -t_i="MSSQL" -t_d="DB02" -f="Y"

python DataTransfer.py -s_h=127.0.0.1 -s_P=1433 -s_i="MSSQL" -s_d="DB01" -obj_type="sp" -obj="dbo.sp1,dbo.sp2" -t_h=127.0.0.1 -t_P=1433 -t_i="MSSQL" -t_d="DB02" -f="Y"

'''

class SyncDatabaseObject(object):

# source databse

s_h = None

s_i = None

s_P = None

s_u = None

s_p = None

s_d = None

# obj type

s_obj_type = None

# sync objects

s_obj = None

# target database

t_h = None

t_i = None

t_P = None

t_u = None

t_p = None

t_d = None

f = None

file_path = None

def __init__(self, *args, **kwargs):

for k, v in kwargs.items():

setattr(self, k, v)

# connect to sqlserver

def get_connect(self, _h, _i, _P, _u, _p, _d):

cursor = False

try:

if (_u) and (_p):

conn = pymssql.connect(host=_h,

server=_i,

port=_P,

user=_u,

password=_p,

database=_d)

else:

conn = pymssql.connect(host=_h,

server=_i,

port=_P,

database=_d)

if (conn):

return conn

except:

raise

return conn

# check connection

def validated_connect(self, _h, _i, _P, _u, _p, _d):

if not (self.get_connect(_h, _i, _P, _u, _p, _d)):

print("connect to " + str(_h) + " failed,please check you parameter")

exit(0)

'''

this is supposed to be a valid object name just like xxx_name,or dbo.xxx_name,or [schema].xxx_name or schema.[xxx_name]

then transfer this kind of valid object name to format object name like [dbo].[xxx_name](give a default dbo schema name when no schema name)

other format object name consider as unvalid,will be rasie error in process

format object name

1,xxx_name ======> [dbo].[xxx_name]

2,dbo.xxx_name ======> [dbo].[xxx_name]

3,[schema].xxx_name ======> [dbo].[xxx_name]

3,schema.xxx_name ======> [schema].[xxx_name]

4,[schema].[xxx_name] ======> [schema].[xxx_name]

5,[schema].[xxx_name ======> rasie error format message

'''

@staticmethod

def format_object_name(name):

format_name = ""

if ("." in name):

schema_name = name[0:name.find(".")]

object_name = name[name.find(".") + 1:]

if not ("[" in schema_name):

schema_name = "[" + schema_name + "]"

if not ("[" in object_name):

object_name = "[" + object_name + "]"

format_name = schema_name + "." + object_name

else:

if ("[" in name):

format_name = "[dbo]." + name

else:

format_name = "[dbo]." + "[" + name + "]"

return format_name

'''

check user input object is a valid object

'''

def exits_object(self, conn, name):

conn = conn

cursor_source = conn.cursor()

# get object by name from source db

sql_script = r'''select top 1 1 from

(

select concat(QUOTENAME(schema_name(schema_id)),'.',QUOTENAME(name)) as obj_name from sys.objects

union all

select concat(QUOTENAME(schema_name(schema_id)),'.',QUOTENAME(name)) as obj_name from sys.types

)t where obj_name = '{0}'

'''.format(self.format_object_name(name))

cursor_source.execute(sql_script)

result = cursor_source.fetchall()

if not result:

return 0

else:

return 1

conn.cursor.close()

conn.close()

# table variable sync

def sync_table_variable(self, tab_name, is_reference):

conn_source = self.get_connect(self.s_h, self.s_i, self.s_P, self.s_u, self.s_p, self.s_d)

conn_target = self.get_connect(self.t_h, self.t_i, self.t_P, self.t_u, self.t_p, self.t_d)

cursor_source = conn_source.cursor()

cursor_target = conn_target.cursor()

if (self.exits_object(conn_source, self.format_object_name(tab_name))) > 0:

pass

else:

print("———————– warning message ———————–")

print("——–warning: object " + tab_name + " not existing in source database ————")

print("———————– warning message ———————–")

print()

return

exists_in_target = 0

sql_script = r'''select top 1 1

from sys.table_types tp

where is_user_defined = 1

and concat(QUOTENAME(schema_name(tp.schema_id)),'.',QUOTENAME(tp.name)) = '{0}' ''' \\

.format((self.format_object_name(tab_name)))

# if the table schema exists in target server,skip

cursor_target.execute(sql_script)

exists_in_target = cursor_target.fetchone()

# weather exists in target server database

if (self.f == "Y"):

if (is_reference != "Y"):

# skiped,table type can not drop when used by sp

sql_script = r'''

if OBJECT_ID('{0}') is not null

drop type {0}

'''.format(self.format_object_name(tab_name))

cursor_target.execute(sql_script)

conn_target.commit()

else:

if exists_in_target:

print("———————– warning message ———————–")

print("the target table type " + tab_name + " exists ,skiped sync table type from source")

print("———————– warning message ———————–")

print()

return

sql_script = r'''

DECLARE @SQL NVARCHAR(MAX) = ''

SELECT @SQL =

'CREATE TYPE ' + '{0}' + 'AS TABLE' + CHAR(13) + '(' + CHAR(13) +

STUFF((

SELECT CHAR(13) + ' , [' + c.name + '] ' +

CASE WHEN c.is_computed = 1

THEN 'AS ' + OBJECT_DEFINITION(c.[object_id], c.column_id)

ELSE

CASE WHEN c.system_type_id != c.user_type_id

THEN '[' + SCHEMA_NAME(tp.[schema_id]) + '].[' + tp.name + ']'

ELSE '[' + UPPER(y.name) + ']'

END +

CASE

WHEN y.name IN ('varchar', 'char', 'varbinary', 'binary')

THEN '(' + CASE WHEN c.max_length = -1

THEN 'MAX'

ELSE CAST(c.max_length AS VARCHAR(5))

END + ')'

WHEN y.name IN ('nvarchar', 'nchar')

THEN '(' + CASE WHEN c.max_length = -1

THEN 'MAX'

ELSE CAST(c.max_length / 2 AS VARCHAR(5))

END + ')'

WHEN y.name IN ('datetime2', 'time2', 'datetimeoffset')

THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'

WHEN y.name = 'decimal'

THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'

ELSE ''

END +

CASE WHEN c.collation_name IS NOT NULL AND c.system_type_id = c.user_type_id

THEN ' COLLATE ' + c.collation_name

ELSE ''

END +

CASE WHEN c.is_nullable = 1

THEN ' NULL'

ELSE ' NOT NULL'

END +

CASE WHEN c.default_object_id != 0

THEN ' CONSTRAINT [' + OBJECT_NAME(c.default_object_id) + ']' +

' DEFAULT ' + OBJECT_DEFINITION(c.default_object_id)

ELSE ''

END

END

From sys.table_types tp

Inner join sys.columns c on c.object_id = tp.type_table_object_id

Inner join sys.types y ON y.system_type_id = c.system_type_id

WHERE tp.is_user_defined = 1 and y.name<>'sysname'

and concat(QUOTENAME(schema_name(tp.schema_id)),'.',QUOTENAME(tp.name)) = '{0}'

ORDER BY c.column_id

FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 7, ' ')

+ ');'

select @SQL as script

'''.format(self.format_object_name(self.format_object_name((tab_name))))

cursor_target = conn_target.cursor()

cursor_source.execute(sql_script)

row = cursor_source.fetchone()

try:

if not exists_in_target:

# execute the script on target server

cursor_target.execute(str(row[0])) # drop current stored_procudre if exists

conn_target.commit()

print("*************table type " + self.format_object_name(tab_name) + " synced *********************")

print() # give a blank row when finish

except:

print("———————– error message ———————–")

print("———–table type " + self.format_object_name(tab_name) + " synced error —————")

print("———————– error message ———————–")

print()

# raise

cursor_source.close()

conn_source.close()

cursor_target.close()

conn_target.close()

# schema sync

def sync_schema(self):

conn_source = self.get_connect(self.s_h, self.s_i, self.s_P, self.s_u, self.s_p, self.s_d)

conn_target = self.get_connect(self.t_h, self.t_i, self.t_P, self.t_u, self.t_p, self.t_d)

cursor_source = conn_source.cursor()

cursor_target = conn_target.cursor()

arr_schema = []

# get all table in database when not define table name

schema_result = cursor_source.execute(r'''

select name from sys.schemas where schema_id>4 and schema_id<16384

''')

for row in cursor_source.fetchall():

cursor_target.execute(r''' if not exists(select * from sys.schemas where name = '{0}')

begin

exec('create schema [{0}]')

end

'''.format(str(row[0])))

conn_target.commit()

cursor_source.close()

conn_source.close()

cursor_target.close()

conn_target.close()

def sync_table_schema_byname(self, tab_name, is_reference):

conn_source = self.get_connect(self.s_h, self.s_i, self.s_P, self.s_u, self.s_p, self.s_d)

conn_target = self.get_connect(self.t_h, self.t_i, self.t_P, self.t_u, self.t_p, self.t_d)

cursor_source = conn_source.cursor()

cursor_target = conn_target.cursor()

if (self.exits_object(conn_source, self.format_object_name(tab_name)) == 0):

print("———————– warning message ———————–")

print("—————warning: object " + tab_name + " not existing in source database —————-")

print("———————– warning message ———————–")

print()

return

# if exists a reference table for sp,not sync the table agagin

if (self.exits_object(conn_target, self.format_object_name(tab_name)) > 0):

if (self.f != "Y"):

print("———————– warning message ———————–")

print("—————warning: object " + tab_name + " existing in target database —————-")

print("———————– warning message ———————–")

print()

return

sql_script = r''' select top 1 1 from sys.tables

where type_desc = 'USER_TABLE'

and concat(QUOTENAME(schema_name(schema_id)),'.',QUOTENAME(name)) = '{0}'

'''.format((self.format_object_name(tab_name)))

# if the table schema exists in target server,skip

cursor_target.execute(sql_script)

exists_in_target = cursor_target.fetchone()

if exists_in_target:

if (self.f == "Y"):

if (is_reference != "Y"):

cursor_target.execute("drop table {0}".format(tab_name))

else:

print("———————– warning message ———————–")

print("the target table " + tab_name + " exists ,skiped sync table schema from source")

print("———————– warning message ———————–")

print()

return

sql_script = r''' DECLARE

@object_name SYSNAME

, @object_id INT

SELECT

@object_name = '[' + s.name + '].[' + o.name + ']'

, @object_id = o.[object_id]

FROM sys.objects o WITH (NOWAIT)

JOIN sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id]

WHERE QUOTENAME(s.name) + '.' + QUOTENAME(o.name) = '{0}'

AND o.[type] = 'U'

AND o.is_ms_shipped = 0

DECLARE @SQL NVARCHAR(MAX) = ''

;WITH index_column AS

(

SELECT

ic.[object_id]

, ic.index_id

, ic.is_descending_key

, ic.is_included_column

, c.name

FROM sys.index_columns ic WITH (NOWAIT)

JOIN sys.columns c WITH (NOWAIT) ON ic.[object_id] = c.[object_id] AND ic.column_id = c.column_id

WHERE ic.[object_id] = @object_id

),

fk_columns AS

(

SELECT

k.constraint_object_id

, cname = c.name

, rcname = rc.name

FROM sys.foreign_key_columns k WITH (NOWAIT)

JOIN sys.columns rc WITH (NOWAIT) ON rc.[object_id] = k.referenced_object_id AND rc.column_id = k.referenced_column_id

JOIN sys.columns c WITH (NOWAIT) ON c.[object_id] = k.parent_object_id AND c.column_id = k.parent_column_id

WHERE k.parent_object_id = @object_id

)

SELECT @SQL = 'CREATE TABLE ' + @object_name + '' + '(' + '' + STUFF((

SELECT '' + ', [' + c.name + '] ' +

CASE WHEN c.is_computed = 1

THEN 'AS ' + cc.[definition]

ELSE UPPER(tp.name) +

CASE WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary', 'text')

THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(5)) END + ')'

WHEN tp.name IN ('nvarchar', 'nchar')

THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length / 2 AS VARCHAR(5)) END + ')'

WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset')

THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'

WHEN tp.name = 'decimal'

THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'

ELSE ''

END +

CASE WHEN c.collation_name IS NOT NULL THEN ' COLLATE ' + c.collation_name ELSE '' END +

CASE WHEN c.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END +

CASE WHEN dc.[definition] IS NOT NULL THEN ' DEFAULT' + dc.[definition] ELSE '' END +

CASE WHEN ic.is_identity = 1 THEN ' IDENTITY(' + CAST(ISNULL( /*ic.seed_value*/ 1, '0') AS CHAR(1)) + ',' + CAST(ISNULL(ic.increment_value, '1') AS CHAR(1)) + ')' ELSE '' END

END + ''

FROM sys.columns c WITH (NOWAIT)

JOIN sys.types tp WITH (NOWAIT) ON c.user_type_id = tp.user_type_id

LEFT JOIN sys.computed_columns cc WITH (NOWAIT) ON c.[object_id] = cc.[object_id] AND c.column_id = cc.column_id

LEFT JOIN sys.default_constraints dc WITH (NOWAIT) ON c.default_object_id != 0 AND c.[object_id] = dc.parent_object_id AND c.column_id = dc.parent_column_id

LEFT JOIN sys.identity_columns ic WITH (NOWAIT) ON c.is_identity = 1 AND c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id

WHERE c.[object_id] = @object_id

ORDER BY c.column_id

FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '' + ' ')

+ ISNULL((SELECT '' + ', CONSTRAINT [' + k.name + '] PRIMARY KEY (' +

(SELECT STUFF((

SELECT ', [' + c.name + '] ' + CASE WHEN ic.is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END

FROM sys.index_columns ic WITH (NOWAIT)

JOIN sys.columns c WITH (NOWAIT) ON c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id

WHERE ic.is_included_column = 0

AND ic.[object_id] = k.parent_object_id

AND ic.index_id = k.unique_index_id

FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ''))

+ ')' + ''

FROM sys.key_constraints k WITH (NOWAIT)

WHERE k.parent_object_id = @object_id

AND k.[type] = 'PK'), '') + ')' + ''

+ ISNULL((SELECT (

SELECT '' +

'ALTER TABLE ' + @object_name + ' WITH'

+ CASE WHEN fk.is_not_trusted = 1

THEN ' NOCHECK'

ELSE ' CHECK'

END +

' ADD CONSTRAINT [' + fk.name + '] FOREIGN KEY('

+ STUFF((

SELECT ', [' + k.cname + ']'

FROM fk_columns k

WHERE k.constraint_object_id = fk.[object_id] and 1=2

FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

+ ')' +

' REFERENCES [' + SCHEMA_NAME(ro.[schema_id]) + '].[' + ro.name + '] ('

+ STUFF((

SELECT ', [' + k.rcname + ']'

FROM fk_columns k

WHERE k.constraint_object_id = fk.[object_id]

FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

+ ')'

+ CASE

WHEN fk.delete_referential_action = 1 THEN ' ON DELETE CASCADE'

WHEN fk.delete_referential_action = 2 THEN ' ON DELETE SET NULL'

WHEN fk.delete_referential_action = 3 THEN ' ON DELETE SET DEFAULT'

ELSE ''

END

+ CASE

WHEN fk.update_referential_action = 1 THEN ' ON UPDATE CASCADE'

WHEN fk.update_referential_action = 2 THEN ' ON UPDATE SET NULL'

WHEN fk.update_referential_action = 3 THEN ' ON UPDATE SET DEFAULT'

ELSE ''

END

+ '' + 'ALTER TABLE ' + @object_name + ' CHECK CONSTRAINT [' + fk.name + ']' + ''

FROM sys.foreign_keys fk WITH (NOWAIT)

JOIN sys.objects ro WITH (NOWAIT) ON ro.[object_id] = fk.referenced_object_id

WHERE fk.parent_object_id = @object_id

FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)')), '')

+ ISNULL(((SELECT

'' + 'CREATE' + CASE WHEN i.is_unique = 1 THEN ' UNIQUE' ELSE '' END

+ ' NONCLUSTERED INDEX [' + i.name + '] ON ' + @object_name + ' (' +

STUFF((

SELECT ', [' + c.name + ']' + CASE WHEN c.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END

FROM index_column c

WHERE c.is_included_column = 0

AND c.index_id = i.index_id

FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')'

+ ISNULL('' + 'INCLUDE (' +

STUFF((

SELECT ', [' + c.name + ']'

FROM index_column c

WHERE c.is_included_column = 1

AND c.index_id = i.index_id

FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')', '') + ''

FROM sys.indexes i WITH (NOWAIT)

WHERE i.[object_id] = @object_id

AND i.is_primary_key = 0

AND i.[type] = 2

FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')

), '')

select @SQL as script '''.format(self.format_object_name(tab_name))

cursor_target = conn_target.cursor()

cursor_source.execute(sql_script)

row = cursor_source.fetchone()

if not row[0]:

return

try:

cursor_target.execute(row[0]) # drop current table schema if exists

conn_target.commit()

print("*************schema " + self.format_object_name(tab_name) + " synced *************")

print() # give a blank row when finish

except:

print("———————– warning message ———————–")

print("———–schema " + self.format_object_name(tab_name) + " synced failed—————")

print("———————– warning message ———————–")

print()

cursor_source.close()

conn_source.close()

cursor_target.close()

conn_target.close()

def get_table_column(self, conn, tab_name):

column_names = ""

conn = conn

cursor_source = conn.cursor()

# get object by name from source db

sql_script = r'''select name from sys.columns

where object_id = object_id('{0}') and is_computed=0 order by object_id

'''.format(self.format_object_name(tab_name))

cursor_source.execute(sql_script)

result = cursor_source.fetchall()

for row in result:

column_names = column_names + row[0] + ","

return column_names[0:len(column_names) – 1]

conn.cursor.close()

conn.close()

def sync_table_schema(self):

#default not sync by referenced other object

is_reference = "N"

conn_source = self.get_connect(self.s_h, self.s_i, self.s_P, self.s_u, self.s_p, self.s_d)

conn_target = self.get_connect(self.t_h, self.t_i, self.t_P, self.t_u, self.t_p, self.t_d)

cursor_source = conn_source.cursor()

cursor_target = conn_target.cursor()

arr_table = []

if (self.s_obj):

for tab_name in self.s_obj.split(","):

if (tab_name) and (self.exits_object(conn_source, tab_name)>0):

self.sync_table_schema_byname(tab_name, is_reference)

else:

print("———————– warning message ———————–")

print("———–schema " + self.format_object_name(tab_name) + " not existing in source database—————")

print("———————– warning message ———————–")

print()

else:

# sync all tables

# get all table in database when not define table name

sql_script = ''' SELECT QUOTENAME(s.name)+'.'+ QUOTENAME(o.name)

FROM sys.objects o WITH (NOWAIT)

JOIN sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id]

WHERE o.[type] = 'U' AND o.is_ms_shipped = 0

'''

cursor_source.execute(sql_script)

for row in cursor_source.fetchall():

self.sync_table_schema_byname(str(row[0]), is_reference)

# sync data from soure table to target table

def sync_table_data(self):

conn_source = self.get_connect(self.s_h, self.s_i, self.s_P, self.s_u, self.s_p, self.s_d)

conn_target = self.get_connect(self.t_h, self.t_i, self.t_P, self.t_u, self.t_p, self.t_d)

cursor_source = conn_source.cursor()

cursor_target = conn_target.cursor()

arr_table = []

if (self.s_obj):

arr_table = self.s_obj.split(',')

for tab_name in arr_table:

if (self.exits_object(conn_target, self.format_object_name(tab_name)) == 0):

arr_table.remove(tab_name)

print("—————– warning message ———————–")

print("—————– warning: table " + tab_name + " not existing in target database ———————")

print("—————– warning message ———————–")

else:

# get all table in database when not define table name

tab_result = cursor_source.execute(r''' SELECT QUOTENAME(s.name)+'.'+ QUOTENAME(o.name)

FROM sys.objects o WITH (NOWAIT)

JOIN sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id]

WHERE o.[type] = 'U'

AND o.is_ms_shipped = 0

''')

for row in cursor_source.fetchall():

arr_table.append(str(row[0]))

insert_columns = ""

insert_columns = self.get_table_column(conn_source, tab_name)

for tab_name in arr_table:

if (self.f != "Y"):

sql_script = "select top 1 {0} from {1} ".format(insert_columns, tab_name)

# if exists data in target table,break

cursor_target.execute(sql_script)

exists = cursor_target.fetchone()

if exists:

print("———————– warning message ———————–")

print("the target table " + tab_name + " exists data,skiped sync table type from source")

print("———————– warning message ———————–")

print()

continue

else:

sql_script = "truncate table {0} ".format(tab_name)

# if exists data in target table,break

cursor_target.execute(sql_script)

conn_target.commit()

insert_columns = ""

insert_columns = self.get_table_column(conn_source, tab_name)

insert_prefix = ""

# weather has identity column

cursor_source.execute(r'''select 1 from sys.columns

where object_id = OBJECT_ID('{0}') and is_identity =1

'''.format(tab_name))

exists_identity = None

exists_identity = cursor_source.fetchone()

if (exists_identity):

insert_prefix = "set identity_insert {0} on; ".format(tab_name)

# data source

insert_sql = ""

values_sql = ""

current_row = ""

counter = 0

sql_script = r''' select {0} from {1} '''.format(insert_columns, tab_name)

cursor_source.execute(sql_script)

# create insert columns

'''

for field in cursor_source.description:

insert_columns = insert_columns + str(field[0]) + ","

insert_columns = insert_columns[0:len(insert_columns) – 1]

'''

insert_prefix = insert_prefix + "insert into {0} ({1}) values ".format(tab_name, insert_columns)

for row in cursor_source.fetchall():

counter = counter + 1

for key in row:

if (str(key) == "None"):

current_row = current_row + r''' null, '''

else:

if (type(key) is datetime.datetime):

current_row = current_row + r''' '{0}', '''.format(str(key)[0:23])

elif (type(key) is str):

# 我槽!!!,这里又有一个坑:https://blog.csdn.net/dadaowuque/article/details/81016127

current_row = current_row + r''' '{0}', '''.format(

key.replace("'", "''").replace('\\u0000', '').replace('\\x00', ''))

elif (type(key) is Decimal):

d = Decimal(key)

s = '{0:f}'.format(d)

current_row = current_row + r''' '{0}', '''.format(s)

elif (type(key) is bytes):

# print(hex(int.from_bytes(key, 'big', signed=True) ))

current_row = current_row + r''' {0}, '''.format(

hex(int.from_bytes(key, 'big', signed=False)))

else:

current_row = current_row + r''' '{0}', '''.format(key)

current_row = current_row[0:len(current_row) – 2] # remove the the last one char ","

values_sql = values_sql + "(" + current_row + "),"

current_row = ""

# execute the one batch when

if (counter == 1000):

insert_sql = insert_prefix + values_sql

insert_sql = insert_sql[0:len(insert_sql) – 1] # remove the the last one char ","

if (exists_identity):

insert_sql = insert_sql + " ;set identity_insert {0} off;".format(tab_name)

try:

cursor_target.execute(insert_sql)

except:

print(

"———————-error " + tab_name + " data synced failed————————-")

raise

conn_target.commit()

insert_sql = ""

values_sql = ""

current_row = ""

counter = 0

print(time.strftime("%Y-%m-%d %H:%M:%S",

time.localtime()) + "*************** " + self.format_object_name(

tab_name) + " " + str(1000) + " rows synced *************")

if (values_sql):

insert_sql = insert_prefix + values_sql

insert_sql = insert_sql[0:len(insert_sql) – 1] # remove the the last one char ","

if (exists_identity):

insert_sql = insert_sql + " ; set identity_insert {0} off;".format(tab_name)

# execute the last batch

try:

cursor_target.execute(insert_sql)

except:

print("——————error " + tab_name + " data synced failed————————")

raise

conn_target.commit()

insert_sql = ""

values_sql = ""

current_row = ""

print(time.strftime("%Y-%m-%d %H:%M:%S",

time.localtime()) + "*************** " + self.format_object_name(

tab_name) + " " + str(

counter) + " rows synced *************")

print(time.strftime("%Y-%m-%d %H:%M:%S",

time.localtime()) + "—————-synced " + self.format_object_name(

tab_name) + " data finished—————")

print()

cursor_source.close()

conn_source.close()

cursor_target.close()

conn_target.close()

def sync_dependent_object(self, obj_name):

# 强制覆盖,不需要对依赖对象生效,如果是因为属于依赖对象而被同步的,先检查target中是否存在,如果存在就不继续同步,这里打一个标记来实现

is_refernece = "Y"

conn_source = self.get_connect(self.s_h, self.s_i, self.s_P, self.s_u, self.s_p, self.s_d)

conn_target = self.get_connect(self.t_h, self.t_i, self.t_P, self.t_u, self.t_p, self.t_d)

cursor_source = conn_source.cursor()

cursor_target = conn_target.cursor()

'''

find dependent objects

if exists dependent objects,sync Dependent objects objects in advance

'''

sql_check_dependent = r'''

SELECT * FROM

(

SELECT

distinct rtrim(lower(s.type)) COLLATE Chinese_PRC_CI_AS as obj_type,

QUOTENAME(d.referenced_schema_name)+'.'+QUOTENAME(d.referenced_entity_name) COLLATE Chinese_PRC_CI_AS as obj

FROM sys.dm_sql_referenced_entities('{0}','OBJECT') as d

inner join sys.sysobjects s on s.id = d.referenced_id

union all

SELECT

distinct rtrim(lower(d.referenced_class_desc)) COLLATE Chinese_PRC_CI_AS as obj_type,

QUOTENAME(d.referenced_schema_name)+'.'+QUOTENAME(d.referenced_entity_name) COLLATE Chinese_PRC_CI_AS as obj

FROM sys.dm_sql_referenced_entities('{0}','OBJECT') as d

inner join sys.types s on s.user_type_id = d.referenced_id

)t

'''.format(self.format_object_name(obj_name))

cursor_source.execute(sql_check_dependent)

result = cursor_source.fetchall()

for row in result:

if row[1]:

if (row[0] == "u"):

if (row[1]):

self.sync_table_schema_byname(row[1], is_refernece)

elif (row[0] == "fn" or row[0] == "if"):

if (row[1]):

self.sync_procudre_by_name("f", row[1], is_refernece)

elif (row[0] == "type"):

if (row[1]):

self.sync_table_variable(row[1], is_refernece)

def sync_procudre_by_name(self, type, obj_name, is_reference):

conn_source = self.get_connect(self.s_h, self.s_i, self.s_P, self.s_u, self.s_p, self.s_d)

conn_target = self.get_connect(self.t_h, self.t_i, self.t_P, self.t_u, self.t_p, self.t_d)

cursor_source = conn_source.cursor()

cursor_target = conn_target.cursor()

if (self.exits_object(conn_source, self.format_object_name(obj_name)) == 0):

print("—————warning message—————-")

print("—————warning: object " + obj_name + " not existing in source database —————-")

print("—————warning message—————-")

print()

return

if (self.exits_object(conn_target, self.format_object_name(obj_name)) > 0):

if (self.f != "Y"):

print("—————warning message—————-")

print("—————warning: object " + obj_name + " existing in target database —————-")

print("—————warning message—————-")

print()

return

'''

本来想直接生成删除语句的:

这里有一个该死的转义,怎么都弄不好,中午先去吃饭吧,

下午回来想了一下,换一种方式,不要死磕转义问题了

sql_script =

select

'if object_id('+''''+QUOTENAME(schema_name(uid))+ '' + QUOTENAME(name)+''''+') is not null '

+' drop proc '+QUOTENAME(schema_name(uid))+ '.' + QUOTENAME(name) ,

OBJECT_DEFINITION(id)

from sys.sysobjects where xtype = 'P' and uid not in (16,19)

'''

sql_script = r'''

select

QUOTENAME(schema_name(uid))+'.'+QUOTENAME(name),

OBJECT_DEFINITION(id)

from sys.sysobjects where xtype in ('P','IF','FN') and uid not in (16,19)

'''

if (obj_name):

sql_script = sql_script + " and QUOTENAME(schema_name(uid))+ '.' + QUOTENAME(name) ='{0}' ".format(

self.format_object_name(obj_name))

cursor_source.execute(sql_script)

row = cursor_source.fetchone()

try:

if type == "f":

sql_script = r'''

if object_id('{0}') is not null

drop function {0}

'''.format(self.format_object_name(row[0]))

elif type == "p":

sql_script = r'''

if object_id('{0}') is not null

drop proc {0}

'''.format(self.format_object_name(row[0]))

cursor_target.execute(sql_script) # drop current stored_procudre if exists

conn_target.commit()

# sync dependent object

if (is_reference != "N"):

self.sync_dependent_object(self.format_object_name(row[0]))

# sync object it self

cursor_target.execute(str(row[1])) # execute create stored_procudre script

conn_target.commit()

print("*************sync sp: " + self.format_object_name(row[0]) + " finished *****************")

print()

except:

print("—————error message—————-")

print("—————— sync " + row[0] + "sp error ————————–")

print("—————error message—————-")

print()

cursor_source.close()

conn_source.close()

cursor_target.close()

conn_target.close()

def sync_procudre(self, type):

is_reference = "N"

conn_source = self.get_connect(self.s_h, self.s_i, self.s_P, self.s_u, self.s_p, self.s_d)

conn_target = self.get_connect(self.t_h, self.t_i, self.t_P, self.t_u, self.t_p, self.t_d)

cursor_source = conn_source.cursor()

cursor_target = conn_target.cursor()

if (self.s_obj):

for proc_name in self.s_obj.split(","):

self.sync_dependent_object(proc_name)

self.sync_procudre_by_name(type, proc_name, is_reference)

# sync all sp and function

else:

sql_script = r'''

select

QUOTENAME(schema_name(uid))+'.'+QUOTENAME(name),

OBJECT_DEFINITION(id)

from sys.sysobjects where xtype = upper('{0}') and uid not in (16,19)

'''.format(type)

cursor_source.execute(sql_script)

for row in cursor_source.fetchall():

self.sync_dependent_object(row[0])

self.sync_procudre_by_name(type, row[0], is_reference)

if __name__ == "__main__":

'''

sync = SyncDatabaseObject(s_h="127.0.0.1",

s_i = "sql2017",

s_P = 49744,

s_d="DB01",

t_h="127.0.0.1",

t_i="sql2017",

t_P=49744,

t_d="DB02",

s_obj_type = "sp",

s_obj = "dbo.sp_test01",

f="Y")

sync.sync_procudre("p")

'''

p_s_h = ""

p_s_i = "MSSQL"

p_s_P = 1433

p_s_d = ""

p_s_u = None

p_s_p = None

p_s_obj = ""

p_type = ""

p_t_s = ""

p_t_i = "MSSQL"

p_t_P = "1433"

p_t_d = ""

p_t_u = None

p_t_p = None

# force conver target database object,default not force cover target database object

p_f = "N"

# sync obj type table|sp

p_obj_type = None

# sync whick database object

p_obj = None

if len(sys.argv) == 1:

print(usage)

sys.exit(1)

elif sys.argv[1] == '–help':

print(usage)

sys.exit()

elif len(sys.argv) >= 2:

for i in sys.argv[1:]:

_argv = i.split('=')

# source server name

if _argv[0] == '-s_h':

p_s_h = _argv[1]

# source server instance name

if _argv[0] == '-s_i':

if (_argv[1]):

p_s_i = _argv[1]

# source server instance PORT

if _argv[0] == '-s_P':

if (_argv[1]):

p_s_P = _argv[1]

# source database name

if _argv[0] == '-s_d':

p_s_d = _argv[1]

if _argv[0] == '-s_u':

p_s_u = _argv[1]

if _argv[0] == '-s_p':

p_s_p = _argv[1]

if _argv[0] == '-t_h':

p_t_h = _argv[1]

if _argv[0] == '-t_i':

if (_argv[1]):

p_t_i = _argv[1]

if _argv[0] == '-t_P':

if (_argv[1]):

p_t_P = _argv[1]

if _argv[0] == '-t_d':

p_t_d = _argv[1]

if _argv[0] == '-t_u':

p_t_u = _argv[1]

if _argv[0] == '-t_p':

p_t_p = _argv[1]

if _argv[0] == '-f':

if (_argv[1]):

p_f = _argv[1]

# object type

if _argv[0] == '-obj_type':

if not (_argv[1]):

print("-obj_type can not be null (-obj=tab|-obj=sp|-obj=fn|-obj=type)")

exit(0)

else:

p_obj_type = _argv[1]

# object name

if _argv[0] == '-obj':

if (_argv[1]):

p_obj = _argv[1]

# require para

if p_s_h.strip() == "":

print("source server host cannot be null")

exit(0)

if p_s_d.strip() == "":

print("source server host database name cannot be null")

exit(0)

if p_t_h.strip() == "":

print("target server host cannot be null")

exit(0)

if p_t_d.strip() == "":

print("target server host database name cannot be null")

exit(0)

sync = SyncDatabaseObject(s_h=p_s_h,

s_i=p_s_i,

s_P=p_s_P,

s_d=p_s_d,

s_u=p_s_u,

s_p=p_s_p,

s_obj=p_obj,

t_h=p_t_h,

t_i=p_t_i,

t_P=p_t_P,

t_d=p_t_d,

t_u=p_t_u,

t_p=p_t_p,

f=p_f)

sync.validated_connect(p_s_h, p_s_i, p_s_P, p_s_d, p_s_u, p_s_p)

sync.validated_connect(p_t_h, p_t_i, p_t_P, p_t_d, p_t_u, p_t_p)

if (p_f.upper() == "Y"):

confirm = input("confirm you want to overwrite the target object? ")

if confirm.upper() != "Y":

exit(0)

print("————————– sync begin ———————————-")

print()

if (p_obj_type == "tab"):

# sync schema

sync.sync_schema()

# sync table schema

sync.sync_table_schema()

# sync data

sync.sync_table_data()

elif (p_obj_type == "sp"):

# sync schema

sync.sync_schema()

# sync sp

sync.sync_procudre("p")

elif (p_obj_type == "fn"):

# sync schema

sync.sync_schema()

# sync sp

sync.sync_procudre("fn")

elif (p_obj_type == "tp"):

# sync schema

sync.sync_schema()

# sync sp

sync.sync_table_variable()

else:

print("-obj_type is not validated")

print()

print("————————– sync finish ———————————-")

总结

以上所述是小编给大家介绍的基于Python的SQL Server数据库实现对象同步轻量级,希望对大家有所帮助,如果大家有任何疑问欢迎给我留言,小编会及时回复大家的!

原文链接:https://www.cnblogs.com/wy123/archive/2019/06/12/10991343.html

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/24488/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022年9月22日 上午11:17
下一篇 2022年9月22日 上午11:17
软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!