def test_bulk_save_updated_include_unchanged(self):
User, = self.classes("User",)
s = Session(expire_on_commit=False)
objects = [
User(name="u1"),
User(name="u2"),
User(name="u3")
]
s.add_all(objects)
s.commit()
objects[0].name = 'u1new'
objects[2].name = 'u3new'
s = Session()
with self.sql_execution_asserter() as asserter:
s.bulk_save_objects(objects, update_changed_only=False)
asserter.assert_(
CompiledSQL(
"UPDATE users SET name=:name WHERE "
"users.id = :users_id",
[{'users_id': 1, 'name': 'u1new'},
{'users_id': 2, 'name': 'u2'},
{'users_id': 3, 'name': 'u3new'}]
)
)
def test_bulk_insert_via_save(self):
Foo = self.classes.Foo
s = Session()
s.bulk_save_objects([Foo(value="value")])
eq_(s.query(Foo).all(), [Foo(version_id=1, value="value")])
def test_bulk_save_mappings_preserve_order(self):
User, = self.classes("User")
s = Session()
# commit some object into db
user1 = User(name="i1")
user2 = User(name="i2")
s.add(user1)
s.add(user2)
s.commit()
# make some changes
user1.name = "u1"
user3 = User(name="i3")
s.add(user3)
user2.name = "u2"
objects = [user1, user3, user2]
from sqlalchemy import inspect
def _bulk_save_mappings(
mapper,
mappings,
isupdate,
isstates,
return_defaults,
update_changed_only,
render_nulls,
):
mock_method(list(mappings), isupdate)
mock_method = mock.Mock()
with mock.patch.object(s, "_bulk_save_mappings", _bulk_save_mappings):
s.bulk_save_objects(objects)
eq_(
mock_method.mock_calls,
[
mock.call([inspect(user1)], True),
mock.call([inspect(user3)], False),
mock.call([inspect(user2)], True),
],
)
mock_method = mock.Mock()
with mock.patch.object(s, "_bulk_save_mappings", _bulk_save_mappings):
s.bulk_save_objects(objects, preserve_order=False)
eq_(
mock_method.mock_calls,
[
mock.call([inspect(user3)], False),
mock.call([inspect(user1), inspect(user2)], True),
],
)
def test_bulk_save(n):
"""Individual INSERT/COMMIT pairs using the "bulk" API """
for i in range(n):
session = Session(bind=engine)
session.bulk_save_objects([
Customer(
name='customer name %d' % i,
description='customer description %d' % i
)])
session.commit()
def test_bulk_save(n):
"""Batched INSERT statements via the ORM in "bulk", discarding PKs."""
session = Session(bind=engine)
session.bulk_save_objects([
Customer(
name='customer name %d' % i,
description='customer description %d' % i
)
for i in range(n)
])
session.commit()
def test_bulk_save_return_pks(n):
"""Individual INSERT statements in "bulk", but calling upon last row id"""
session = Session(bind=engine)
session.bulk_save_objects([
Customer(
name='customer name %d' % i,
description='customer description %d' % i
)
for i in range(n)
], return_defaults=True)
session.commit()
class SqlAlchemyBulkTest(TestCase):
def setUp(self):
self.engine = sa.create_engine('crate://')
Base = declarative_base(bind=self.engine)
class Character(Base):
__tablename__ = 'characters'
name = sa.Column(sa.String, primary_key=True)
age = sa.Column(sa.Integer)
self.character = Character
self.session = Session()
@patch('crate.client.connection.Cursor', FakeCursor)
def test_bulk_save(self):
chars = [
self.character(name='Arthur', age=35),
self.character(name='Banshee', age=26),
self.character(name='Callisto', age=37),
]
fake_cursor.description = ()
fake_cursor.rowcount = len(chars)
fake_cursor.executemany.return_value = [
{'rowcount': 1},
{'rowcount': 1},
{'rowcount': 1},
]
self.session.bulk_save_objects(chars)
(stmt, bulk_args), _kwargs = fake_cursor.executemany.call_args
expected_stmt = "INSERT INTO characters (name, age) VALUES (?, ?)"
self.assertEqual(expected_stmt, stmt)
expected_bulk_args = (
('Arthur', 35),
('Banshee', 26),
('Callisto', 37)
)
self.assertEqual(expected_bulk_args, bulk_args)
def persist(session: orm.Session, distributions: List[BaseDistribution]) -> None:
if len(distributions) == 0:
return
session.bulk_save_objects(distributions)
session.commit()
if isinstance(e.ad_, BINet) and e.ad_.version == 0:
continue
if heuristic is not None and heuristic not in e.ad_.supported_heuristics:
continue
if strategy is not None and strategy not in e.ad_.supported_strategies:
continue
if base is not None and base not in e.ad_.supported_bases:
continue
_params.append([e, base, heuristic, strategy])
return [_e for p in _params for _e in _evaluate(p)]
if __name__ == "__main__":
models = sorted([m.name for m in get_model_files() if m.p == 0.3])
evaluations = []
with Pool() as p:
for e in tqdm(p.imap(evaluate, models),
total=len(models),
desc='Evaluate'):
evaluations.append(e)
print(evaluations)
# Write to database
session = Session(get_engine())
for e in evaluations:
session.bulk_save_objects(e)
session.commit()
session.close()
def test_bulk_save_joined_inh_no_defaults(self):
Person, Engineer, Manager, Boss = self.classes("Person", "Engineer",
"Manager", "Boss")
s = Session()
with self.sql_execution_asserter() as asserter:
s.bulk_save_objects([
Manager(person_id=1,
name="m1",
status="s1",
manager_name="mn1"),
Engineer(
person_id=2,
name="e1",
status="s2",
primary_language="l1",
),
Engineer(
person_id=3,
name="e2",
status="s3",
primary_language="l2",
),
Boss(
person_id=4,
boss_id=4,
name="b1",
status="s3",
manager_name="mn2",
golf_swing="g1",
),
])
# the only difference here is that common classes are grouped together.
# at the moment it doesn't lump all the "people" tables from
# different classes together.
asserter.assert_(
CompiledSQL(
"INSERT INTO people (person_id, name, type) VALUES "
"(:person_id, :name, :type)",
[{
"person_id": 1,
"type": "manager",
"name": "m1"
}],
),
CompiledSQL(
"INSERT INTO managers (person_id, status, manager_name) "
"VALUES (:person_id, :status, :manager_name)",
[{
"status": "s1",
"person_id": 1,
"manager_name": "mn1"
}],
),
CompiledSQL(
"INSERT INTO people (person_id, name, type) VALUES "
"(:person_id, :name, :type)",
[
{
"person_id": 2,
"type": "engineer",
"name": "e1"
},
{
"person_id": 3,
"type": "engineer",
"name": "e2"
},
],
),
CompiledSQL(
"INSERT INTO engineers (person_id, status, primary_language) "
"VALUES (:person_id, :status, :primary_language)",
[
{
"person_id": 2,
"status": "s2",
"primary_language": "l1"
},
{
"person_id": 3,
"status": "s3",
"primary_language": "l2"
},
],
),
CompiledSQL(
"INSERT INTO people (person_id, name, type) VALUES "
"(:person_id, :name, :type)",
[{
"person_id": 4,
"type": "boss",
"name": "b1"
}],
),
CompiledSQL(
"INSERT INTO managers (person_id, status, manager_name) "
"VALUES (:person_id, :status, :manager_name)",
[{
"status": "s3",
"person_id": 4,
"manager_name": "mn2"
}],
),
CompiledSQL(
"INSERT INTO boss (boss_id, golf_swing) VALUES "
"(:boss_id, :golf_swing)",
[{
"boss_id": 4,
"golf_swing": "g1"
}],
),
)
def test_bulk_save_joined_inh_return_defaults(self):
Person, Engineer, Manager, Boss = self.classes("Person", "Engineer",
"Manager", "Boss")
s = Session()
objects = [
Manager(name="m1", status="s1", manager_name="mn1"),
Engineer(name="e1", status="s2", primary_language="l1"),
Engineer(name="e2", status="s3", primary_language="l2"),
Boss(name="b1", status="s3", manager_name="mn2", golf_swing="g1"),
]
assert "person_id" not in objects[0].__dict__
with self.sql_execution_asserter() as asserter:
s.bulk_save_objects(objects, return_defaults=True)
asserter.assert_(
CompiledSQL(
"INSERT INTO people (name, type) VALUES (:name, :type)",
[{
"type": "manager",
"name": "m1"
}],
),
CompiledSQL(
"INSERT INTO managers (person_id, status, manager_name) "
"VALUES (:person_id, :status, :manager_name)",
[{
"person_id": 1,
"status": "s1",
"manager_name": "mn1"
}],
),
CompiledSQL(
"INSERT INTO people (name, type) VALUES (:name, :type)",
[{
"type": "engineer",
"name": "e1"
}],
),
CompiledSQL(
"INSERT INTO people (name, type) VALUES (:name, :type)",
[{
"type": "engineer",
"name": "e2"
}],
),
CompiledSQL(
"INSERT INTO engineers (person_id, status, primary_language) "
"VALUES (:person_id, :status, :primary_language)",
[
{
"person_id": 2,
"status": "s2",
"primary_language": "l1"
},
{
"person_id": 3,
"status": "s3",
"primary_language": "l2"
},
],
),
CompiledSQL(
"INSERT INTO people (name, type) VALUES (:name, :type)",
[{
"type": "boss",
"name": "b1"
}],
),
CompiledSQL(
"INSERT INTO managers (person_id, status, manager_name) "
"VALUES (:person_id, :status, :manager_name)",
[{
"person_id": 4,
"status": "s3",
"manager_name": "mn2"
}],
),
CompiledSQL(
"INSERT INTO boss (boss_id, golf_swing) VALUES "
"(:boss_id, :golf_swing)",
[{
"boss_id": 4,
"golf_swing": "g1"
}],
),
)
eq_(objects[0].__dict__["person_id"], 1)
eq_(objects[3].__dict__["person_id"], 4)
eq_(objects[3].__dict__["boss_id"], 4)
def save_insert_table(db: Session, fqdn_insert_list):
db.bulk_save_objects(fqdn_insert_list)
db.commit()
class InfoExtractor:
"""
This class executes the following task:
1. web scrapes the url's from the url list and retrieves the relevant gzip file links
2. extracts the xml file from the gzip file and parses it into an xml tree
3. the relevant information is then extracted from the parsed xml tree
4. the information is placed in the relevant table in the db.
- this script only updates the product information (db table name = products)
- and branch price information (db table name = branch_price)
Currently this class can only handle the supermarkets: shufersal, mega and victory
XML files are found @https://www.consumers.org.il/item/transparency_price
"""
def __init__(self):
self.current_super = ''
# list of unwanted names to be filter out
self.exclude_names = ['משלוחים', 'ריק', 'פיקדון', 'תיבה', 'משלוח']
self.item_id_set = set()
self.branch_price_unique_constraint_set = set()
engine = create_engine(
'mysql+pymysql://Super_User:SuperX1234'
'@mysql-13101-0.cloudclusters.net:13101/SuperX',
echo=False)
self.session = Session(bind=engine)
def run_info_extractor(self):
"""
This method is incharge of the whole extraction process.
It works in the following way:
1. retreives all zip file links from the relevant website
2. retreives all branch id's and child node objects that hold the wanted information
3. creates 2 lists one containing all Product info and the other all BranchPrice info
4. commits to the db
"""
self.create_branch_price_set()
self.create_product_set()
for key in supermarket_info_dictionary:
self.current_super = supermarket_info_dictionary[key]
url_list = [self.current_super['url']]
if self.current_super['multiple_pages']:
try:
url_list = self.get_all_super_links()
except ConnectionError as c_e:
logging.error(str(c_e))
zip_links = self.get_zip_file_links(url_list)
node_info_list = self.extract_xml_from_zip_and_parse(zip_links)
for info_child_node, branch_id in node_info_list:
xml_info_list = self.extract_information_from_parsed_xml(
info_child_node)
if branch_id == '86' and self.current_super[
'store_name'] == 'victory':
continue
product_info_list, branch_price_list = self.fill_product_and_branch_price_tables(
xml_info_list, branch_id)
if len(product_info_list) != 0:
self.session.bulk_save_objects(product_info_list)
if len(branch_price_list) != 0:
self.session.bulk_save_objects(branch_price_list)
self.session.flush()
self.session.commit()
self.session.close()
def get_zip_file_links(self, url_list):
"""
This method web scrapes the urls in url_list and creates a set of the gzip file links.
The method then sends the set to parsing
If connection to the url failed, moves on to next url
:param url_list: list of urls to extract zip files from
:return: a set of zip file links
"""
for url in url_list:
try:
page = requests.get(url)
web_scrapper = BeautifulSoup(page.content, 'html.parser')
except requests.ConnectionError:
logging.error('Unable to connect to url:\n %s', url)
else:
links_list = web_scrapper.find_all('a')
zip_links = set()
for link in links_list:
if link.has_attr('href'):
https = str(link.attrs['href'])
if self.current_super['price_full'] in https:
zip_links.add(https)
return zip_links
def extract_xml_from_zip_and_parse(self, zip_links):
"""
This method retrieves the xml file in the gzip file and parses it into an xml tree
The child node containg the item information is found
and The branch_id is retrieved from the xml file.
This information is packed into a tuple and placed in a list
If connection failed, moves on to next link
:param zip_links: list of zip file links from the website
:return: a list of tuples containing all branch id's and child node object's
"""
node_info_list = []
for zip_link in zip_links:
# fix zip link url if neccessary
if not self.current_super['zip_link_prefix'] is None:
zip_link = self.current_super['zip_link_prefix'] + zip_link
try:
request = requests.get(zip_link)
content = request.content
except requests.ConnectionError:
logging.error('Unable to extract from zip file with url: %s',
zip_link)
else:
xml_file = gzip.decompress(content).decode('utf-8')
store_id = 'StoreId'
if self.current_super['store_name'] == 'victory':
store_id = 'StoreID'
# parses the xml document into a tree
tree = ET.fromstring(xml_file)
branch_id = tree.find(store_id).text.lstrip('0')
# gets child containing item information
info_child_node = tree.getchildren()[-1] # pylint: disable=deprecated-method
node_info_list.append((info_child_node, branch_id))
return node_info_list
def extract_information_from_parsed_xml(self, xml_info_child_node):
"""
This method iterates over all items in the supermarket and extracts the relevant data
The data is then committed packed into a tuple and placed in a list of all the info tuples
:param xml_info_child_node: The child of the parsed xml tree containing all item info
:return: a list of tuples containing the information
"""
item_attr_name = self.current_super['item_attr_name']
is_weighted_attr = self.current_super['is_weighted_attr_name']
xml_info_list = []
for item in xml_info_child_node.findall(item_attr_name):
item_code = int(item.find('ItemCode').text)
if item_code == 0:
continue
item_name = item.find('ItemName').text
# exclude unwanted names from DB
for name in self.exclude_names:
if name in item_name:
continue
quantity = Decimal(item.find('Quantity').text)
price = Decimal(item.find('ItemPrice').text)
update_date = self.standardize_date(
item.find('PriceUpdateDate').text)
is_weighted = False
if item.find(is_weighted_attr).text == '1':
is_weighted = True
unit_of_measure = 'יחידה'
if is_weighted:
unit_of_measure = self.standardize_weight_name(
item.find('UnitQty').text.strip())
xml_info_list.append((item_code, item_name, quantity, is_weighted,
unit_of_measure, price, update_date))
return xml_info_list
def fill_product_and_branch_price_tables(self, information_list,
branch_id): # pylint: disable=too-many-locals
"""
This method receives a list containing a tuple of all the xml info
and places it into the correct table
:param information_list: list of tuples containing all the xml info
:param branch_id: the id of the current branch
:return: two lists, one containing Product obj the other BranchPrice objects
"""
branch_price_list = []
product_info_list = []
for item_code, item_name, quantity, is_weighted, unit_of_measure, price, update_date in information_list: # pylint: disable=line-too-long
# If the item is not in the db , add it
if item_code not in self.item_id_set:
product_info_list.append(
Product(id=item_code,
name=item_name,
quantity=quantity,
is_weighted=is_weighted,
unit_of_measure=unit_of_measure))
self.item_id_set.add(item_code)
# if not in the db then add it
if (self.current_super['chain_id'], item_code, int(branch_id)) not in \
self.branch_price_unique_constraint_set:
branch_price_list.append(
BranchPrice(chain_id=self.current_super['chain_id'],
branch_id=branch_id,
item_code=item_code,
price=price,
update_date=update_date))
self.branch_price_unique_constraint_set.add(
(self.current_super['chain_id'], item_code,
int(branch_id)))
else:
current_branch = self.session.query(BranchPrice).filter_by(
chain_id=self.current_super['chain_id'],
item_code=item_code,
branch_id=branch_id).all()[0]
old_price = current_branch.price
# update the price if it has changed
if old_price != price:
current_branch.price = price
self.session.flush()
return product_info_list, branch_price_list
def standardize_weight_name(self, unit_in_hebrew): # pylint: disable=no-self-use
"""
This method standardizes the unit of measurement
if the unit of measurement is not know, returns unknown
:param unit_in_hebrew: unit of measurement in hebrew
:return: standardized version of the unit or unknown if the unit is not known
"""
unit_dict = {
'ק"ג': ['קילו', 'ק"ג', 'קילו', 'קילוגרמים', '1 ק"ג'],
'גרם': ['גרם', 'גרמים'],
'ליטר': ['ליטר', 'ליטרים', 'ליטר '],
'מ"ל': ['מיליליטרים', 'מ"ל', 'מיליליטר'],
'יחידה': [
'יחידה', 'לא ידוע', "יח'", "'יח", "יח`", "מטרים", "מארז",
"קרטון"
]
}
for unit in unit_dict.keys(): # pylint: disable=C0201
if unit_in_hebrew in unit_dict[unit]:
return unit
if "יח'" in unit_in_hebrew:
return 'יחידה'
# as a default return the original unit and log it
logging.info('New item weight name encoded to UTF-8: %s',
unit_in_hebrew.encode("UTF-8"))
return unit_in_hebrew
def standardize_date(self, date):
"""
This method standardizes the update date of the item
:param date: string representation of the date from the xml file
:return: standardized date as a string
"""
# remove time from date
date = date[:10]
date_format = self.current_super['item_date_format']
new_date = datetime.strptime(date, date_format).date()
return new_date.__str__()
def get_all_super_links(self):
"""
builds a list of urls for the supermarket
:return: a list of links according to th amount of pages
"""
num_of_pages = self.get_num_of_pages()
if num_of_pages == -1:
raise ConnectionError(
f'Unable to connect to url to find number of pages for '
f'{self.current_super["store_name"]}')
general_url = self.current_super['url']
general_url = general_url[:len(general_url) - 1]
url_list = []
for i in range(1, num_of_pages + 1):
url_list.append(general_url + str(i))
return url_list
def get_num_of_pages(self):
"""
gets the number of pages for a certain supermarket
In case the number of pages increases or decreases
the code will always check if it is a single, double, triple digit
:return: number of pages
"""
num_of_pages = '1'
try:
page = requests.get(self.current_super['url'])
web_scrapper = BeautifulSoup(page.content, 'html.parser')
except requests.ConnectionError:
num_of_pages = -1
else:
if self.current_super['store_name'] == 'shufersal':
links = web_scrapper.find_all(name='a', text='>>')
wanted_link = links[0]
for i in range(1, 4):
try:
num_of_pages = int(wanted_link.attrs['href'][-i::])
except ValueError:
break
return num_of_pages
def create_branch_price_set(self):
"""
queries the DB for all rows and saves the unique contraints in a set
"""
all_rows = BranchPrice.query.all()
for row in all_rows:
unique_constraints = (row.chain_id, row.item_code, row.branch_id)
self.branch_price_unique_constraint_set.add(unique_constraints)
def create_product_set(self):
"""
queries the DB for all rows and saves the unique contraints in a set
"""
all_rows = Product.query.all()
for row in all_rows:
self.item_id_set.add(row.id)
def insert_data_from_json(json_path: str, current_session: Session) -> None:
json_data_from_file = Utils.load_json_data_from_file(json_path)
rows_to_add = get_rows_to_create(json_data_from_file)
current_session.bulk_save_objects(rows_to_add)
current_session.commit()
for f in files:
print('Processing: %s' % f)
records = []
with open(f, encoding='utf8') as csv_file:
csv_reader = csv.reader(csv_file, delimiter=';')
line_count = 0
for row in csv_reader:
print(f'Row in progress: %s' % row)
print(f'Length %i' % len(row))
if len(row) == 9:
records.append(
Tax(year=row[0],
business_id=row[1],
name=row[2],
municipality=row[3],
tax_income=float(row[4].replace(',', '.')),
tax_total=float(row[5].replace(',', '.')),
tax_advance=float(row[6].replace(',', '.')),
tax_return=float(row[7].replace(',', '.')),
tax_residual=float(row[8].replace(',', '.'))))
line_count += 1
session.bulk_save_objects(records)
session.commit()
records = []
print(f'Committing records')
print(f'Processed {line_count} lines.')
session.commit()
session.close()
employee_name = Column(String, nullable=False)
employee_sal = Column(Integer)
class Login(Base):
__tablename__ = "login"
username = Column(String, index=True, primary_key=True)
password = Column(String, nullable=False)
datetime = Column(DateTime,
nullable=False,
default=datetime.datetime.now())
Base.metadata.create_all(engine)
session = Session(engine)
employee = [
Employee(employee_name='Tom', employee_sal=1000),
Employee(employee_name='John', employee_sal=1500)
]
session.bulk_save_objects(employee)
session.commit()
login = Login(username="******", password=generate_password_hash("admin@1234"))
session.add(login)
session.commit()
session.close()
def test_bulk_save_joined_inh_return_defaults(self):
Person, Engineer, Manager, Boss = self.classes(
"Person", "Engineer", "Manager", "Boss"
)
s = Session()
objects = [
Manager(name="m1", status="s1", manager_name="mn1"),
Engineer(name="e1", status="s2", primary_language="l1"),
Engineer(name="e2", status="s3", primary_language="l2"),
Boss(name="b1", status="s3", manager_name="mn2", golf_swing="g1"),
]
assert "person_id" not in objects[0].__dict__
with self.sql_execution_asserter() as asserter:
s.bulk_save_objects(objects, return_defaults=True)
asserter.assert_(
CompiledSQL(
"INSERT INTO people (name, type) VALUES (:name, :type)",
[{"type": "manager", "name": "m1"}],
),
CompiledSQL(
"INSERT INTO managers (person_id, status, manager_name) "
"VALUES (:person_id, :status, :manager_name)",
[{"person_id": 1, "status": "s1", "manager_name": "mn1"}],
),
CompiledSQL(
"INSERT INTO people (name, type) VALUES (:name, :type)",
[{"type": "engineer", "name": "e1"}],
),
CompiledSQL(
"INSERT INTO people (name, type) VALUES (:name, :type)",
[{"type": "engineer", "name": "e2"}],
),
CompiledSQL(
"INSERT INTO engineers (person_id, status, primary_language) "
"VALUES (:person_id, :status, :primary_language)",
[
{"person_id": 2, "status": "s2", "primary_language": "l1"},
{"person_id": 3, "status": "s3", "primary_language": "l2"},
],
),
CompiledSQL(
"INSERT INTO people (name, type) VALUES (:name, :type)",
[{"type": "boss", "name": "b1"}],
),
CompiledSQL(
"INSERT INTO managers (person_id, status, manager_name) "
"VALUES (:person_id, :status, :manager_name)",
[{"person_id": 4, "status": "s3", "manager_name": "mn2"}],
),
CompiledSQL(
"INSERT INTO boss (boss_id, golf_swing) VALUES "
"(:boss_id, :golf_swing)",
[{"boss_id": 4, "golf_swing": "g1"}],
),
)
eq_(objects[0].__dict__["person_id"], 1)
eq_(objects[3].__dict__["person_id"], 4)
eq_(objects[3].__dict__["boss_id"], 4)
def test_bulk_save_joined_inh_no_defaults(self):
Person, Engineer, Manager, Boss = \
self.classes('Person', 'Engineer', 'Manager', 'Boss')
s = Session()
with self.sql_execution_asserter() as asserter:
s.bulk_save_objects([
Manager(
person_id=1, name='m1', status='s1', manager_name='mn1'),
Engineer(
person_id=2, name='e1', status='s2',
primary_language='l1'),
Engineer(
person_id=3, name='e2', status='s3',
primary_language='l2'),
Boss(person_id=4,
boss_id=4,
name='b1',
status='s3',
manager_name='mn2',
golf_swing='g1')
], )
# the only difference here is that common classes are grouped together.
# at the moment it doesn't lump all the "people" tables from
# different classes together.
asserter.assert_(
CompiledSQL(
"INSERT INTO people (person_id, name, type) VALUES "
"(:person_id, :name, :type)", [{
'person_id': 1,
'type': 'manager',
'name': 'm1'
}]),
CompiledSQL(
"INSERT INTO managers (person_id, status, manager_name) "
"VALUES (:person_id, :status, :manager_name)",
[{
'status': 's1',
'person_id': 1,
'manager_name': 'mn1'
}]),
CompiledSQL(
"INSERT INTO people (person_id, name, type) VALUES "
"(:person_id, :name, :type)", [{
'person_id': 2,
'type': 'engineer',
'name': 'e1'
}, {
'person_id': 3,
'type': 'engineer',
'name': 'e2'
}]),
CompiledSQL(
"INSERT INTO engineers (person_id, status, primary_language) "
"VALUES (:person_id, :status, :primary_language)",
[{
'person_id': 2,
'status': 's2',
'primary_language': 'l1'
}, {
'person_id': 3,
'status': 's3',
'primary_language': 'l2'
}]),
CompiledSQL(
"INSERT INTO people (person_id, name, type) VALUES "
"(:person_id, :name, :type)", [{
'person_id': 4,
'type': 'boss',
'name': 'b1'
}]),
CompiledSQL(
"INSERT INTO managers (person_id, status, manager_name) "
"VALUES (:person_id, :status, :manager_name)",
[{
'status': 's3',
'person_id': 4,
'manager_name': 'mn2'
}]),
CompiledSQL(
"INSERT INTO boss (boss_id, golf_swing) VALUES "
"(:boss_id, :golf_swing)", [{
'boss_id': 4,
'golf_swing': 'g1'
}]))
def test_bulk_save_joined_inh_return_defaults(self):
Person, Engineer, Manager, Boss = \
self.classes('Person', 'Engineer', 'Manager', 'Boss')
s = Session()
objects = [
Manager(name='m1', status='s1', manager_name='mn1'),
Engineer(name='e1', status='s2', primary_language='l1'),
Engineer(name='e2', status='s3', primary_language='l2'),
Boss(name='b1', status='s3', manager_name='mn2', golf_swing='g1')
]
assert 'person_id' not in objects[0].__dict__
with self.sql_execution_asserter() as asserter:
s.bulk_save_objects(objects, return_defaults=True)
asserter.assert_(
CompiledSQL(
"INSERT INTO people (name, type) VALUES (:name, :type)",
[{
'type': 'manager',
'name': 'm1'
}]),
CompiledSQL(
"INSERT INTO managers (person_id, status, manager_name) "
"VALUES (:person_id, :status, :manager_name)",
[{
'person_id': 1,
'status': 's1',
'manager_name': 'mn1'
}]),
CompiledSQL(
"INSERT INTO people (name, type) VALUES (:name, :type)",
[{
'type': 'engineer',
'name': 'e1'
}]),
CompiledSQL(
"INSERT INTO people (name, type) VALUES (:name, :type)",
[{
'type': 'engineer',
'name': 'e2'
}]),
CompiledSQL(
"INSERT INTO engineers (person_id, status, primary_language) "
"VALUES (:person_id, :status, :primary_language)",
[{
'person_id': 2,
'status': 's2',
'primary_language': 'l1'
}, {
'person_id': 3,
'status': 's3',
'primary_language': 'l2'
}]),
CompiledSQL(
"INSERT INTO people (name, type) VALUES (:name, :type)",
[{
'type': 'boss',
'name': 'b1'
}]),
CompiledSQL(
"INSERT INTO managers (person_id, status, manager_name) "
"VALUES (:person_id, :status, :manager_name)",
[{
'person_id': 4,
'status': 's3',
'manager_name': 'mn2'
}]),
CompiledSQL(
"INSERT INTO boss (boss_id, golf_swing) VALUES "
"(:boss_id, :golf_swing)", [{
'boss_id': 4,
'golf_swing': 'g1'
}]))
eq_(objects[0].__dict__['person_id'], 1)
eq_(objects[3].__dict__['person_id'], 4)
eq_(objects[3].__dict__['boss_id'], 4)
class BranchExtractor:
"""
extracts the branches
"""
def __init__(self):
self.current_super = ''
engine = create_engine(
'mysql+pymysql://Super_User:SuperX1234'
'@mysql-13101-0.cloudclusters.net:13101/SuperX',
echo=False)
self.session = Session(bind=engine)
self.branch_unique_constraint_set = set()
def run_branch_extractor(self):
"""
This method is in charge of running the branch information extractor
It works as such:
1. retrieves the xml file, either from link or from the zip file
2. parses the xml file and extracts the relevant information from it
3. creates a list of Branch objects to be committed into the db
4. commits all the Branch objects to the db (per supermarket)
"""
self.create_branch_unique_contraint_set()
for keys in supermarket_info_dictionary:
self.current_super = supermarket_info_dictionary[keys]
try:
if self.current_super['needs_web_scraping']:
zip_link = self.get_zip_file_link()
if self.current_super['need_zip_prefix']:
self.current_super['branch_url'] = self.current_super['branch_url'] \
+ zip_link
else:
self.current_super['branch_url'] = zip_link
xml_file = self.get_xml_file()
except ConnectionError as c_e:
logging.error(str(c_e))
continue
else:
xml_info_list = self.extract_info_from_xml(xml_file)
branch_list = self.fill_branch_table(xml_info_list)
# if there are no branches to add then continue
if len(branch_list) != 0:
self.session.bulk_save_objects(branch_list)
self.session.flush()
self.session.commit()
self.session.close()
def get_zip_file_link(self):
"""
This method web scrapes the urls in url_list and creates a set of the gzip file links.
If connection to the url failed, raises connection error
:return: the link for the zip file
"""
try:
page = requests.get(self.current_super['branch_url'])
web_scrapper = BeautifulSoup(page.content, 'html.parser')
except requests.ConnectionError as c_e:
raise ConnectionError(f'''Unable to retrieve zip file link
for {self.current_super["store_name"]}'''
) from c_e
else:
links_list = web_scrapper.find_all('a')
zip_link = ''
for link in links_list:
if link.has_attr('href'):
https = str(link.attrs['href'])
if self.current_super['link_attrs_name'] in https:
zip_link = https
break
return zip_link
def get_xml_file(self):
"""
This method gets the xml file from the zipfile link
If connection to the url failed, raises connection error
:return: the unparsed xml file
"""
try:
xml_file = ''
request = requests.get(self.current_super['branch_url'])
content = request.content
except requests.ConnectionError as c_e:
raise ConnectionError(f'''Unable to retrieve xml
file for super {self.current_super["store_name"]}''') from c_e
else:
if self.current_super['needs_web_scraping']:
xml_file = gzip.decompress(content).decode(
self.current_super['encoding'])
else:
xml_file = content.decode(self.current_super['encoding'])
return xml_file
def extract_info_from_xml(self, xml_file):
"""
This method parses the xml file and then
extracts info from the xml and packs it into a tuple.
The tuple is then placed into a list
:param xml_file: the parsed xml file
:return: a list of tuples containing all the relevant information
"""
tree = et.fromstring(xml_file)
stores = tree.find(self.current_super['attr_path'])
attrs_dict = self.current_super['attrs']
xml_info_list = []
for store in stores.findall(attrs_dict['store']):
branch_id = store.find(attrs_dict['store_id']).text
branch_name = store.find(attrs_dict['store_name']).text
city = store.find(attrs_dict['city']).text
if city is not None and '-' in city:
city = city.replace("-", " ")
address = store.find(attrs_dict['address']).text
if address is None or address == ' ':
address = city
if city is None:
address = 'none'
sub_chain_id = attrs_dict['sub_chain_id']
if isinstance(attrs_dict['sub_chain_id'], str):
sub_chain_id = store.find(attrs_dict['sub_chain_id']).text
xml_info_list.append(
(branch_id, branch_name, address, sub_chain_id, city))
return xml_info_list
def fill_branch_table(self, xml_info_list):
"""
This method unpacks the tuples in the xml_info_list and creates a list of Branch items
:param xml_info_list: a list of tuples containing all relevant information
:return: a list of Branch objects
"""
branch_list = []
for branch_id, branch_name, address, sub_chain_id, city in xml_info_list:
# if not in db then add it
if (int(branch_id), self.current_super['chain_id']) not in \
self.branch_unique_constraint_set:
branch_list.append(
Branch(id=branch_id,
name=branch_name,
address=address,
sub_chain_id=sub_chain_id,
city=city,
chain_id=self.current_super['chain_id']))
self.branch_unique_constraint_set.add(
(int(branch_id), self.current_super['chain_id']))
return branch_list
def create_branch_unique_contraint_set(self):
"""
queries the DB for all rows and saves the unique contraints in a set
"""
all_rows = Branch.query.all()
for row in all_rows:
self.branch_unique_constraint_set.add((row.id, row.chain_id))
def test_bulk_save_joined_inh_no_defaults(self):
Person, Engineer, Manager, Boss = self.classes(
"Person", "Engineer", "Manager", "Boss"
)
s = Session()
with self.sql_execution_asserter() as asserter:
s.bulk_save_objects(
[
Manager(
person_id=1, name="m1", status="s1", manager_name="mn1"
),
Engineer(
person_id=2,
name="e1",
status="s2",
primary_language="l1",
),
Engineer(
person_id=3,
name="e2",
status="s3",
primary_language="l2",
),
Boss(
person_id=4,
boss_id=4,
name="b1",
status="s3",
manager_name="mn2",
golf_swing="g1",
),
]
)
# the only difference here is that common classes are grouped together.
# at the moment it doesn't lump all the "people" tables from
# different classes together.
asserter.assert_(
CompiledSQL(
"INSERT INTO people (person_id, name, type) VALUES "
"(:person_id, :name, :type)",
[{"person_id": 1, "type": "manager", "name": "m1"}],
),
CompiledSQL(
"INSERT INTO managers (person_id, status, manager_name) "
"VALUES (:person_id, :status, :manager_name)",
[{"status": "s1", "person_id": 1, "manager_name": "mn1"}],
),
CompiledSQL(
"INSERT INTO people (person_id, name, type) VALUES "
"(:person_id, :name, :type)",
[
{"person_id": 2, "type": "engineer", "name": "e1"},
{"person_id": 3, "type": "engineer", "name": "e2"},
],
),
CompiledSQL(
"INSERT INTO engineers (person_id, status, primary_language) "
"VALUES (:person_id, :status, :primary_language)",
[
{"person_id": 2, "status": "s2", "primary_language": "l1"},
{"person_id": 3, "status": "s3", "primary_language": "l2"},
],
),
CompiledSQL(
"INSERT INTO people (person_id, name, type) VALUES "
"(:person_id, :name, :type)",
[{"person_id": 4, "type": "boss", "name": "b1"}],
),
CompiledSQL(
"INSERT INTO managers (person_id, status, manager_name) "
"VALUES (:person_id, :status, :manager_name)",
[{"status": "s3", "person_id": 4, "manager_name": "mn2"}],
),
CompiledSQL(
"INSERT INTO boss (boss_id, golf_swing) VALUES "
"(:boss_id, :golf_swing)",
[{"boss_id": 4, "golf_swing": "g1"}],
),
)
class DatabaseManager:
def __init__(self, connectionstring, electiondate):
self.electiondate = electiondate
self.engine = create_engine(connectionstring)
self.conn = self.engine.connect()
self.session = Session(self.engine)
self.Base = automap_base()
self.Base.prepare(self.engine, reflect=True)
self.Election = self.Base.classes.election
self.Constituency = self.Base.classes.constituency
self.District = self.Base.classes.district
self.JudicalDistrict = self.Base.classes.judicaldistrict
self.Party = self.Base.classes.party
self.Candidacy = self.Base.classes.candidacy
self.Votes = self.Base.classes.votes
self.TotalVotes = self.Base.classes.totalvotes
self.Projection = self.Base.classes.projection
self.ProjectionResult = self.Base.classes.projectionresult
self.enr = first(self.session.query(self.Election.nr).filter(self.Election.dt == self.electiondate).all())
if self.enr is None:
raise Exception("Invalid election date")
else:
self.enr = self.enr[0]
def close(self):
self.conn.close()
self.engine.dispose()
def write(self, data):
self.session.query(self.ProjectionResult).filter(self.ProjectionResult.enr == self.enr).delete(
synchronize_session=False)
self.session.query(self.Projection).filter(self.Projection.enr == self.enr).delete(synchronize_session=False)
self.session.query(self.TotalVotes).filter(self.TotalVotes.enr == self.enr).delete(synchronize_session=False)
self.session.query(self.Votes).filter(self.Votes.enr == self.enr).delete(synchronize_session=False)
self.session.query(self.JudicalDistrict).filter(self.JudicalDistrict.enr == self.enr).delete(
synchronize_session=False)
parties = {}
for key in data[0].keys():
if key not in ["SPR", "BZ", "WBER", "ABG", "UNG", "T", "WV", "WK"]:
parties[key] = self.session.query(self.Party.nr).filter(self.Party.abbr == key).first()[0]
districts = []
votes = []
for line in data:
districts.append(self.JudicalDistrict(
enr=self.enr,
dnr=int(line["BZ"]),
nr=int(line["SPR"]),
electivecnt=int(line["WBER"]),
invalidcnt=int(line["UNG"]),
votecnt=int(line["ABG"])
))
for party, pnr in parties.items():
votes.append(self.Votes(
enr=self.enr,
dnr=int(line["BZ"]),
jdnr=int(line["SPR"]),
pnr=pnr,
cnt=int(line[party])
))
self.session.bulk_save_objects(districts)
self.session.bulk_save_objects(votes)
self.session.commit()
def load(self):
header = OrderedSet(["WK", "BZ", "SPR", "WBER", "ABG", "UNG"])
parties = {}
for party in self.session.query(self.Party).all():
parties[party.nr] = party.abbr
header.add(party.abbr)
query = "SELECT constituency.nr AS cnr, district.nr AS dnr, judicaldistrict.nr AS jdnr, judicaldistrict.electivecnt, " \
"judicaldistrict.votecnt, judicaldistrict.invalidcnt, votes.pnr, votes.cnt " \
"FROM constituency " \
"INNER JOIN district ON constituency.nr = district.cnr " \
"INNER JOIN judicaldistrict ON district.nr = judicaldistrict.dnr " \
"AND judicaldistrict.enr = '" + str(self.enr) + "' " \
"INNER JOIN votes ON votes.enr = '" + str(
self.enr) + "' " \
"AND votes.dnr = district.nr " \
"AND votes.jdnr = judicaldistrict.nr;"
result = self.session.execute(query)
data = {}
for row in result:
key = str(row["cnr"]) + str(row["dnr"]) + str(row["jdnr"])
if key not in data:
data[key] = {
"WK": row["cnr"],
"BZ": row["dnr"],
"SPR": row["jdnr"],
"WBER": row["electivecnt"],
"ABG": row["votecnt"],
"UNG": row["invalidcnt"],
"T": 4,
"WV": 1,
}
data[key][parties[row["pnr"]]] = row["cnt"]
return list(data.values())
def create_projection(self):
"""
Cudos an Rene Hollander
"""
ts = datetime.datetime.now().time().strftime("%H:%M:%S")
connection = self.engine.raw_connection()
cursor = connection.cursor()
cursor.callproc("create_projection", [self.enr, ts])
cursor.close()
connection.commit()
self.session.commit()
result = self.session.query(self.Party.abbr, self.ProjectionResult.percentage) \
.select_from(self.Party).join(self.ProjectionResult, self.Party.nr == self.ProjectionResult.pnr) \
.filter(self.ProjectionResult.enr == self.enr, self.ProjectionResult.ts == ts) \
.all()
data = {}
for row in result:
data[row.abbr] = float(row.percentage)
return data
class WienWahlDatabase:
def __init__(self, connectionstring, electiondate):
self.electiondate = electiondate
self.engine = create_engine(connectionstring)
self.conn = self.engine.connect()
self.session = Session(self.engine)
self.Base = automap_base()
self.Base.prepare(self.engine, reflect=True)
self.Election = self.Base.classes.election
self.Constituency = self.Base.classes.constituency
self.District = self.Base.classes.district
self.JudicalDistrict = self.Base.classes.judicaldistrict
self.Party = self.Base.classes.party
self.Candidacy = self.Base.classes.candidacy
self.Votes = self.Base.classes.votes
self.TotalVotes = self.Base.classes.totalvotes
self.Projection = self.Base.classes.projection
self.ProjectionResult = self.Base.classes.projectionresult
self.enr = first(self.session.query(self.Election.nr).filter(self.Election.dt == self.electiondate).all())
if self.enr is None:
raise Exception("Invalid election date")
else:
self.enr = self.enr[0]
def close(self):
self.conn.close()
self.engine.dispose()
def write(self, data):
self.session.query(self.ProjectionResult).filter(self.ProjectionResult.enr == self.enr).delete(synchronize_session=False)
self.session.query(self.Projection).filter(self.Projection.enr == self.enr).delete(synchronize_session=False)
self.session.query(self.TotalVotes).filter(self.TotalVotes.enr == self.enr).delete(synchronize_session=False)
self.session.query(self.Votes).filter(self.Votes.enr == self.enr).delete(synchronize_session=False)
self.session.query(self.JudicalDistrict).filter(self.JudicalDistrict.enr == self.enr).delete(synchronize_session=False)
parties = {}
for key in data[0].keys():
if key not in default_header:
parties[key] = self.session.query(self.Party.nr).filter(self.Party.abbr == key).first()[0]
districts = []
votes = []
for line in data:
districts.append(self.JudicalDistrict(
enr=self.enr,
dnr=int(line["BZ"]),
nr=int(line["SPR"]),
electivecnt=int(line["WBER"]),
invalidcnt=int(line["UNG"]),
votecnt=int(line["ABG"])
))
for party, pnr in parties.items():
votes.append(self.Votes(
enr=self.enr,
dnr=int(line["BZ"]),
jdnr=int(line["SPR"]),
pnr=pnr,
cnt=int(line[party])
))
self.session.bulk_save_objects(districts)
self.session.bulk_save_objects(votes)
self.session.commit()
def load(self):
header = OrderedSet(default_header)
parties = {}
for party in self.session.query(self.Party).all():
parties[party.nr] = party.abbr
header.add(party.abbr)
query = "SELECT constituency.nr AS cnr, district.nr AS dnr, judicaldistrict.nr AS jdnr, judicaldistrict.electivecnt, " \
"judicaldistrict.votecnt, judicaldistrict.invalidcnt, votes.pnr, votes.cnt " \
"FROM constituency " \
"INNER JOIN district ON constituency.nr = district.cnr " \
"INNER JOIN judicaldistrict ON district.nr = judicaldistrict.dnr " \
"AND judicaldistrict.enr = '" + str(self.enr) + "' " \
"INNER JOIN votes ON votes.enr = '" + str(self.enr) + "' " \
"AND votes.dnr = district.nr " \
"AND votes.jdnr = judicaldistrict.nr;"
result = self.session.execute(query)
data = {}
for row in result:
key = str(row["cnr"]) + str(row["dnr"]) + str(row["jdnr"])
if key not in data:
data[key] = {
"WK": row["cnr"],
"BZ": row["dnr"],
"SPR": row["jdnr"],
"WBER": row["electivecnt"],
"ABG": row["votecnt"],
"UNG": row["invalidcnt"],
"T": 4,
"WV": 1,
}
data[key][parties[row["pnr"]]] = row["cnt"]
return header, list(data.values())
def create_projection(self):
ts = datetime.now().time().strftime("%H:%M:%S")
connection = self.engine.raw_connection()
cursor = connection.cursor()
cursor.callproc("create_projection", [self.enr, ts])
cursor.close()
connection.commit()
self.session.commit()
result = self.session.query(self.Party.abbr, self.ProjectionResult.percentage) \
.select_from(self.Party).join(self.ProjectionResult, self.Party.nr == self.ProjectionResult.pnr) \
.filter(self.ProjectionResult.enr == self.enr, self.ProjectionResult.ts == ts) \
.all()
data = {}
for row in result:
data[row.abbr] = float(row.percentage)
return data
def import_block(session: orm.Session, block_ir: BlockIR) -> None:
header = Header.from_ir(block_ir.header)
transactions = tuple(
Transaction.from_ir(transaction_ir, block_header_hash=Hash32(header.hash))
for transaction_ir in block_ir.transactions
)
uncles = tuple(Header.from_ir(uncle_ir) for uncle_ir in block_ir.uncles)
receipts = tuple(
Receipt.from_ir(receipt_ir, Hash32(transaction.hash))
for transaction, receipt_ir in zip(transactions, block_ir.receipts)
)
log_bundles = tuple(
tuple(
Log.from_ir(log_ir, idx, Hash32(receipt.transaction_hash))
for idx, log_ir in enumerate(receipt_ir.logs)
)
for receipt, receipt_ir in zip(receipts, block_ir.receipts)
)
logs = tuple(itertools.chain(*log_bundles))
block = Block(header_hash=header.hash)
block_uncles = tuple(
BlockUncle(idx=idx, block_header_hash=block.header_hash, uncle_hash=uncle.hash)
for idx, uncle in enumerate(uncles)
)
block_transactions = tuple(
BlockTransaction(
idx=idx,
block_header_hash=block.header_hash,
transaction_hash=transaction.hash,
)
for idx, transaction in enumerate(transactions)
)
# These need to be lazily created.
topic_values = tuple(
topic
for receipt_ir in block_ir.receipts
for log_ir in receipt_ir.logs
for topic in log_ir.topics
)
topics = get_or_create_topics(session, topic_values)
logtopics = tuple(
LogTopic(idx=idx, topic_topic=topic, log=log)
for bundle, receipt_ir in zip(log_bundles, block_ir.receipts)
for log, log_ir in zip(bundle, receipt_ir.logs)
for idx, topic in enumerate(log_ir.topics)
)
objects_to_save = tuple(
itertools.chain(
(header, block),
uncles,
transactions,
receipts,
logs,
block_uncles,
block_transactions,
topics,
logtopics,
)
)
session.bulk_save_objects(objects_to_save)
def test_bulk_save_joined_inh_no_defaults(self):
Person, Engineer, Manager, Boss = \
self.classes('Person', 'Engineer', 'Manager', 'Boss')
s = Session()
with self.sql_execution_asserter() as asserter:
s.bulk_save_objects([
Manager(
person_id=1,
name='m1', status='s1', manager_name='mn1'),
Engineer(
person_id=2,
name='e1', status='s2', primary_language='l1'),
Engineer(
person_id=3,
name='e2', status='s3', primary_language='l2'),
Boss(
person_id=4, boss_id=4,
name='b1', status='s3', manager_name='mn2',
golf_swing='g1')
],
)
# the only difference here is that common classes are grouped together.
# at the moment it doesn't lump all the "people" tables from
# different classes together.
asserter.assert_(
CompiledSQL(
"INSERT INTO people (person_id, name, type) VALUES "
"(:person_id, :name, :type)",
[{'person_id': 1, 'type': 'manager', 'name': 'm1'}]
),
CompiledSQL(
"INSERT INTO managers (person_id, status, manager_name) "
"VALUES (:person_id, :status, :manager_name)",
[{'status': 's1', 'person_id': 1, 'manager_name': 'mn1'}]
),
CompiledSQL(
"INSERT INTO people (person_id, name, type) VALUES "
"(:person_id, :name, :type)",
[{'person_id': 2, 'type': 'engineer', 'name': 'e1'},
{'person_id': 3, 'type': 'engineer', 'name': 'e2'}]
),
CompiledSQL(
"INSERT INTO engineers (person_id, status, primary_language) "
"VALUES (:person_id, :status, :primary_language)",
[{'person_id': 2, 'status': 's2', 'primary_language': 'l1'},
{'person_id': 3, 'status': 's3', 'primary_language': 'l2'}]
),
CompiledSQL(
"INSERT INTO people (person_id, name, type) VALUES "
"(:person_id, :name, :type)",
[{'person_id': 4, 'type': 'boss', 'name': 'b1'}]
),
CompiledSQL(
"INSERT INTO managers (person_id, status, manager_name) "
"VALUES (:person_id, :status, :manager_name)",
[{'status': 's3', 'person_id': 4, 'manager_name': 'mn2'}]
),
CompiledSQL(
"INSERT INTO boss (boss_id, golf_swing) VALUES "
"(:boss_id, :golf_swing)",
[{'boss_id': 4, 'golf_swing': 'g1'}]
)
)
def test_bulk_save_joined_inh_return_defaults(self):
Person, Engineer, Manager, Boss = \
self.classes('Person', 'Engineer', 'Manager', 'Boss')
s = Session()
objects = [
Manager(name='m1', status='s1', manager_name='mn1'),
Engineer(name='e1', status='s2', primary_language='l1'),
Engineer(name='e2', status='s3', primary_language='l2'),
Boss(
name='b1', status='s3', manager_name='mn2',
golf_swing='g1')
]
assert 'person_id' not in objects[0].__dict__
with self.sql_execution_asserter() as asserter:
s.bulk_save_objects(objects, return_defaults=True)
asserter.assert_(
CompiledSQL(
"INSERT INTO people (name, type) VALUES (:name, :type)",
[{'type': 'manager', 'name': 'm1'}]
),
CompiledSQL(
"INSERT INTO managers (person_id, status, manager_name) "
"VALUES (:person_id, :status, :manager_name)",
[{'person_id': 1, 'status': 's1', 'manager_name': 'mn1'}]
),
CompiledSQL(
"INSERT INTO people (name, type) VALUES (:name, :type)",
[{'type': 'engineer', 'name': 'e1'}]
),
CompiledSQL(
"INSERT INTO people (name, type) VALUES (:name, :type)",
[{'type': 'engineer', 'name': 'e2'}]
),
CompiledSQL(
"INSERT INTO engineers (person_id, status, primary_language) "
"VALUES (:person_id, :status, :primary_language)",
[{'person_id': 2, 'status': 's2', 'primary_language': 'l1'},
{'person_id': 3, 'status': 's3', 'primary_language': 'l2'}]
),
CompiledSQL(
"INSERT INTO people (name, type) VALUES (:name, :type)",
[{'type': 'boss', 'name': 'b1'}]
),
CompiledSQL(
"INSERT INTO managers (person_id, status, manager_name) "
"VALUES (:person_id, :status, :manager_name)",
[{'person_id': 4, 'status': 's3', 'manager_name': 'mn2'}]
),
CompiledSQL(
"INSERT INTO boss (boss_id, golf_swing) VALUES "
"(:boss_id, :golf_swing)",
[{'boss_id': 4, 'golf_swing': 'g1'}]
)
)
eq_(objects[0].__dict__['person_id'], 1)
eq_(objects[3].__dict__['person_id'], 4)
eq_(objects[3].__dict__['boss_id'], 4)
def test_bulk_save_return_defaults(self):
User, = self.classes("User",)
s = Session()
objects = [
User(name="u1"),
User(name="u2"),
User(name="u3")
]
assert 'id' not in objects[0].__dict__
with self.sql_execution_asserter() as asserter:
s.bulk_save_objects(objects, return_defaults=True)
asserter.assert_(
CompiledSQL(
"INSERT INTO users (name) VALUES (:name)",
[{'name': 'u1'}]
),
CompiledSQL(
"INSERT INTO users (name) VALUES (:name)",
[{'name': 'u2'}]
),
CompiledSQL(
"INSERT INTO users (name) VALUES (:name)",
[{'name': 'u3'}]
),
)
eq_(objects[0].__dict__['id'], 1)
def test_insert_w_fetch(self):
A = self.classes.A
s = Session()
a1 = A(x=1)
s.bulk_save_objects([a1])
s.commit()
def test_bulk_update_via_save(self):
Foo = self.classes.Foo
s = Session()
s.add(Foo(value="value"))
s.commit()
f1 = s.query(Foo).first()
f1.value = "new value"
s.bulk_save_objects([f1])
s.expunge_all()
eq_(s.query(Foo).all(), [Foo(version_id=2, value="new value")])
def test_bulk_save_no_defaults(self):
User, = self.classes("User")
s = Session()
objects = [User(name="u1"), User(name="u2"), User(name="u3")]
assert "id" not in objects[0].__dict__
with self.sql_execution_asserter() as asserter:
s.bulk_save_objects(objects)
asserter.assert_(
CompiledSQL(
"INSERT INTO users (name) VALUES (:name)",
[{"name": "u1"}, {"name": "u2"}, {"name": "u3"}],
)
)
assert "id" not in objects[0].__dict__
def test_update_w_fetch(self):
A = self.classes.A
s = Session()
a1 = A(x=1, y=2)
s.add(a1)
s.commit()
eq_(a1.id, 1) # force a load
a1.x = 5
s.expire(a1, ['y'])
assert 'y' not in a1.__dict__
s.bulk_save_objects([a1])
s.commit()
eq_(a1.x, 5)
eq_(a1.y, 2)
