def __init__(
self,
simple_or_comprehensive:
SimpleOrComprehensiveArgEnum = SimpleOrComprehensiveArgEnum.simple,
db_file_path: typing.Union[str, None] = None,
download_url: typing.Union[str, None] = None,
engine: Engine = None,
):
validate_enum_arg(
self.SimpleOrComprehensiveArgEnum,
"simple_or_comprehensive",
simple_or_comprehensive,
)
self.simple_or_comprehensive = simple_or_comprehensive
if isinstance(engine, Engine):
self.db_file_path = None
self.download_url = None
self.engine = engine
else:
self.db_file_path = db_file_path
self.download_url = download_url
self._download_db_file_if_not_exists()
self.engine = sam.EngineCreator().create_sqlite(
path=self.db_file_path)
self.eng = self.engine
self.session = orm.Session(self.engine)
self.ses = self.session
self.zip_klass: typing.Union[SimpleZipcode, ComprehensiveZipcode]
if self.simple_or_comprehensive is self.SimpleOrComprehensiveArgEnum.simple:
self.zip_klass = SimpleZipcode
elif self.simple_or_comprehensive is self.SimpleOrComprehensiveArgEnum.comprehensive:
self.zip_klass = ComprehensiveZipcode
def test_custom_engine():
# ensure simple db file exists
_ = SearchEngine()
# use custom db engine
engine = sam.EngineCreator().create_sqlite(
path=DEFAULT_SIMPLE_DB_FILE_PATH)
sr = SearchEngine(engine=engine)
z = sr.by_zipcode("10001")
assert z.state == "NY"
@hybrid_method
def intersects(self, other):
return self.contains(other.start) | self.contains(other.end)
@hybrid_property
def radius(self):
return abs(self.length) / 2
# 显示告知相对应的 SQL expression
# 特别注意, 这个 expression 的 name 要和 hybrid_property 的名字一摸一样
@radius.expression
def radius(cls):
return sa.func.abs(cls.length) / 2
engine = sam.EngineCreator().create_sqlite()
Base.metadata.create_all(engine)
with orm.Session(engine) as ses:
ses.add_all([
Interval(id=1, start=0, end=10),
Interval(id=2, start=5, end=15),
Interval(id=3, start=10, end=20),
Interval(id=4, start=15, end=30),
])
ses.commit()
res = ses.scalars(sa.select(Interval).where(Interval.length <= 10)).all()
print(res) # id = 1, 2, 3
res = ses.scalars(sa.select(Interval).where(Interval.contains(7))).all()
__tablename__ = "address"
id = sa.Column(sa.Integer, primary_key=True)
user_id = sa.Column(sa.Integer, sa.ForeignKey("user.id"))
class User(Base, sam.ExtendedBase):
__tablename__ = "user"
id = sa.Column(sa.Integer, primary_key=True)
address_count = orm.column_property(
sa.select(sa.func.count(Address.id)).where(
Address.user_id == id).correlate_except(Address).scalar_subquery())
engine = sam.EngineCreator().create_sqlite(echo=True)
Base.metadata.create_all(engine)
with orm.Session(engine) as ses:
ses.add_all([
User(id=1),
User(id=2),
])
ses.add_all([
Address(id=1, user_id=1),
Address(id=2, user_id=1),
Address(id=3, user_id=1),
Address(id=4, user_id=2),
Address(id=5, user_id=2),
Address(id=6, user_id=2),
Address(id=7, user_id=2),
一个完美的方案,
Ref:
- https://docs.sqlalchemy.org/en/latest/orm/extensions/horizontal_shard.html
"""
import random
import sqlalchemy as sa
from sqlalchemy.orm import declarative_base, ORMExecuteState
from sqlalchemy.sql import visitors
from sqlalchemy.sql.selectable import Select
from sqlalchemy.ext.horizontal_shard import ShardedQuery, ShardedSession
import sqlalchemy_mate as sam
engine1 = sam.EngineCreator().create_sqlite()
engine2 = sam.EngineCreator().create_sqlite()
engine3 = sam.EngineCreator().create_sqlite()
engine4 = sam.EngineCreator().create_sqlite()
engine_list = [engine1, engine2, engine3, engine4]
Base = declarative_base()
class User(Base, sam.ExtendedBase):
__tablename__ = "user"
id = sa.Column(sa.Integer, primary_key=True)
for engine in engine_list:
# -*- coding: utf-8 -*-
import sqlalchemy_mate as sam
engine_sqlite = sam.EngineCreator().create_sqlite()
engine_psql = sam.EngineCreator(
host="localhost",
port=38835,
database="postgres",
username="******",
password="******",
).create_postgresql_pg8000()
questions = relationship(
"Question",
secondary=question_and_tag_association_table,
back_populates="tags",
)
@property
def n_question(self):
return len(self.questions)
engine = sam.EngineCreator(
host="localhost",
port=43348,
database="postgres",
username="******",
password="******",
).create_postgresql_pg8000()
sam.test_connection(engine, timeout=3)
class Test:
@classmethod
def setup_class(cls):
question_and_tag_association_table
Question
Tag
tag_py = Tag(id=1, name="Python")
tag_py2 = Tag(id=2, name="Python2")
# -*- coding: utf-8 -*-
import sqlalchemy as sa
import sqlalchemy.orm as orm
import sqlalchemy_mate as sam
from uszipcode.model import SimpleZipcode, ComprehensiveZipcode
db_file_path = "/Users/sanhehu/.crawl_uszipcode/comprehensive.sqlite"
engine = sam.EngineCreator().create_sqlite(path=db_file_path)
Zipcode = ComprehensiveZipcode
with orm.Session(engine) as ses:
stmt = sa.select(
Zipcode.zipcode,
Zipcode.major_city,
Zipcode.state,
Zipcode.population,
).where(Zipcode.population.between(10000, 50000)).limit(10)
res = ses.execute(stmt)
print(sam.pt.from_result(res))
# stmt = sa.select(Zipcode).limit(20)
# res = ses.execute(stmt)
# sam.pt.from_everything(Zipcode, ses)
# n = Zipcode.count_all(ses)
# print(n)
import json
from pathlib_mate import Path
l = sam.selecting.select_single_column(engine, Zipcode.__table__.c.zipcode)
