from sqlalchemy.dialects import postgresql, mysql, sqlite from sqlalchemy import func Base = declarative_base() from sqlalchemy.dialects import registry registry.register('spatialite', 'ambry.orm.dialects.spatialite', 'SpatialiteDialect') registry.register('postgis', 'ambry.orm.dialects.postgis', 'PostgisDialect') # http://stackoverflow.com/a/23175518/1144479 # SQLAlchemy does not map BigInt to Int by default on the sqlite dialect. # It should, but it doesnt. BigIntegerType = BigInteger() BigIntegerType = BigIntegerType.with_variant(postgresql.BIGINT(), 'postgresql') BigIntegerType = BigIntegerType.with_variant(mysql.BIGINT(), 'mysql') BigIntegerType = BigIntegerType.with_variant(sqlite.INTEGER(), 'sqlite') class Geometry(UserDefinedType): """Geometry type, to ensure that WKT text is properly inserted into the database with the GeomFromText() function. NOTE! This is paired with code in database.relational.RelationalDatabase.table() to convert NUMERIC fields that have the name 'geometry' to GEOMETRY types. Sqlalchemy sees spatialte GEOMETRY types as NUMERIC """
from sqlalchemy.ext.declarative import declarative_base from sqlalchemy_utils import UUIDType, JSONType from sqlalchemy import BigInteger, Column, DateTime from sqlalchemy.sql import func import sqlalchemy from sqlalchemy.dialects import sqlite, postgresql from sqlalchemy.orm.attributes import flag_modified BigIntegerType = BigInteger() BigIntegerType = BigIntegerType.with_variant(postgresql.BIGINT(), "postgresql") BigIntegerType = BigIntegerType.with_variant(sqlite.INTEGER(), "sqlite") DeclarativeBase = declarative_base() PkType = BigIntegerType ExtPkType = UUIDType(binary=False) import uuid from typing import Optional class Base(DeclarativeBase): # type: ignore __abstract__ = True id = Column(PkType, primary_key=True) ext_id = Column(ExtPkType, default=lambda: uuid.uuid4(), nullable=False, unique=True) created_at = Column(DateTime, server_default=func.now())
from sqlalchemy.dialects import sqlite from sqlalchemy import Column, Integer, BigInteger, String from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from sqlalchemy.types import DateTime from sqlalchemy.sql import expression from sqlalchemy.ext.compiler import compiles from sqlalchemy import event from sqlalchemy.engine import Engine from sqlalchemy.orm.exc import NoResultFound # SO much boilerplate BigIntegerType = BigInteger() BigIntegerType = BigIntegerType.with_variant(sqlite.INTEGER(), "sqlite") # create a custom utcnow function class utcnow(expression.FunctionElement): type = DateTime() @compiles(utcnow, "sqlite") def sqlite_utcnow(element, compiler, **kw): return "CURRENT_TIMESTAMP" class AppMixin(object): """ Provide common attributes to our models
from sqlalchemy import BigInteger from sqlalchemy.dialects import postgresql, mysql, sqlite KeyInteger = BigInteger() KeyInteger = KeyInteger.with_variant(postgresql.BIGINT(), 'postgresql') KeyInteger = KeyInteger.with_variant(mysql.BIGINT(), 'mysql') KeyInteger = KeyInteger.with_variant(sqlite.INTEGER(), 'sqlite') class SABase(object): """ Base class for all the DB mapper objects. """ def _commit(self, session, batch, merge=False): if merge: session.merge(self) else: session.add(self) if batch: return session.flush() session.commit() def commit_to_db(self, session, batch=False): """ Commit the DB object/row to the database. @type session: sqlalchemy.orm.scoping.ScopedSession object @param session: SQLAlch session to commit row to. """ self._commit(session, batch)
from sqlalchemy import BigInteger from sqlalchemy.dialects import sqlite # sqlite does not allow BigInteger as a primary key with autoincrement. Use an integer for sqlite (local testing) # but BigInteger elsewhere BigIntegerType = BigInteger() BigIntegerType = BigIntegerType.with_variant(sqlite.INTEGER(), 'sqlite')
""" SQLAlchemy Database Models used to generate and update db tables """ from . import db from sqlalchemy import BigInteger from sqlalchemy.dialects import postgresql, sqlite from flask_login import UserMixin from werkzeug.security import generate_password_hash, check_password_hash from datetime import datetime from application import UNDERGRAD_MAJORS as majors # Fix BigInteger not working in sqlite but still works with postgresql BigInt = BigInteger() BigInt = BigInt.with_variant(postgresql.BIGINT(), 'postgresql') BigInt = BigInt.with_variant(sqlite.INTEGER(), 'sqlite') class User(UserMixin, db.Model): """Model for user accounts.""" __tablename__ = 'users' id = db.Column(db.Integer, primary_key=True) username = db.Column(db.String(64), index=False, unique=False, nullable=False) email = db.Column(db.String(80), index=True, unique=True, nullable=False) password = db.Column(db.String(128), index=False, unique=False, nullable=False) admin = db.Column(db.Boolean(), default=False)
from sqlalchemy import BigInteger, Integer, Column, DateTime, func from sqlalchemy.dialects import postgresql, sqlite from sqlalchemy.orm.session import sessionmaker from sqlalchemy.orm.scoping import scoped_session from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() BigIntegerType = BigInteger() BigIntegerType = BigIntegerType.with_variant(postgresql.BIGINT(), 'postgresql') BigIntegerType = BigIntegerType.with_variant(sqlite.INTEGER(), 'sqlite') _sessionmaker = sessionmaker() session = scoped_session(_sessionmaker) class CommonColumnsMixin(): """ Some common attributes for tables. """ id = Column(Integer, primary_key=True) created_at = Column(DateTime, default=func.now()) updated_at = Column(DateTime, default=func.now(), onupdate=func.now())
# post-contractual effects is governed by the laws of Austria. Any disputes # concerning this License-Agreement including the issue of its valid conclusion # and its pre- and post-contractual effects are exclusively decided by the # competent court, in whose district STRG.AT GmbH has its registered seat, at # the discretion of STRG.AT GmbH also the competent court, in whose district the # Licensee has his registered seat, an establishment or assets. import re from sqlalchemy import Column, ForeignKey, BigInteger, Integer, UniqueConstraint from sqlalchemy.orm import backref, relationship from sqlalchemy.ext.associationproxy import association_proxy from sqlalchemy.ext.orderinglist import ordering_list IdType = BigInteger() IdType = IdType.with_variant(Integer, 'sqlite') # taken from stackoverflow: # http://stackoverflow.com/a/1176023/44562 _first_cap_re = re.compile('(.)([A-Z][a-z]+)') _all_cap_re = re.compile('([a-z0-9])([A-Z])') def cls2tbl(cls): """ Converts a class (or a class name) to a table name. The class name is expected to be in *CamelCase*. The return value will be *seperated_by_underscores* and prefixed with an underscore. Omitting the underscore will yield the name of the class's :ref:`view <db_view>`. """ if isinstance(cls, type): cls = cls.__name__
from sqlalchemy import Integer from sqlalchemy import BigInteger from sqlalchemy import Numeric from sqlalchemy import Boolean from sqlalchemy import ForeignKey from sqlalchemy import DateTime from sqlalchemy import UnicodeText from sqlalchemy.orm import relationship from datetime import datetime from contextlib import contextmanager UnsignedBigInteger = BigInteger() UnsignedBigInteger = UnsignedBigInteger.with_variant( mysql.BIGINT(unsigned=True), 'mysql') UnsignedBigInteger = UnsignedBigInteger.with_variant(sqlite.INTEGER(), 'sqlite') def fk_guid(constraint, table): str_tokens = [ table.name, ] + [element.parent.name for element in constraint.elements ] + [element.target_fullname for element in constraint.elements] print(f'str_tokens: {str_tokens}') guid = uuid.uuid5(uuid.NAMESPACE_OID, "_".join(str_tokens)) return str(guid)