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)
"""
