def get_engine_for_database_with_ssl_certs( cls, *, database_key: SQLAlchemyDatabaseKey, ssl_cert_path: str) -> Engine: db_user = cls._get_db_user(database_key=database_key) db_password = cls._get_db_password(database_key=database_key) host_name = cls._get_db_host(database_key=database_key) db_name = database_key.db_name url = URL.create( drivername="postgresql", username=db_user, password=db_password, host=host_name, database=db_name, ) return create_engine( url, connect_args={ "sslmode": "require", "sslcert": os.path.join(ssl_cert_path, "client-cert.pem"), "sslkey": os.path.join(ssl_cert_path, "client-key.pem"), "sslrootcert": os.path.join(ssl_cert_path, "server-ca.pem"), }, )
def handler(self): try: gethostbyname(self.hostname) except gaierror as exc: self.error( _("Host name resolution failed: {}.").format( exc.strerror.lower())) return url = EngineURL.create('postgresql+psycopg2', host=self.hostname, port=self.port, database=self.database, username=self.username, password=self.password) engine = create_engine(url, client_encoding='utf-8', poolclass=NullPool, connect_args=dict(connect_timeout=5)) try: conn = self._conn = engine.connect() except OperationalError as exc: self.error(_("Failed to connect to the database.")) return self.inject(conn) self.success(_("Connected to the database.")) conn.execute(sql.text("SELECT 1")) self.success(_("Executed {} query.").format('SELECT 1')) ver = conn.execute( sql.text("SHOW server_version")).scalar().split(' ')[0] self.success(_("PostgreSQL version {}.").format(ver))
def _get_sqlalchemy_url(use_ssl: bool = True) -> str: """Returns string used for SQLAlchemy engine, with SSL params if |use_ssl| is True.""" user = os.getenv(SQLALCHEMY_DB_USER) password = os.getenv(SQLALCHEMY_DB_PASSWORD) host = os.getenv(SQLALCHEMY_DB_HOST) db_name = os.getenv(SQLALCHEMY_DB_NAME) url = URL.create( drivername=_DB_TYPE, username=user, password=password, database=db_name, host=host, ) if use_ssl: ssl_key_path = os.getenv(SQLALCHEMY_SSL_KEY_PATH) ssl_cert_path = os.getenv(SQLALCHEMY_SSL_CERT_PATH) url = url.update_query_dict( { "sslkey": ssl_key_path, "sslcert": ssl_cert_path }, append=True) return url.render_as_string(hide_password=False)
def _create_database(self) -> sqlalchemy.orm.sessionmaker: if self._is_disabled(): # Use in-memory database as mock to avoid having to check `_is_disabled` # everywhere database = "" else: database = str(self._cache_location / self._cache_database_name) # Use `future=True` as we are using the 2.x query style engine = sa.create_engine( URL.create(drivername="sqlite", database=database), future=True ) self.Base.metadata.create_all(engine) return sa.orm.sessionmaker(engine)
def __init__( self, conn_url: str = None, config: Dynaconf = settings, ) -> None: """Instantiates the FasterWeb class and connects to the database""" if not conn_url: conn_str = ("Driver={SQL Server};" f"Server={config.faster_web_server};" f"Database={config.faster_web_db};" "Trusted_Connection=yes;") pyodbc.pool = False conn_url = URL.create("mssql+pyodbc", query={"odbc_connect": conn_str}) self.engine = sqlalchemy.create_engine(conn_url)
def database_url( cls, v: Optional[Union[SecretStr, SQLAlchemy_DB_URL]], values: dict) -> Optional[Union[SecretStr, SQLAlchemy_DB_URL]]: if v is None: pw_secret = values["sqlalchemy_db_password"] return SQLAlchemy_DB_URL.create( drivername=values["sqlalchemy_db_drivername"], username=values["sqlalchemy_db_user"], password=(pw_secret.get_secret_value() if isinstance( pw_secret, SecretStr) else pw_secret), host=values["sqlalchemy_db_host"], port=values["sqlalchemy_db_port"], database=values["sqlalchemy_db_database"], ) return v
def global_init(server, database, username, password): global __factory if __factory: return if not server and not database: raise Exception("Необходимо указать сервер и/или имя базы данных.") driver = '{SQL Server}' connection_string = f"DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password}" # print(f"Подключение к базе данных по адресу {connection_string}") connection_url = URL.create("mssql+pyodbc", query={"odbc_connect": connection_string}) engine = create_engine(connection_url) __factory = orm.sessionmaker(bind=engine) SqlAlchemyBase.metadata.create_all(engine)
def get_server_postgres_instance_url( cls, *, database_key: SQLAlchemyDatabaseKey) -> URL: schema_type = database_key.schema_type instance_id_key = cls._get_cloudsql_instance_id_key(schema_type) if instance_id_key is None: raise ValueError( f"Instance id is not configured for schema type [{schema_type}]" ) db_user = cls._get_db_user(database_key=database_key) db_password = cls._get_db_password(database_key=database_key) db_name = database_key.db_name cloudsql_instance_id = secrets.get_secret(instance_id_key) db_name = database_key.db_name url = URL.create( drivername="postgresql", username=db_user, password=db_password, database=db_name, query={"host": f"/cloudsql/{cloudsql_instance_id}"}, ) return url
def test_engine() -> Engine: """ Return a privileged SQLAlchemy engine with no database. This method is particularly useful for providing an engine that can be used to create and drop databases from an SQL server. :return: SQLAlchemy Engine instance (not connected to a database) """ unix_socket = aurweb.config.get_with_fallback("database", "socket", None) kwargs = { "username": aurweb.config.get("database", "user"), "password": aurweb.config.get_with_fallback("database", "password", None), "host": aurweb.config.get("database", "host"), "port": aurweb.config.get_with_fallback("database", "port", None), "query": { "unix_socket": unix_socket } } backend = aurweb.config.get("database", "backend") driver = aurweb.db.DRIVERS.get(backend) return create_engine(URL.create(driver, **kwargs))
def ConnectToDBServerV2(ServerName: str, DatabaseName: str = 'master'): #Create connection string to connect DBTest database with windows authentication odbc_str = 'DRIVER={SQL Server};SERVER=' + ServerName + ';Database=' + DatabaseName + ';Trusted_Connection=yes;' connection_url = URL.create("mssql+pyodbc", query={'odbc_connect': odbc_str}) return sqlalchemy.create_engine(connection_url)
user="******", password=my_pass.pop()) ''' print("PostgreSQL Verbindung erfolgreich aufgebaut") except (Exception, psycopg2.DatabaseError) as error: print(error) conn_sql = conn_asql tables = [ table.table_name for table in conn_acc.cursor().tables(tableType='TABLE') ] #connect = f"postgresql+psycopg2://{conn_sql.info.user}:{conn_sql.info.password}@{conn_sql.info.host}:{conn_sql.info.port}/{conn_sql.info.dbname}" #engine = create_engine(connect, pool_pre_ping=True) connection_string = "DRIVER={ODBC Driver 17 for SQL Server};SERVER=finversedb.database.windows.net;DATABASE=Muster_DB;UID=finverse;PWD=" connection_url = URL.create("mssql+pyodbc", query={"odbc_connect": connection_string}) engine = create_engine(connection_url, pool_pre_ping=True, fast_executemany=True) while tables: table_name = tables.pop(0) query = 'select * from ' + table_name try: DataFrame = pd.read_sql(query, conn_acc) startTime = datetime.now() # Driver DataFrame.to_sql( table_name, con=engine,
#SQL server DB information driver='{SQL Server}' server = '任意' database = '任意' trusted_connection='yes' #windows authentication #replace other value none_name = '名無し' none_travelcosts = '0' none_date = '99991231' none_pjcode = '99999999' # an connection_string, which the Session will use for connection # resources, typically in module scope connection_string ='DRIVER='+driver+';SERVER='+server+';DATABASE='+database+';PORT=1433;Trusted_Connection='+trusted_connection+';' db_uri = URL.create('mssql+pyodbc', query={'odbc_connect': connection_string}) app.config['SQLALCHEMY_DATABASE_URI'] = db_uri db = SQLAlchemy(app) #SeisanDB class class SeisanDB(db.Model): __tablename__ = 'SeisanDB' id = db.Column(db.Integer, primary_key=True, autoincrement=True,nullable=False) name = db.Column(db.String(200),nullable=False) travel_costs = db.Column(db.Integer,nullable=False) date = db.Column(db.DateTime, default=date,nullable=False) pjcode = db.Column(db.String(200),nullable=False) # avoid to input inccorect travel_costs value def check_travel_costs_format(input_travelcosts): value_true_or_false = False
def get_dialect(self, dialect): dd = URL.create(dialect).get_dialect()() if dialect in {"oracle", "postgresql"}: dd.supports_identity_columns = False return dd
def testInitEngines_usesCorrectIsolationLevels( self, mock_get_secret: mock.MagicMock, mock_in_gcp: mock.MagicMock, mock_in_production: mock.MagicMock, mock_create_engine: mock.MagicMock, mock_get_states: mock.MagicMock, ) -> None: # Arrange mock_in_gcp.return_value = True mock_in_production.return_value = True # Pretend all secret values are just the key suffixed with '_value' mock_get_secret.side_effect = lambda key: f"{key}_value" # Act SQLAlchemyEngineManager.attempt_init_engines_for_server( set(schema_utils.SchemaType)) # Assert self.assertEqual( mock_create_engine.call_args_list, [ call( URL.create( drivername="postgresql", username="******", password="******", database="postgres", query={ "host": "/cloudsql/sqlalchemy_cloudsql_instance_id_value" }, ), isolation_level=None, poolclass=None, echo_pool=True, pool_recycle=600, ), call( URL.create( drivername="postgresql", username="******", password="******", database="postgres", query={ "host": "/cloudsql/state_cloudsql_instance_id_value" }, ), isolation_level="SERIALIZABLE", poolclass=sqlalchemy.pool.NullPool, echo_pool=True, pool_recycle=600, ), call( URL.create( drivername="postgresql", username="******", password="******", database="postgres", query={ "host": "/cloudsql/operations_cloudsql_instance_id_value" }, ), isolation_level=None, poolclass=None, pool_size=2, max_overflow=5, pool_timeout=15, echo_pool=True, pool_recycle=600, ), call( URL.create( drivername="postgresql", username="******", password="******", database="postgres", query={ "host": "/cloudsql/justice_counts_cloudsql_instance_id_value" }, ), isolation_level="SERIALIZABLE", poolclass=None, echo_pool=True, pool_recycle=600, ), call( URL.create( drivername="postgresql", username="******", password="******", database="postgres", query={ "host": "/cloudsql/case_triage_cloudsql_instance_id_value" }, ), isolation_level=None, poolclass=None, echo_pool=True, pool_recycle=600, ), call( URL.create( drivername="postgresql", username="******", password="******", database="us_xx_primary", query={ "host": "/cloudsql/state_cloudsql_instance_id_value" }, ), isolation_level="SERIALIZABLE", poolclass=sqlalchemy.pool.NullPool, echo_pool=True, pool_recycle=600, ), call( URL.create( drivername="postgresql", username="******", password="******", database="us_ww_primary", query={ "host": "/cloudsql/state_cloudsql_instance_id_value" }, ), isolation_level="SERIALIZABLE", poolclass=sqlalchemy.pool.NullPool, echo_pool=True, pool_recycle=600, ), call( URL.create( drivername="postgresql", username="******", password="******", database="us_xx_secondary", query={ "host": "/cloudsql/state_cloudsql_instance_id_value" }, ), isolation_level="SERIALIZABLE", poolclass=sqlalchemy.pool.NullPool, echo_pool=True, pool_recycle=600, ), call( URL.create( drivername="postgresql", username="******", password="******", database="us_ww_secondary", query={ "host": "/cloudsql/state_cloudsql_instance_id_value" }, ), isolation_level="SERIALIZABLE", poolclass=sqlalchemy.pool.NullPool, echo_pool=True, pool_recycle=600, ), ], ) mock_get_states.assert_called()
class BaseConfig: CONFIG_NAME = "base" ENV = 'development' FLASK_DEBUG = 0 SQLALCHEMY_TRACK_MODIFICATIONS = False SQLALCHEMY_DATABASE_URI = URL.create(**DATABASE)
class TestingConfig(BaseConfig): CONFIG_NAME = "test" SECRET_KEY = os.getenv("TEST_SECRET_KEY", "Если сразу не получилось хорошо, назовите это версией 1.0") DEBUG = True TESTING = True SQLALCHEMY_DATABASE_URI = URL.create(**TEST_DATABASE)