def test_get_columns_using_enumerator(self,
database: PostgresqlExtDatabase,
database_table: Tuple[str, str]):
enum_name = TEST_ENUM_NAME
enumerators.create_enumerator(enum_name)
columns = enumerators.get_columns_used_by_enumerator(enum_name)
assert len(columns) == 0
schema_name = database_table[0]
table_name = database_table[1]
column_name = "test_column"
sql_add_column = f"ALTER TABLE {schema_name}.{table_name} ADD COLUMN {column_name} {enum_name}"
database.execute_sql(sql_add_column)
columns = enumerators.get_columns_used_by_enumerator(enum_name)
assert len(columns) == 1
expected_data = {
"schema_name": schema_name,
"table_name": table_name,
"column_name": column_name
}
assert columns[0] == expected_data
assert enumerators.is_enumerator_used_by_any_column(enum_name) is True
def test_drop_enumerator_which_is_used_by_columns(
self, database: PostgresqlExtDatabase, database_table: Tuple[str,
str]):
enum_name = TEST_ENUM_NAME
enumerators.create_enumerator(enum_name)
schema_name = database_table[0]
table_name = database_table[1]
column_name = "test_column"
sql_add_column = f"ALTER TABLE {schema_name}.{table_name} ADD COLUMN {column_name} {enum_name}"
database.execute_sql(sql_add_column)
with pytest.raises(peewee.InternalError):
enumerators.drop_enumerator(enum_name)
def test_is_enumerator_used_by_any_column(self,
database: PostgresqlExtDatabase,
database_table: Tuple[str, str]):
enum_name = TEST_ENUM_NAME
enumerators.create_enumerator(enum_name)
assert enumerators.is_enumerator_used_by_any_column(enum_name) is False
schema_name = database_table[0]
table_name = database_table[1]
column_name = "test_column"
sql_add_column = f"ALTER TABLE {schema_name}.{table_name} ADD COLUMN {column_name} {enum_name}"
database.execute_sql(sql_add_column)
assert enumerators.is_enumerator_used_by_any_column(enum_name) is True
class LagouPipeline(object):
def __init__(self):
self.db = db
def process_item(self, item, spider):
print(item)
sql = "INSERT INTO position(position_name,city,business_zones," \
"company_full_name,company_short_name,company_lable_list," \
"company_size,education,finance_stage,first_type,industry_field," \
"job_nature,position_lables,salary,salary_max,salary_min," \
"salary_avg,second_type,work_year) VALUES (" \
"'{item[position_name]}'," \
"'{item[city]}'," \
"ARRAY{item[business_zones]}," \
"'{item[company_full_name]}'," \
"'{item[company_short_name]}'," \
"ARRAY{item[company_lable_list]}," \
"'{item[company_size]}','{item[education]}'," \
"'{item[finance_stage]}'," \
"'{item[first_type]}'," \
"'{item[industry_field]}'," \
"'{item[job_nature]}'," \
"ARRAY{item[position_lables]}," \
"'{item[salary]}'," \
"{item[salary_max]}," \
"{item[salary_min]}," \
"{item[salary_avg]}," \
"'{item[second_type]}','{item[work_year]}')".format(item=item)
print(sql)
try:
self.db.execute_sql(sql)
self.db.commit()
except peewee.IntegrityError:
self.db.rollback()
except (peewee.OperationalError, peewee.InterfaceError):
self.db.close()
self.db = PostgresqlExtDatabase(database=DB_DATABASE,
host=DB_HOST,
user=DB_USER,
password=DB_PASSWORD,
autocommit=True,
autorollback=True)
self.db.execute_sql(sql)
self.db.commit()
return item
def get_enum_values(database: PostgresqlExtDatabase, name: str):
sql = f"""SELECT t2.enumlabel
FROM pg_type t1 JOIN pg_enum t2 ON t1.oid = t2.enumtypid
WHERE t1.typname='{name}'"""
cursor = database.execute_sql(sql)
result = tuple(row[0] for row in cursor.fetchall())
return result
def createTable(Model, db_name, user=None, password=None, host='127.0.0.1'):
'''
create by bigzhu at 15/04/04 01:08:30 建立数据库表; peewee 要在定义时候指定 db 相当不人性化,修正
modify by bigzhu at 15/04/04 01:32:46 没有这个数据库的时候,直接返回建立数据的语句
modify by bigzhu at 15/04/04 01:45:43 如果表已经存在,不能往下继续了
'''
if user is None:
user = db_name
if password is None:
password = db_name
if host is None:
host = '127.0.0.1'
#db = PostgresqlExtDatabase(db_name, user=user, password=password, host='127.0.0.1', register_hstore=False)
db = PostgresqlExtDatabase(db_name, user=user, password=password, host=host, register_hstore=False)
Model._meta.database = db
try:
if Model.table_exists():
print 'table %s already exists' % Model.__name__
return
createBaseTable(db)
Model.create_table()
print 'create table ' + Model.__name__
except peewee.OperationalError:
print public_bz.getExpInfo()
showDBCreate(db_name)
exit(1)
table_name = Model.__name__
if table_name != 'base':
sql = '''
alter table %s inherit base;
''' % table_name
db.execute_sql(sql)
resetBaseDefault(db)
# add table comment
comment = Model.__doc__
sql = '''
COMMENT ON TABLE %s IS '%s';
''' % (table_name, comment)
db.execute_sql(sql)
def test_create_and_drop_enumerator(self, database: PostgresqlExtDatabase):
enum_name = TEST_ENUM_NAME
enumerators.create_enumerator(enum_name)
enumerators.drop_enumerator(enum_name)
sql_select = self.build_select_enum_sql(enum_name)
cursor = database.execute_sql(sql_select)
rows = cursor.fetchall()
assert len(rows) == 0
def test_create_enumerator_duplicate(self,
database: PostgresqlExtDatabase):
enum_name = TEST_ENUM_NAME
enumerators.create_enumerator(enum_name)
with pytest.raises(peewee.ProgrammingError):
enumerators.create_enumerator(enum_name)
sql_select = self.build_select_enum_sql(enum_name)
cursor = database.execute_sql(sql_select)
rows = cursor.fetchall()
assert len(rows) == 1
def test_create_enumerator_string_values(self,
database: PostgresqlExtDatabase):
enum_name = TEST_ENUM_NAME
enum_values = ("v1", "v2", "v3")
enumerators.create_enumerator(enum_name, enum_values)
sql_select = self.build_select_enum_sql(enum_name)
cursor = database.execute_sql(sql_select)
rows = cursor.fetchall()
assert len(rows) == 1
actual_values = self.get_enum_values(database, enum_name)
assert actual_values == enum_values
