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)