def __init__(self, config=None, mysql=None, db_migrate=None): self.__cli = CLI() self.__config = config self.__mysql = mysql if self.__mysql is None and not self.__config.get("new_migration"): if self.__config.get("srv_type") == "mysql": self.__mysql = MySQL(config) else: self.__mysql = MSSQL(config) self.__db_migrate = db_migrate if self.__db_migrate is None: self.__db_migrate = Migrations(config)
def __init__(self, config, sgdb=None): Main._check_configuration(config) self.cli = CLI() self.config = config self.log = LOG(self.config.get("log_dir", None)) self.sgdb = sgdb if self.sgdb is None and not self.config.get("new_migration", None): if self.config.get("database_engine") == 'mysql': from mysql import MySQL self.sgdb = MySQL(config) elif self.config.get("database_engine") == 'oracle': from oracle import Oracle self.sgdb = Oracle(config) elif self.config.get("database_engine") == 'mssql': from mssql import MSSQL self.sgdb = MSSQL(config) elif self.config.get("database_engine") == 'postgresql': from postgresql import PostgreSQL self.sgdb = PostgreSQL(config) else: raise Exception("engine not supported '%s'" % self.config.get("database_engine")) self.db_migrate = SimpleDBMigrate(self.config)
def __init__(self, config, sgdb=None): Main._check_configuration(config) self.cli = CLI() self.config = config self.log = LOG(self.config.get("log_dir", None)) self.sgdb = sgdb if self.sgdb is None and not self.config.get("new_migration", None): if self.config.get("database_engine") == 'mysql': from mysql import MySQL self.sgdb = MySQL(config) elif self.config.get("database_engine") == 'oracle': from oracle import Oracle self.sgdb = Oracle(config) elif self.config.get("database_engine") == 'mssql': from mssql import MSSQL self.sgdb = MSSQL(config) else: raise Exception("engine not supported '%s'" % self.config.get("database_engine")) self.db_migrate = SimpleDBMigrate(self.config)
def init(self): #初始化mysql if self.cj == '共济': try: from mysql import MYSQL import xb_1 as cv self.cv = cv self.ms = MYSQL(host=self.host, port=self.port, user=self.user, pwd=self.pwd, db=self.db) self.flag = 1 except: self.flag = 0 self.sinOut.emit(2000) elif self.cj == '中联': try: from mssql import MSSQL import zl_1 as cv self.cv = cv self.ms = MSSQL(host=self.host, port=self.port, user=self.user, pwd=self.pwd, db=self.db) self.flag = 1 except: self.flag = 0 self.sinOut.emit(2001) elif self.cj == '栅格': try: from mysql import MYSQL import sg_1 as cv self.cv = cv self.ms = MYSQL(host=self.host, port=self.port, user=self.user, pwd=self.pwd, db=self.db) self.flag = 1 except: self.flag = 0 self.sinOut.emit(2000)
class Main(object): def __init__(self, config, sgdb=None): Main._check_configuration(config) self.cli = CLI() self.config = config self.log = LOG(self.config.get("log_dir", None)) self.sgdb = sgdb if self.sgdb is None and not self.config.get("new_migration", None): if self.config.get("database_engine") == 'mysql': from mysql import MySQL self.sgdb = MySQL(config) elif self.config.get("database_engine") == 'oracle': from oracle import Oracle self.sgdb = Oracle(config) elif self.config.get("database_engine") == 'mssql': from mssql import MSSQL self.sgdb = MSSQL(config) else: raise Exception("engine not supported '%s'" % self.config.get("database_engine")) self.db_migrate = SimpleDBMigrate(self.config) def execute(self): self._execution_log( '\nStarting DB migration on host/database "%s/%s" with user "%s"...' % (self.config.get('database_host'), self.config.get('database_name'), self.config.get('database_user')), "PINK", log_level_limit=1) if self.config.get("new_migration", None): self._create_migration() else: self._migrate() self._execution_log("\nDone.\n", "PINK", log_level_limit=1) @staticmethod def _check_configuration(config): if not isinstance(config, Config): raise Exception( "config must be an instance of simple_db_migrate.config.Config" ) required_configs = [ 'database_host', 'database_name', 'database_user', 'database_password', 'database_migrations_dir', 'database_engine', 'schema_version' ] if config.get("new_migration", None): required_configs = ['database_migrations_dir'] for key in required_configs: #check if config has the key, if do not have will raise exception config.get(key) def _create_migration(self): migrations_dir = self.config.get("database_migrations_dir") new_file = Migration.create( self.config.get("new_migration", None), migrations_dir[0], self.config.get("database_script_encoding", "utf-8"), self.config.get("utc_timestamp", False)) self._execution_log("- Created file '%s'" % (new_file), log_level_limit=1) def _migrate(self): destination_version = self._get_destination_version() current_version = self.sgdb.get_current_schema_version() # do it! self._execute_migrations(current_version, destination_version) def _get_destination_version(self): label_version = self.config.get("label_version", None) schema_version = self.config.get("schema_version", None) destination_version = None destination_version_by_label = None destination_version_by_schema = None if label_version is not None: destination_version_by_label = self.sgdb.get_version_number_from_label( label_version) """ if specified label exists at database and schema version was not specified, is equivalent to run simple-db-migrate with schema_version equals to the version with specified label """ if destination_version_by_label is not None and schema_version is None: schema_version = destination_version_by_label self.config.update("schema_version", destination_version_by_label) if schema_version is not None and self.sgdb.get_version_id_from_version_number( schema_version): destination_version_by_schema = schema_version if label_version is None: if schema_version is None: destination_version = self.db_migrate.latest_version_available( ) elif destination_version_by_schema is None: destination_version = schema_version else: destination_version = destination_version_by_schema else: if schema_version is None: destination_version = self.db_migrate.latest_version_available( ) elif (destination_version_by_label is None) or (destination_version_by_schema == destination_version_by_label): destination_version = schema_version if (destination_version_by_schema is not None ) and (destination_version_by_label is not None) and ( destination_version_by_schema != destination_version_by_label): raise Exception( "label (%s) and schema_version (%s) don't correspond to the same version at database" % (label_version, schema_version)) if (schema_version is not None and label_version is not None) and ( (destination_version_by_schema is not None and destination_version_by_label is None) or (destination_version_by_schema is None and destination_version_by_label is not None)): raise Exception( "label (%s) or schema_version (%s), only one of them exists in the database" % (label_version, schema_version)) if destination_version is not '0' and not ( self.db_migrate.check_if_version_exists(destination_version) or self.sgdb.get_version_id_from_version_number( destination_version)): raise Exception("version not found (%s)" % destination_version) return destination_version def _get_migration_files_to_be_executed(self, current_version, destination_version, is_migration_up): if current_version == destination_version and not self.config.get( "force_execute_old_migrations_versions", False): return [] schema_migrations = self.sgdb.get_all_schema_migrations() # migration up if is_migration_up: available_migrations = self.db_migrate.get_all_migrations() remaining_migrations = Lists.subtract(available_migrations, schema_migrations) remaining_migrations_to_execute = [ migration for migration in remaining_migrations if migration.version <= destination_version ] return remaining_migrations_to_execute # migration down... destination_version_id = self.sgdb.get_version_id_from_version_number( destination_version) try: migration_versions = self.db_migrate.get_all_migration_versions() except: migration_versions = [] down_migrations_to_execute = [ migration for migration in schema_migrations if migration.id > destination_version_id ] force_files = self.config.get("force_use_files_on_down", False) for migration in down_migrations_to_execute: if not migration.sql_down or force_files: if migration.version not in migration_versions: raise Exception( "impossible to migrate down: one of the versions was not found (%s)" % migration.version) migration_tmp = self.db_migrate.get_migration_from_version_number( migration.version) migration.sql_up = migration_tmp.sql_up migration.sql_down = migration_tmp.sql_down migration.file_name = migration_tmp.file_name down_migrations_to_execute.reverse() return down_migrations_to_execute def _execute_migrations(self, current_version, destination_version): """ passed a version: this version don't exists in the database and is younger than the last version -> do migrations up until this version this version don't exists in the database and is older than the last version -> do nothing, is a unpredictable behavior this version exists in the database and is older than the last version -> do migrations down until this version didn't pass a version -> do migrations up until the last available version """ is_migration_up = True # check if a version was passed to the program if self.config.get("schema_version"): # if was passed and this version is present in the database, check if is older than the current version destination_version_id = self.sgdb.get_version_id_from_version_number( destination_version) if destination_version_id: current_version_id = self.sgdb.get_version_id_from_version_number( current_version) # if this version is previous to the current version in database, then will be done a migration down to this version if current_version_id > destination_version_id: is_migration_up = False # if was passed and this version is not present in the database and is older than the current version, raise an exception # cause is trying to go down to something that never was done elif current_version > destination_version: raise Exception( "Trying to migrate to a lower version wich is not found on database (%s)" % destination_version) # getting only the migration sql files to be executed migrations_to_be_executed = self._get_migration_files_to_be_executed( current_version, destination_version, is_migration_up) self._execution_log("- Current version is: %s" % current_version, "GREEN", log_level_limit=1) if migrations_to_be_executed is None or len( migrations_to_be_executed) == 0: self._execution_log("- Destination version is: %s" % current_version, "GREEN", log_level_limit=1) self._execution_log("\nNothing to do.\n", "PINK", log_level_limit=1) return self._execution_log( "- Destination version is: %s" % (is_migration_up and migrations_to_be_executed[-1].version or destination_version), "GREEN", log_level_limit=1) up_down_label = is_migration_up and "up" or "down" if self.config.get("show_sql_only", False): self._execution_log( "\nWARNING: database migrations are not being executed ('--showsqlonly' activated)", "YELLOW", log_level_limit=1) else: self._execution_log("\nStarting migration %s!" % up_down_label, log_level_limit=1) self._execution_log( "*** versions: %s\n" % ([migration.version for migration in migrations_to_be_executed]), "CYAN", log_level_limit=1) sql_statements_executed = [] for migration in migrations_to_be_executed: sql = is_migration_up and migration.sql_up or migration.sql_down if not self.config.get("show_sql_only", False): self._execution_log("===== executing %s (%s) =====" % (migration.file_name, up_down_label), log_level_limit=1) label = None if is_migration_up: label = self.config.get("label_version", None) try: self.sgdb.change(sql, migration.version, migration.file_name, migration.sql_up, migration.sql_down, is_migration_up, self._execution_log, label) except Exception, e: self._execution_log("===== ERROR executing %s (%s) =====" % (migration.abspath, up_down_label), log_level_limit=1) raise e # paused mode if self.config.get("paused_mode", False): raw_input("* press <enter> to continue... ") # recording the last statement executed sql_statements_executed.append(sql) if self.config.get("show_sql", False) or self.config.get( "show_sql_only", False): self._execution_log( "__________ SQL statements executed __________", "YELLOW", log_level_limit=1) for sql in sql_statements_executed: self._execution_log(sql, "YELLOW", log_level_limit=1) self._execution_log( "_____________________________________________", "YELLOW", log_level_limit=1)
class Main(object): def __init__(self, config, sgdb=None): Main._check_configuration(config) self.cli = CLI() self.config = config self.log = LOG(self.config.get("log_dir", None)) self.sgdb = sgdb if self.sgdb is None and not self.config.get("new_migration", None): if self.config.get("database_engine") == 'mysql': from mysql import MySQL self.sgdb = MySQL(config) elif self.config.get("database_engine") == 'oracle': from oracle import Oracle self.sgdb = Oracle(config) elif self.config.get("database_engine") == 'mssql': from mssql import MSSQL self.sgdb = MSSQL(config) else: raise Exception("engine not supported '%s'" % self.config.get("database_engine")) self.db_migrate = SimpleDBMigrate(self.config) def execute(self): self._execution_log("\nStarting DB migration...", "PINK", log_level_limit=1) if self.config.get("new_migration", None): self._create_migration() else: self._migrate() self._execution_log("\nDone.\n", "PINK", log_level_limit=1) @staticmethod def _check_configuration(config): if not isinstance(config, Config): raise Exception("config must be an instance of simple_db_migrate.config.Config") required_configs = ['database_host', 'database_name', 'database_user', 'database_password', 'database_migrations_dir', 'database_engine', 'schema_version'] if config.get("new_migration", None): required_configs = ['database_migrations_dir'] for key in required_configs: #check if config has the key, if do not have will raise exception config.get(key) def _create_migration(self): migrations_dir = self.config.get("database_migrations_dir") new_file = Migration.create(self.config.get("new_migration", None), migrations_dir[0], self.config.get("database_script_encoding", "utf-8"), self.config.get("utc_timestamp", False)) self._execution_log("- Created file '%s'" % (new_file), log_level_limit=1) def _migrate(self): destination_version = self._get_destination_version() current_version = self.sgdb.get_current_schema_version() # do it! self._execute_migrations(current_version, destination_version) def _get_destination_version(self): label_version = self.config.get("label_version", None) schema_version = self.config.get("schema_version", None) destination_version = None destination_version_by_label = None destination_version_by_schema = None if label_version is not None: destination_version_by_label = self.sgdb.get_version_number_from_label(label_version) """ if specified label exists at database and schema version was not specified, is equivalent to run simple-db-migrate with schema_version equals to the version with specified label """ if destination_version_by_label is not None and schema_version is None: schema_version = destination_version_by_label self.config.update("schema_version", destination_version_by_label) if schema_version is not None and self.sgdb.get_version_id_from_version_number(schema_version): destination_version_by_schema = schema_version if label_version is None: if schema_version is None: destination_version = self.db_migrate.latest_version_available() elif destination_version_by_schema is None: destination_version = schema_version else: destination_version = destination_version_by_schema else: if schema_version is None: destination_version = self.db_migrate.latest_version_available() elif (destination_version_by_label is None) or (destination_version_by_schema == destination_version_by_label): destination_version = schema_version if (destination_version_by_schema is not None) and (destination_version_by_label is not None) and (destination_version_by_schema != destination_version_by_label): raise Exception("label (%s) and schema_version (%s) don't correspond to the same version at database" % (label_version, schema_version)) if (schema_version is not None and label_version is not None) and ((destination_version_by_schema is not None and destination_version_by_label is None) or (destination_version_by_schema is None and destination_version_by_label is not None)): raise Exception("label (%s) or schema_version (%s), only one of them exists in the database" % (label_version, schema_version)) if destination_version is not '0' and not (self.db_migrate.check_if_version_exists(destination_version) or self.sgdb.get_version_id_from_version_number(destination_version)): raise Exception("version not found (%s)" % destination_version) return destination_version def _get_migration_files_to_be_executed(self, current_version, destination_version, is_migration_up): if current_version == destination_version and not self.config.get("force_execute_old_migrations_versions", False): return [] schema_versions = self.sgdb.get_all_schema_versions() migration_versions = self.db_migrate.get_all_migration_versions() # migration up if is_migration_up: remaining_versions_to_execute = Lists.subtract(migration_versions, schema_versions) remaining_migrations_to_execute = [self.db_migrate.get_migration_from_version_number(version) for version in remaining_versions_to_execute if version <= destination_version] return remaining_migrations_to_execute # migration down... destination_version_id = self.sgdb.get_version_id_from_version_number(destination_version) migrations = self.sgdb.get_all_schema_migrations() down_migrations_to_execute = [migration for migration in migrations if migration.id > destination_version_id] force_files = self.config.get("force_use_files_on_down", False) for migration in down_migrations_to_execute: if not migration.sql_down or force_files: if migration.version not in migration_versions: raise Exception("impossible to migrate down: one of the versions was not found (%s)" % migration.version) migration_tmp = self.db_migrate.get_migration_from_version_number(migration.version) migration.sql_up = migration_tmp.sql_up migration.sql_down = migration_tmp.sql_down migration.file_name = migration_tmp.file_name down_migrations_to_execute.reverse() return down_migrations_to_execute def _execute_migrations(self, current_version, destination_version): """ passed a version: this version don't exists in the database and is younger than the last version -> do migrations up until this version this version don't exists in the database and is older than the last version -> do nothing, is a unpredictable behavior this version exists in the database and is older than the last version -> do migrations down until this version didn't pass a version -> do migrations up until the last available version """ is_migration_up = True # check if a version was passed to the program if self.config.get("schema_version"): # if was passed and this version is present in the database, check if is older than the current version destination_version_id = self.sgdb.get_version_id_from_version_number(destination_version) if destination_version_id: current_version_id = self.sgdb.get_version_id_from_version_number(current_version) # if this version is previous to the current version in database, then will be done a migration down to this version if current_version_id > destination_version_id: is_migration_up = False # if was passed and this version is not present in the database and is older than the current version, raise an exception # cause is trying to go down to something that never was done elif current_version > destination_version: raise Exception("Trying to migrate to a lower version wich is not found on database (%s)" % destination_version) # getting only the migration sql files to be executed migrations_to_be_executed = self._get_migration_files_to_be_executed(current_version, destination_version, is_migration_up) self._execution_log("- Current version is: %s" % current_version, "GREEN", log_level_limit=1) if migrations_to_be_executed is None or len(migrations_to_be_executed) == 0: self._execution_log("- Destination version is: %s" % current_version, "GREEN", log_level_limit=1) self._execution_log("\nNothing to do.\n", "PINK", log_level_limit=1) return self._execution_log("- Destination version is: %s" % (is_migration_up and migrations_to_be_executed[-1].version or destination_version), "GREEN", log_level_limit=1) up_down_label = is_migration_up and "up" or "down" if self.config.get("show_sql_only", False): self._execution_log("\nWARNING: database migrations are not being executed ('--showsqlonly' activated)", "YELLOW", log_level_limit=1) else: self._execution_log("\nStarting migration %s!" % up_down_label, log_level_limit=1) self._execution_log("*** versions: %s\n" % ([ migration.version for migration in migrations_to_be_executed]), "CYAN", log_level_limit=1) sql_statements_executed = [] for migration in migrations_to_be_executed: sql = is_migration_up and migration.sql_up or migration.sql_down if not self.config.get("show_sql_only", False): self._execution_log("===== executing %s (%s) =====" % (migration.file_name, up_down_label), log_level_limit=1) label = None if is_migration_up: label = self.config.get("label_version", None) try: self.sgdb.change(sql, migration.version, migration.file_name, migration.sql_up, migration.sql_down, is_migration_up, self._execution_log, label) except Exception, e: self._execution_log("===== ERROR executing %s (%s) =====" % (migration.abspath, up_down_label), log_level_limit=1) raise e # paused mode if self.config.get("paused_mode", False): raw_input("* press <enter> to continue... ") # recording the last statement executed sql_statements_executed.append(sql) if self.config.get("show_sql", False) or self.config.get("show_sql_only", False): self._execution_log("__________ SQL statements executed __________", "YELLOW", log_level_limit=1) for sql in sql_statements_executed: self._execution_log(sql, "YELLOW", log_level_limit=1) self._execution_log("_____________________________________________", "YELLOW", log_level_limit=1)
class Main(object): def __init__(self, config=None, mysql=None, db_migrate=None): self.__cli = CLI() self.__config = config self.__mysql = mysql if self.__mysql is None and not self.__config.get("new_migration"): if self.__config.get("srv_type") == "mysql": self.__mysql = MySQL(config) else: self.__mysql = MSSQL(config) self.__db_migrate = db_migrate if self.__db_migrate is None: self.__db_migrate = Migrations(config) def execute(self): self.__cli.msg("\nStarting DB migration...", "PINK") if self.__config.get("new_migration"): self._create_migration() else: self._migrate() self.__cli.msg("\nDone.\n", "PINK") def _create_migration(self): new_file = self.__db_migrate.create_migration(self.__config.get("new_migration")) self.__cli.msg("- Created file '%s'" % (new_file)) def _migrate(self): destination_version = self._get_destination_version() current_version = self.__mysql.get_current_schema_version() self.__cli.msg("- Current version is: %s" % current_version, "GREEN") self.__cli.msg("- Destination version is: %s" % destination_version, "GREEN") # if current and destination versions are the same, # will consider a migration up to execute remaining files is_migration_up = True if int(current_version) > int(destination_version): is_migration_up = False # do it! self._execute_migrations(current_version, destination_version, is_migration_up) def _get_destination_version(self): destination_version = self.__config.get("schema_version") if destination_version is None: destination_version = self.__db_migrate.latest_schema_version_available() if not self.__db_migrate.check_if_version_exists(destination_version): self.__cli.error_and_exit("version not found (%s)" % destination_version) return destination_version def _get_migration_files_to_be_executed(self, current_version, destination_version): mysql_versions = self.__mysql.get_all_schema_versions() migration_versions = self.__db_migrate.get_all_migration_versions() # migration up: the easy part if current_version <= destination_version: remaining_versions_to_execute = Lists.subtract(migration_versions, mysql_versions) remaining_versions_to_execute = [version for version in remaining_versions_to_execute if version <= destination_version] return remaining_versions_to_execute # migration down... down_versions = [version for version in mysql_versions if version <= current_version and version > destination_version] for version in down_versions: if version not in migration_versions: self.__cli.error_and_exit("impossible to migrate down: one of the versions was not found (%s)" % version) down_versions.reverse() return down_versions def _execute_migrations(self, current_version, destination_version, is_migration_up): # getting only the migration sql files to be executed versions_to_be_executed = self._get_migration_files_to_be_executed(current_version, destination_version) if versions_to_be_executed is None or len(versions_to_be_executed) == 0: self.__cli.msg("\nNothing to do.\n", "PINK") sys.exit(0) up_down_label = "up" if is_migration_up else "down" if self.__config.get("show_sql_only"): self.__cli.msg("\nWARNING: database migrations are not being executed ('--showsqlonly' activated)", "YELLOW") else: self.__cli.msg("\nStarting migration %s!" % up_down_label) self.__cli.msg("*** versions: %s\n" % versions_to_be_executed, "CYAN") sql_statements_executed = [] for migration_version in versions_to_be_executed: sql_file = self.__db_migrate.get_migration_file_name_from_version_number(migration_version) sql = self.__db_migrate.get_sql_command(sql_file, is_migration_up) if not self.__config.get("show_sql_only"): self.__cli.msg("===== executing %s (%s) =====" % (sql_file, up_down_label)) self.__mysql.change(sql, migration_version, is_migration_up) #recording the last statement executed sql_statements_executed.append(sql) if self.__config.get("show_sql") or self.__config.get("show_sql_only"): self.__cli.msg("__________ SQL statements executed __________", "YELLOW") for sql in sql_statements_executed: self.__cli.msg(sql, "YELLOW") self.__cli.msg("_____________________________________________", "YELLOW")
import xlrd import glob import os import csv import json from mssql import MSSQL MS = MSSQL(host="localhost", user="******", pwd="Health123", db="Dfcf") def bulk_query(csv_fn): basename = os.path.basename(csv_fn) data_name = basename[20:basename.index('.')] if '1Day.zjlx' in basename: data_name = '1DAY' return r"""BULK INSERT [dbo].[{table}] FROM '{csv}' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', FIRSTROW = 2 ) """.format(table=data_name, csv=csv_fn) def get_header(data_name): table = { 'GGPM': [ 'DT', 'Ordi', 'S', 'N', 'P', 'R', 'RP', 'V', 'NewV', 'BP', 'SP',
def query(saletype: str): result = MSSQL.execproc("BI_COST_GET_DEPT", (saletype, )) return result
def init(self, server, port, user, passwd, db): self.count = 1 self.ms = MSSQL(host=server, port=port, user=user, pwd=passwd, db=db)
class zldatbase(): def init(self, server, port, user, passwd, db): self.count = 1 self.ms = MSSQL(host=server, port=port, user=user, pwd=passwd, db=db) #print(server,port,user,passwd,db) def name_get_id(self, name): sql = "SELECT nID,strFullName from NameObject WHERE strFullName like '%{}%'".format( name) try: reslist = self.ms.ExecQuery(sql) except: return False else: return reslist def id_get_name(self, devid): #根据ID查找名 sql = "SELECT strfullname FROM nameobject where nid like '{}'".format( devid) #print(sql) res = self.ms.ExecQuery(sql) #print(res,789) self.count += 1 if res: if res[0][0]: return res[0][0] else: '未知设备' else: return '未知设备' def main(self, name): res = self.name_get_id(name) #print(res) text = '' if res: for i in res: #print(i) text1 = '' text1 += '|||本级设备名: ' + i[1] + ' ' + '本级设备ID: ' + ' ' + str( i[0]) upid = self.checkcont(i[0]) #print(upid,123) upname = self.id_get_name(upid) #print(upname,234) text1 = '上级设备名: ' + upname + ' 上级设备ID: ' + str( upid) + ' ' + text1 + '\n' #print(text1) text += text1 return text else: return '没有查询到任何设备' def checkcont(self, devid): #查询设备关系表 sql = "SELECT * from Container where nSubObjectID = {}".format(devid) try: res = self.ms.ExecQuery(sql) #print(res) self.count += 1 except: return '未知设备' else: for i in res: if i[1] == 62: return i[0] else: pass
def __init__(self, driver): self.mssql = MSSQL() self.driver = driver
class Lagou(object): def __init__(self, driver): self.mssql = MSSQL() self.driver = driver # self.taltalpage = 0 # 登录 def setUsername(self, username): return self.driver.find_element_by_xpath("//input[@placeholder='请输入常用手机号/邮箱']").send_keys(username) def setPassword(self, password): return self.driver.find_element_by_xpath("//input[@placeholder='请输入密码']").send_keys(password) def doSubmit(self): return self.driver.find_element_by_xpath("//form[@class='active']/div[5]/input[@type='submit']").click() def getLoginErrMsg(self): return self.driver.find_element_by_class_name('input_tips').text.strip() # 首页搜索,条件少 def firstSearch(self, keyword): return self.driver.find_element_by_id('search_input').send_keys(keyword) def firstSearchClick(self): return self.driver.find_element_by_id('search_button').click() # 详细搜索 def detailSearch(self, keyword, city, workyear, education, financestage, industryfield, monthsalary): keyword = keyword.strip() if len(city) == 0: city = "" else: city = "&city=%s" % city.strip() if len(workyear) == 0: workyear = "" else: workyear = "&gj=%s" % workyear.strip() if len(education) == 0: education = "" else: education = "&xl=%s" % education.strip() if len(financestage) == 0: financestage = "" else: financestage = "&jd=%s" % financestage.strip() if len(industryfield) == 0: industryfield = "" else: industryfield = "&hy=%s" % industryfield.strip() if len(monthsalary) == 0: monthsalary = "" else: monthsalary = "&yx=%s" % monthsalary.strip() # 选择标签比较麻烦,直接拼接网站访问 url = "https://www.lagou.com/jobs/list_%s?px=default" % keyword url = url + "%s%s%s%s%s%s" % (workyear, education, financestage, industryfield, monthsalary, city) self.driver.get(url) # 总页数 def getTaltalPage(self): num = self.driver.find_element_by_xpath("//div[@class='page-number']/span[2]").text.strip() if len(num) == 0: num = 0 # self.taltalpage = int(num) print("总页数:%s " % num) return int(num) # 点击下一页 def NextPage(self): self.driver.find_element_by_xpath("//span[@class='pager_next ']").click() # 保存所有页数据 def saveDate(self): taltalpage = self.getTaltalPage() currentpage = 1 if taltalpage != 0: while currentpage <= taltalpage: time.sleep(3) # 等待页面加载 print(">> 第 %s 页数据处理中…………………………………………" % currentpage) print(self.driver.current_url) self.saveOnePageDate() # 保存当页数据 self.NextPage() # 点击下一页 currentpage = currentpage + 1 else: pass # 保存一页数据 def saveOnePageDate(self): index = 0 while index <= 14: xpath = "//li[@data-index='%s']" % index print(">> 第 %s 条" % index) self.saveliDate(xpath) index = index + 1 # 保存 li 到数据库 def saveliDate(self, xpath): positi = self.driver.find_element_by_xpath(xpath + "/div[1]/div[1]/div[1]/a/h3").text.strip() citydist = self.driver.find_element_by_xpath(xpath + "/div[1]/div[1]/div[1]/a/span/em").text.strip() salary = self.driver.find_element_by_xpath(xpath + "/div[1]/div[1]/div[2]/div/span").text.strip() wy_edu = self.driver.find_element_by_xpath(xpath + "/div[1]/div[1]/div[2]/div").text.strip() company = self.driver.find_element_by_xpath(xpath + "/div[1]/div[2]/div[1]/a").text.strip() fina_ind = self.driver.find_element_by_xpath(xpath + "/div[1]/div[2]/div[2]").text.strip() firsttype = self.driver.find_element_by_xpath(xpath + "/div[2]/div[1]").text.strip() lables = self.driver.find_element_by_xpath(xpath + "/div[2]/div[2]").text.strip() companyfullname = company positionname = positi salary = salary # ((wy_edu.replace(" ", "/")).split('/')[0]).strip() workyear = ((wy_edu.replace(" ", "/")).split('/')[1]).strip() education = ((wy_edu.replace(" ", "/")).split('/')[4]).strip() city = "武汉" district = ((citydist + '·' + citydist).split('·')[1]).strip() industryfield = (fina_ind.split('/')[0]).strip() financestage = (fina_ind.split('/')[1]).strip() firsttype = firsttype.replace(" ", ",").strip() positionlables = lables.replace("“", "").replace("”", "").strip() sql = """INSERT INTO seleniumlagou( companyfullname , positionname, salary, workyear, education,city,district, industryfield, financestage, firsttype, positionlables) VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')""" % \ (companyfullname, positionname, salary, workyear, education, city, district, industryfield, financestage, firsttype, positionlables) self.mssql.ExecNonQuery(sql) """