Hot-keys on this page
r m x p toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1"""
2@file
4@brief generic class to access a SQL database
5"""
7import os
10from pyquickhelper.loghelper.flog import run_cmd
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]
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
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))
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
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
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
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)
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)