1""" 

2@file 

3 

4@brief generic class to access a SQL database 

5""" 

6 

7import os 

8 

9 

10from pyquickhelper.loghelper.flog import run_cmd 

11 

12 

13def get_list_server(): 

14 """ 

15 @return a list of the available servers from this machine 

16 """ 

17 cmd = "sqlcmd -Lc" 

18 out, err = run_cmd(cmd, wait=True, shell=True, log_error=False) 

19 line = out.split("\n") 

20 line = [_.strip("\r\n ") for _ in line] 

21 line = [_ for _ in line if len(_) > 0] 

22 

23 # we place all server containing the machine name in the first place 

24 machine = os.environ.get( 

25 "COMPUTERNAME", os.environ.get("HOSTNAME", "--------")) 

26 line = sorted([(-1 if _.startswith(machine) else 0, _) for _ in line]) 

27 line = [_[1] for _ in line] 

28 return line 

29 

30 

31def get_list_instance(server): 

32 """ 

33 @param server server name 

34 @return a list of the available instance on a server 

35 """ 

36 cmd = 'sqlcmd -S %s -Q "SELECT @@ServerName"' % server 

37 out, err = run_cmd(cmd, wait=True, shell=True, log_error=False) 

38 if len(err) > 0: 

39 cmd = 'sqlcmd -S %s\\SQLEXPRESS -Q "SELECT @@ServerName"' % server 

40 out, err = run_cmd(cmd, wait=True, shell=True, log_error=False) 

41 if len(err) > 0: 

42 raise Exception( 

43 "unable to find instances for server %s (%s)" % 

44 (server, err)) 

45 

46 li = [_.strip("\r\n ") for _ in out.split("\n")] 

47 li = [_ for _ in li if len(_) > 0] 

48 li = li[1:-1] 

49 return li 

50 

51 

52def get_list_database(instance): 

53 """ 

54 @param instance instance name 

55 @return a list of the available database 

56 """ 

57 cmd = 'sqlcmd -S %s -Q "SELECT name FROM master..sysdatabases"' % instance 

58 out, err = run_cmd(cmd, wait=True, shell=True, log_error=False) 

59 li = [_.strip("\r\n ") for _ in out.split("\n")] 

60 no = ["master", "tempdb", "model", "msdb"] 

61 li = [_ for _ in li if len(_) > 0 and _ not in no] 

62 li = li[2:-1] 

63 return li 

64 

65 

66def create_database(instance, database, exc=True): 

67 """ 

68 create a database 

69 @param instance instance name 

70 @param database database name 

71 @param exc if True and if the database exists, raise an Exception 

72 

73 Example: 

74 @code 

75 fLOG (OutputPrint = True) 

76 fLOG (get_list_database ('PCXAVIER\\SQLEXPRESS')) 

77 create_database ('PCXAVIER\\SQLEXPRESS', "essai_database") 

78 drop_database ('PCXAVIER\\SQLEXPRESS', "essai_database") 

79 @endcode 

80 """ 

81 ins = get_list_database(instance) 

82 if database in ins: 

83 if exc: 

84 raise Exception("database %s already exists" % database) 

85 else: 

86 cmd = 'sqlcmd -S %s -Q "CREATE DATABASE %s"' % (instance, database) 

87 out, err = run_cmd(cmd, wait=True, shell=True, log_error=False) 

88 if len(err) > 0: 

89 raise Exception("error: %s" % err) 

90 

91 

92def drop_database(instance, database, exc=True): 

93 """ 

94 remove a database 

95 @param instance instance name 

96 @param database database name 

97 @param exc if True and if the database does not exist, raise an Exception 

98 """ 

99 ins = get_list_database(instance) 

100 if database not in ins: 

101 if exc: 

102 raise Exception("database %s does not exist" % database) 

103 else: 

104 cmd = 'sqlcmd -S %s -Q "DROP DATABASE %s"' % (instance, database) 

105 out, err = run_cmd(cmd, wait=True, shell=True, log_error=False) 

106 if len(err) > 0: 

107 raise Exception("error: %s" % err)