def loadDB(content): global user global pwd global port global database global server if user == "": c = windows.readConfig() user = c[0] pwd = c[1] p = c[2] database = c[3] server = c[4] path = getPath() + "bin\\" process = Popen("\"" + path + "mysql\" -u " + user + " -p" + pwd + " -h" + server + " --port " + port + " --default-character-set=utf8 " + database, shell=True, stdin=PIPE) process.stdin.write(content) process.stdin.close() process.kill()
def backupDatabase(file=None): global user global pwd global port global database global server if user == "": c = windows.readConfig() user = c[0] pwd = c[1] port = c[2] database = c[3] server = c[4] path = getPath() + "bin\\" cmdL1 = [ path + "mysqldump", "--port=" + port, "-h" + server, "--force", "-u" + user, "-p" + pwd, database ] p1 = Popen(cmdL1, shell=True, stdout=PIPE) if file is not None: file.write(p1.communicate()[0]) file.close() if file is None: return p1.communicate()[0]
def connection(): global user global pwd global port global database global server global odbcV if user == "": c = windows.readConfig() user = c[0] pwd = c[1] port = c[2] database = c[3] server = c[4] odbcV = c[5] with pyodbc.connect(r'DRIVER={MySQL ODBC ' + odbcV + ' Unicode Driver};' r'UID=' + user + ';' r'PWD=' + pwd + ';' r'PORT=' + port + ';' r'DATABASE=' + database + ';' r'SERVER=' + server + ';' r'OPTION=3;') as connection: # Setting Encoding connection.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8') connection.setencoding(encoding='utf-8') return connection
def addColumns(): query = "ALTER TABLE `" + windows.readConfig()[3] + "`.`items` \ ADD COLUMN `subtype` VARCHAR(45) NULL DEFAULT NULL AFTER `mods`, \ ADD COLUMN `buyprice` INT(11) NULL DEFAULT NULL AFTER `subtype`,\ ADD COLUMN `sellprice` INT(11) NULL DEFAULT NULL AFTER `buyprice`, \ ADD COLUMN `traderCat` VARCHAR(3) NULL DEFAULT NULL AFTER `sellprice`,\ ADD COLUMN `traderExclude` TINYINT(1) UNSIGNED ZEROFILL NOT NULL DEFAULT '0' AFTER `traderCat`;" conn = connection() cursor = conn.cursor() cursor.execute(query) conn.commit()
def createDB(name): global user global pwd global port global database global server global odbcV if user == "": c = windows.readConfig() user = c[0] pwd = c[1] port = c[2] database = c[3] server = c[4] odbcV = c[5] try: pyodbc.connect( r'DRIVER={MySQL ODBC 5.3 Unicode Driver};' r'UID=' + user + ';' r'PWD=' + pwd + ';' r'PORT=' + port + ';' r'SERVER=' + server + ';' r'OPTION=3;' ) except pyodbc.Error: pyodbc.connect( r'DRIVER={MySQL ODBC 8.0 Unicode Driver};' r'UID=' + user + ';' r'PWD=' + pwd + ';' r'PORT=' + port + ';' r'SERVER=' + server + ';' r'OPTION=3;' ) windows.writeConfig(user, pwd, port, database,server, "8.0") with pyodbc.connect( r'DRIVER={MySQL ODBC '+odbcV+' Unicode Driver};' r'UID=' + user + ';' r'PWD=' + pwd + ';' r'PORT=' + port + ';' r'SERVER=' + server + ';' r'OPTION=3;' ) as connection: # Setting Encoding connection.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8') connection.setencoding(encoding='utf-8') cursor = connection.cursor() cursor.execute("CREATE DATABASE " + name + ";")
def getCoulumNames(): global user global pwd global port global database global server global odbcV if user == "": c = windows.readConfig() user = c[0] pwd = c[1] port = c[2] database = c[3] server = c[4] odbcV = c[5] cursor = connection().cursor() cursor.execute("SELECT COLUMN_NAME \ FROM INFORMATION_SCHEMA.COLUMNS \ WHERE TABLE_SCHEMA= '" + database + "' \ AND TABLE_NAME= 'items' \ ORDER BY ORDINAL_POSITION;") return [row[0] for row in cursor.fetchall()]
def dropDB(): global database if database == "": database = windows.readConfig()[3] return drop_selected_DB(database)
def __init__(self, root): self.window = Toplevel(root) self.window.grab_set() self.user = "******" self.pwd = "rootroot" self.prt = "3306" self.dbName = "dayzitems" self.server = "localhost" self.odbcV = "8.0" try: c = windows.readConfig() self.user = c[0] self.pwd = c[1] self.prt = c[2] self.dbName = c[3] self.server = c[4] self.odbcV = c[5] except FileNotFoundError: pass self.entryFrame = Frame(self.window) self.entryFrame.grid(row=1, column=0, sticky="n,w,e", padx=30) # Host Label(self.entryFrame, text="Host:").grid(row=1, column=0, sticky="w") self.HostName = StringVar() self.HostName.set(self.server) self.nameEntry = Entry(self.entryFrame, textvariable=self.HostName) self.nameEntry.grid(row=1, column=1, sticky="e", pady=5) # Port Label(self.entryFrame, text="Port:").grid(row=2, column=0, sticky="w") self.port = StringVar() self.port.set(self.prt) self.nameEntry = Entry(self.entryFrame, textvariable=self.port) self.nameEntry.grid(row=2, column=1, sticky="e", pady=5) # Username Label(self.entryFrame, text="Username:"******"w") self.username = StringVar() self.username.set(self.user) self.DBEntry = Entry(self.entryFrame, textvariable=self.username) self.DBEntry.grid(row=4, column=1, sticky="e", pady=5) # Password Label(self.entryFrame, text="Password:"******"w") self.password = StringVar() self.password.set(self.pwd) self.DBEntry = Entry(self.entryFrame, textvariable=self.password) self.DBEntry.grid(row=5, column=1, sticky="e", pady=5) # Database MODES = [("New Database", "create"), ("Use Existing", "use")] self.v = StringVar() self.v.set("use") Radiobutton(self.entryFrame, text=MODES[0][0], variable=self.v, value=MODES[0][1]).grid(row=6, column=0, pady=10) Radiobutton(self.entryFrame, text=MODES[1][0], variable=self.v, value=MODES[1][1]).grid(row=6, column=1) Label(self.entryFrame, text="Database:").grid(row=7, column=0, sticky="w") self.database = StringVar() self.database.set(self.dbName) self.DBEntry = Entry(self.entryFrame, textvariable=self.database) self.DBEntry.grid(row=7, column=1, sticky="e", pady=5) # Types Label(self.entryFrame, text="Types.xml:").grid(row=8, column=0, sticky="w") self.typesDir = StringVar() self.DBEntry = Entry(self.entryFrame, textvariable=self.typesDir) self.DBEntry.grid(row=8, column=1, sticky="e", pady=5) Button(self.entryFrame, text="...", height=1, command=self.openTypes).grid(row=8, column=2, sticky="w") buttonFrame = Frame(self.window) buttonFrame.grid(row=2, column=0, columnspan=3, pady=10) Button(buttonFrame, text="Create / Test", width=12, command=self.createTest).grid(row=0, column=1, sticky="w", padx=5) Button(buttonFrame, text="Set", width=12, command=self.set).grid(row=0, column=3, sticky="e", padx=5) windows.center(self.window) self.window.wait_window()