def model_login(conn_params): link = URL(conn_params['database_driver'], username = conn_params['username'], password= conn_params['password'], host = conn_params['host']) if conn_params['connection_database']: link.database = conn_params['connection_database'] elif not conn_params['connection_database'] and conn_params['database_driver'] == 'postgresql': link.database = 'postgres' engine = create_engine(link) conn = '' dict_ret = {} try: conn = engine.connect() except OperationalError as e: dict_ret = {'login': False, 'msg': str(e)} else: # todo 'msg' dict_ret = {'login': True, 'msg': ''} conn.close() return dict_ret
#!/usr/bin/env python3 # -*- coding: utf-8 -*- """ Pedro H Knoll Udacity FullStack NanoDegree Data population script - ver: 0.1 04/2018 """ from sqlalchemy import create_engine from sqlalchemy.engine.url import URL from sqlalchemy.orm import sessionmaker from models import (Base, User, Category, Organization, DATABASE) engine = create_engine(URL(**DATABASE)) Base.metadata.bind = engine DBSession = sessionmaker(bind=engine) session = DBSession() # Create a user User1 = User( name="Robot Machine", email="*****@*****.**", picture= 'https://pbs.twimg.com/profile_images/2671170543/18debd694829ed78203a5a36dd364160_400x400.png' ) session.add(User1) session.commit() # Create some test Category objects
def connect_database(): return create_engine(URL(**settings.DATABASE))
def adjust_database_uri(cls, uri: URL, selected_schema: Optional[str]) -> None: if selected_schema: uri.database = parse.quote(selected_schema, safe="")
def main(argv): # parse command line options opts, args = parse_options(argv) if not (opts or args): return 1 urlfile = opts.urlfile csvfile = opts.csvfile dbuser = opts.dbuser dbpass = opts.dbpass dbhost = opts.dbhost dbport = opts.dbport dbschema = opts.dbschema depth = opts.depth resume = opts.resume cluster_jobs = opts.cluster_jobs # connect to database try: engine = create_engine(URL("mysql+mysqlconnector", username=dbuser, password=dbpass, host=dbhost, port=dbport)) # create database schema engine.execute("CREATE DATABASE IF NOT EXISTS " + dbschema) engine.execute("USE " + dbschema) except Exception as e: print("ERROR: Can't connect to database (%s)" % e) return 1 # if resume previous crawl, do not clean database if not resume: # read file with URL list if urlfile: try: with open(urlfile, 'r') as file: url_list = [line.strip() for line in file.readlines()] if not url_list: print("No data in file %s" % urlfile) return 1 print("Read file %s" % urlfile) except IOError as error: print("I/O error({0}): {1}".format(error.errno, error.strerror)) return 1 except: # handle other exceptions such as attribute errors print("Unexpected error:", sys.exc_info()[0]) return 1 else: # URL in command line argument url_list = [args[0]] # clean database Base.metadata.drop_all(engine) Base.metadata.create_all(engine) session = Session(engine) # insert URL list into database links = [] for url in url_list: links.append(Link(url)) session.add_all(links) session.commit() session.close() # run in cluster mode if cluster_jobs > 0: # 'compute' needs definition of class Crawler cluster = dispy.JobCluster(compute, depends=[Crawler]) jobs = [] for i in range(1, cluster_jobs + 1): crawler = Crawler(i, dbuser, dbpass, dbhost, dbport, dbschema, depth) # create object of Crawler job = cluster.submit(crawler) # it is sent to a node for executing 'compute' job.id = crawler # store this object for later use jobs.append(job) # waits until all jobs finish for job in jobs: job() # wait for job to finish print('Job %s:\n%s\n%s\n%s' % (job.id.job_num, job.stdout, job.stderr, job.exception)) else: # run in standalone mode crawler = Crawler(0, dbuser, dbpass, dbhost, dbport, dbschema, depth) crawler.crawl() # write product list to .csv file if csvfile: if not csvfile.lower().endswith('.csv'): csvfile += '.csv' try: with open(csvfile, 'w', newline='') as outfile: writer = csv.writer(outfile, quoting=csv.QUOTE_ALL) session = Session(engine) # paginate results rows_per_page = 50 page_number = 0 dbquery = session.query(Product) products = dbquery.limit(rows_per_page).offset(page_number * rows_per_page).all() while products: for product in products: writer.writerow([product.url, product.title, product.name]) page_number += 1 products = dbquery.limit(rows_per_page).offset(page_number * rows_per_page).all() print("Write file %s" % csvfile) except IOError as error: print("I/O error({0}): {1}".format(error.errno, error.strerror)) return 1 except: # handle other exceptions such as attribute errors print("Unexpected error:", sys.exc_info()[0]) return 1 session.close() engine.dispose() return 0
def db_connect(): """ Performs database connection using database settings from settings.py. Returns sqlalchemy engine instance """ return create_engine(URL(**settings.DATABASE))
import os from sqlalchemy import create_engine from sqlalchemy.engine.url import URL from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker, scoped_session # DB 연결 정보 DB = { 'drivername': 'mysql', 'host': '', 'port': '', 'username': "", 'password': "", 'database': "", 'query': { 'charset': 'utf8' } } ENGINE = create_engine(URL(**DB)) session = scoped_session( sessionmaker(autocommit=False, autoflush=False, bind=ENGINE)) Base = declarative_base() Base.query = session.query_property()
import sandman2 from sqlalchemy.engine.url import URL from utvsapi import models url = URL('mysql', query={'read_default_file': './mysql.cnf'}) app = sandman2.get_app(url, user_models=models.all(), read_only=True) if __name__ == '__main__': app.run(debug=True)
import os from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker, scoped_session from sqlalchemy.engine.url import URL from sqlalchemy.ext.automap import automap_base # TODO: Config-ify. url = URL(drivername='postgresql', database='osp_graphs') engine = create_engine(url) factory = sessionmaker(bind=engine) session = scoped_session(factory) Base = automap_base() Base.prepare(engine, reflect=True) Text = Base.classes.text Citation = Base.classes.citation Field = Base.classes.field Subfield = Base.classes.subfield SubfieldDocument = Base.classes.subfield_document Document = Base.classes.document # added after original code:
import sqlalchemy as sa from sqlalchemy.orm import sessionmaker from sqlalchemy.engine.url import URL # 初始化数据库连接: url = URL(drivername='postgresql', username='******', password='******', host='localhost', database='employeeinfodb') engine = sa.create_engine(url) # 创建DBSession类型: DBSession = sessionmaker(engine) dbSession = DBSession()
from random import randint import datetime from sqlalchemy import create_engine, select, func from sqlalchemy.engine.url import URL import settings import utils import tables engine = create_engine(URL(**settings.DATABASE)) tables.create_all(engine) data = utils.get_static_data() with engine.begin() as conn: for book in data['books']: ins = tables.Book.insert().values(title=book) conn.execute(ins) ins = tables.Person.insert() values = [] for person in data['persons']: dob = utils.get_dob(1980, 2010) values.append({'name': person, 'date_of_birth': dob}) with engine.begin() as conn: conn.execute(ins, values) ins = tables.Write.insert() with engine.begin() as conn:
def _get_test_engine(): return create_engine(URL(**connection_params, host=docker_ip))
def get_connection_string(self): return str(URL('sqlite+pysqlite', None, None, None, None, self.dbpath))
from sqlalchemy import create_engine from sqlalchemy.engine.url import URL from sqlalchemy import event, DDL from alembic.config import Config from alembic import command from pipeline.results import Base from pipeline.luigi_ext import DatabaseConfig event.listen(Base.metadata, 'before_create', DDL("CREATE SCHEMA IF NOT EXISTS results")) url = None if not url: url = URL("postgres", host=DatabaseConfig().host, username=DatabaseConfig().user, database=DatabaseConfig().database, password=DatabaseConfig().password, port=DatabaseConfig().port) engine = create_engine(url, echo=True) Base.metadata.create_all(engine) alembic_cfg = Config("alembic.ini") command.stamp(alembic_cfg, "head")
def make_url(url_params: dict) -> URL: return URL(**url_params)
def _initialize(self): models.Store_menu.objects.all().delete() query_store = "SELECT * FROM django_test.api_store where id=" # query_menu="SELECT * FROM django_test.api_menu where id between 1 and 50000" # query_menu="SELECT * FROM django_test.api_menu where id between 50001 and 100000" # query_menu="SELECT * FROM django_test.api_menu where id between 100001 and 150000" # query_menu="SELECT * FROM django_test.api_menu where id between 150001 and 200000" # query_menu="SELECT * FROM django_test.api_menu where id between 200001 and 250000" # query_menu="SELECT * FROM django_test.api_menu where id between 250001 and 300000" # query_menu="SELECT * FROM django_test.api_menu where id between 300001 and 400000;" # query_menu="SELECT * FROM django_test.api_menu where id between 400001 and 550000" # query_menu="SELECT * FROM django_test.api_menu where id between 550001 and 700000" # query_menu="SELECT * FROM django_test.api_menu where id between 700001 and 1000000" # query_menu="SELECT * FROM django_test.api_menu where id between 1000001 and 1200000" # query_menu="SELECT * FROM django_test.api_menu where id between 1200001 and 1500000" query_menu = "SELECT * FROM django_test.api_menu where id between 1500001 and 1651857" query_menu = "SELECT * FROM django_test.api_menu" engine = create_engine( URL(drivername="mysql", username="******", password="******", host="52.78.173.64", port="3306", database="django_test", query={'charset': 'utf8mb4'})) # engine = create_engine("mysql+mysqldb://root:[email protected]/django_test", # encodeing='utf-8') conn = engine.connect() print("[*] loading menus...") menus = pd.read_sql( sql=query_menu, # mysql query con=conn) print(menus) # menus= menus.head(10) print("[*] Initializing store_menus...") Store_menus_bulk = [] tmp_store_id = menus.iloc[0].store_id print("temp store id: " + str(tmp_store_id)) store = pd.read_sql( sql=query_store + str(tmp_store_id), # mysql query con=conn) print(store.iloc[0].id) #일단 1번째 인덱스의 store 정보 부터 가져온다. temp = "" for menu in menus.itertuples(): if (menu.id % 100000) == 0: print("10만개 완료") now_store_id = menu.store_id if tmp_store_id != now_store_id: # 저장시작 Store_menus_bulk.append( models.Store_menu(id=store.iloc[0].id, store_name=store.iloc[0].store_name, branch=store.iloc[0].branch, area=store.iloc[0].area, tel=store.iloc[0].tel, address=store.iloc[0].address, latitude=store.iloc[0].latitude, longitude=store.iloc[0].longitude, category=store.iloc[0].category, menu=temp + "|" + store.iloc[0].category)) tmp_store_id = now_store_id temp = "" temp += menu.menu_name store = pd.read_sql( sql=query_store + str(tmp_store_id), # mysql query con=conn) else: #상점번호가 같다면 temp란 str에 메뉴이름을 계속 추가한다. temp += "|" + menu.menu_name models.Store_menu.objects.bulk_create(Store_menus_bulk) print("[*] query [" + query_menu + "] is completed")
def open_spider(self, spider): self.engine = create_engine(URL.create(**self.database_settings))
"users.starlette_password.hashers.PBKDF2PasswordHasher", "users.starlette_password.hashers.PBKDF2SHA1PasswordHasher", "users.starlette_password.hashers.BCryptSHA256PasswordHasher", ] # Services URLs ASSOCIATION_FRONTEND_URL = config( "ASSOCIATION_FRONTEND_URL", ) # Unit test configuration RUNNING_TESTS = config("RUNNING_TESTS", cast=bool, default=False) if RUNNING_TESTS: original_url = make_url(DATABASE_URL) test_db_url = URL.create( drivername=original_url.drivername, username=original_url.username, password=original_url.password, host=original_url.host, port=original_url.port, database=f"TEST_{original_url.database}", query=original_url.query, ) DATABASE_URL = str(test_db_url) PASSWORD_HASHERS = ["users.starlette_password.plain_hasher.PlainPasswordHasher"] SERVICE_TO_SERVICE_SECRET = "test-service-to-service"
def get_connection_url(): arguments = {k[3:].lower(): os.environ[k] for k in db_env_vars} return URL(**arguments)
from h3_helper import * from s2_generate_cell_geometry import * from EAGGR_generate_cell_geometry import * from shapely.geometry import box HOST = 'localhost' DB = 'dggs' USER = '******' PORT = 5432 PWD = 'postgres' workDataDir = r"D:\UTCloud\DGGS\working\Python\workingdata" # Database info db_url = URL(drivername='postgresql+psycopg2', host=HOST, database=DB, username=USER, port=PORT, password=PWD) # Create engine engine = create_engine(db_url) # read extent file gdf = gpd.read_file(workDataDir + "working.gpkg", layer='dev_extent2') # convert extent to geoJSON extentJSON = json.loads((gdf.to_json())) # create geojson extent extentJSON = gpd.GeoSeries(box(12.2, 47.3, 13.7, 48.2)).__geo_interface__
def get_url(): """ Return the URL to be used with engine creation based on configuration """ used = Profile.get_used() return URL(**dict(used))
PG_DB_DATABASE = config("PG_DB_DATABASE", default=None) # TESTING = config("TESTING", cast=bool, default=False) # if TESTING: # if PG_DB_DATABASE: # PG_DB_DATABASE += "_test" # else: # PG_DB_DATABASE = "market_test" PG_DB_DSN = config( "PG_DB_DSN", cast=make_url, default=URL( drivername=PG_DB_DRIVER, username=PG_DB_USER, password=PG_DB_PASSWORD, host=PG_DB_HOST, port=PG_DB_PORT, database=PG_DB_DATABASE, ), ) PG_DB_POOL_MIN_SIZE = config("PG_DB_POOL_MIN_SIZE", cast=int, default=1) PG_DB_POOL_MAX_SIZE = config("PG_DB_POOL_MAX_SIZE", cast=int, default=16) PG_DB_ECHO = config("PG_DB_ECHO", cast=bool, default=False) PG_DB_SSL = config("PG_DB_SSL", default=None) PG_DB_USE_CONNECTION_FOR_REQUEST = config("PG_DB_USE_CONNECTION_FOR_REQUEST", cast=bool, default=True) PG_DB_RETRY_LIMIT = config("PG_DB_RETRY_LIMIT", cast=int, default=1) PG_DB_RETRY_INTERVAL = config("PG_DB_RETRY_INTERVAL", cast=int, default=1) # first user infos
from sqlalchemy import create_engine from sqlalchemy import MetaData from sqlalchemy import inspect from sqlalchemy import Table from sqlalchemy import Column, Integer, String from sqlalchemy.engine.url import URL db_url = { 'drivername': 'postgres', 'username': '******', 'password': '******', 'host': '192.168.99.100', 'port': 5432 } engine = create_engine(URL(**db_url)) m = MetaData() table = Table('Test', m, Column('id', Integer, primary_key=True), Column('key', String, nullable=True), Column('val', String)) table.create(engine) inspector = inspect(engine) print('Test' in inspector.get_table_names()) table.drop(engine) inspector = inspect(engine) print('Test' in inspector.get_table_names())
def test_main(): while not (pg_ready(PG_HOST) and pg_ready(PG_HOST_EMPTY)): print("Waiting for Postgres containers...") time.sleep(1) s3 = boto3.resource('s3', endpoint_url=S3_URL) while not (s3_ready(s3)): print("Waiting for S3 container...") time.sleep(1) ssm = boto3.client('ssm', endpoint_url=SSM_URL) while not (ssm_ready(ssm)): print("Waiting for SSM container...") time.sleep(1) bucket = s3.create_bucket(Bucket=S3_BUCKET) ssm.put_parameter(Name=POSTGRES_PASSWORD_SSM_KEY, Value='password', Type='SecureString') os.environ.update({ 'POSTGRES_HOST': PG_HOST, 'POSTGRES_PORT': PG_PORT, 'POSTGRES_USER': PG_USER, 'POSTGRES_DATABASE': PG_DATABASE, 'S3_BUCKET': S3_BUCKET, }) lambda_handler( { 'organization_schema': ORGANIZATION_SCHEMA, 's3_key': S3_KEY, }, {}) bucket.download_file(S3_KEY, DUMP) # Import the dump file into a clean database # There is not an easy way to import a whole file using SqlAlchemy, so use psql subprocess.check_output([ PSQL, '--host', PG_HOST_EMPTY, '--port', PG_PORT, '--user', PG_USER, '--dbname', PG_DATABASE, '--file', DUMP, '-v', 'ON_ERROR_STOP=1' ], env={ 'PGPASSWORD': PG_PASSWORD, 'LD_LIBRARY_PATH': PG_PATH }) # Connect to the new database engine = create_engine( URL(drivername='postgresql', database=PG_DATABASE, username=PG_USER, password=PG_PASSWORD, host=PG_HOST_EMPTY, port=PG_PORT)) # Does the pennsievedb seed data from pennsieve-api/local-seed.sql look good? with engine.connect() as conn: rows = conn.execute( f'SELECT * FROM "{ORGANIZATION_SCHEMA}".datasets;').fetchall() assert len(rows) == 1 assert rows[0]['name'] == 'Pennsieve Dataset' assert rows[0][ 'node_id'] == 'N:dataset:c0f0db41-c7cb-4fb5-98b4-e90791f8a975' with pytest.raises(ProgrammingError): conn.execute('SELECT * FROM "2".datasets;').fetchall() rows = conn.execute( f'SELECT * FROM "{ORGANIZATION_SCHEMA}".files;').fetchall() assert len(rows) == 0
def crawl(self): import math import time import urllib.parse import urllib.request from crawler_db import (Status, Link, Product) from crawler_web import Webpage import sqlalchemy from sqlalchemy import (create_engine, exc) from sqlalchemy.engine.url import URL from sqlalchemy.orm import (Session, exc) # connect to database try: engine = create_engine(URL("mysql+mysqlconnector", username=self.dbuser, password=self.dbpass, host=self.dbhost, port=self.dbport)) engine.execute("USE " + self.dbschema) # select database except Exception as e: print("ERROR: Can't connect to database (%s)" % e) return 1 session = Session(engine) start_time = time.time() num_retries = 3 if self.job_num: attempts = num_retries # running in cluster mode # retry if new links are not available momentarily, # otherwise process has finished else: attempts = 1 # running in standalone mode # no retry while attempts: # process same site first link = session.query(Link).filter(Link.depth > 1, Link.status == Status.new).with_for_update().first() if not link: link = session.query(Link).filter_by(status=Status.new).with_for_update().first() while link: attempts = num_retries # restart attempts this_url = link.url link_depth = link.depth + 1 self.num_processed += 1 self.host = urllib.parse.urlparse(this_url)[1] status = Status.visited try: page = Webpage(this_url) if not page.fetch(): status = Status.error else: if not self.depth or (link_depth <= self.depth): for link_url in [self._pre_visit_url_condense(l) for l in page.out_urls]: # apply pre-visit filters. do_not_follow = [f for f in self.pre_visit_filters if not f(link_url)] # if no filters failed, process URL if [] == do_not_follow: new_link = Link(link_url, depth=link_depth) session.begin_nested() # establish a savepoint session.add(new_link) try: session.flush() except sqlalchemy.exc.IntegrityError: # rollback duplicated entry session.rollback() continue except exc.FlushError: # rollback duplicated entry session.rollback() continue except: session.rollback() raise session.commit() # apply product filters. is_product = [f for f in self.product_filters if not f(page)] # if no filters failed, process product if [] == is_product: product = Product(this_url, title=page.title, name=page.product_name) session.begin_nested() # establish a savepoint session.add(product) try: session.flush() except: session.rollback() raise session.commit() except Exception as e: print("ERROR: Can't process url '%s' (%s)" % (this_url, e)) status = Status.error link.status = status session.commit() # process same site first link = session.query(Link).filter(Link.depth > 1, Link.status == Status.new).with_for_update().first() if not link: link = session.query(Link).filter_by(status=Status.new).with_for_update().first() # sleep if running in cluster mode if self.job_num: time.sleep(5) attempts -= 1 end_time = time.time() time_diff = end_time - start_time rate = 0 if time_diff: rate = int(math.ceil(float(self.num_processed) / time_diff)) print("\tProcessed: %d" % self.num_processed) print("\tStats: %d/s after %0.2fs" % (rate, time_diff)) session.close() engine.dispose() return 0
def workers_queue(pid, q1, q2): """Receiving parameters from q1, then computing and finally put results into q2 """ engine = create_engine( URL(**CONF['database']['connect_args']), pool_size=1, pool_recycle=CONF['database']['pool_recycle'], client_encoding='utf8') Session = scoped_session(sessionmaker(bind=engine)) session = Session() parser = BulkParser(platform_id=1, save_none_url_tweet=True) while True: try: data = q1.get(timeout=1) except Empty: logger.info('Worker process %s: queue is empty for 1 seconds', pid) q2.put((pid, 'STOP', None)) break if data == 'STOP': logger.info('Worker process %s: STOP sign received from q1!', pid) q1.put('STOP') q2.put((pid, 'STOP', None)) break else: logger.info('Worker process %s: data=%s received', pid, data) w_open_left, w_close_right = data jds = dict() g_urls_map = dict() query = """ SELECT tw.id, tw.json_data, u.id, u.raw FROM tweet AS tw LEFT JOIN ass_tweet_url AS atu ON atu.tweet_id=tw.id LEFT JOIN url AS u ON u.id=atu.url_id WHERE tw.id>:l AND tw.id<=:r """ for tw_id, jd, url_id, url in engine.execute( text(query).bindparams(l=w_open_left, r=w_close_right)): jds[tw_id] = jd if url_id is not None: g_urls_map[url] = url_id g_uusers_set = set() g_edges_set = set() for tw_id, jd in jds.iteritems(): parser.parse_existed_one( tw_id, jd, session, g_urls_map=g_urls_map, g_uusers_set=g_uusers_set, g_edges_set=g_edges_set) edges = [ dict( tweet_raw_id=t0, from_raw_id=t1, to_raw_id=t2, url_id=t3, is_quoted_url=t4, is_mention=t5, tweet_type=t6) for t0, t1, t2, t3, t4, t5, t6 in g_edges_set if t3 != -1 ] uusers = [dict(raw_id=t1, screen_name=t2) for t1, t2 in g_uusers_set] # session.bulk_insert_mappings(TwitterNetworkEdge, edges) stmt_do_nothing = insert(TwitterNetworkEdge).values( edges).on_conflict_do_nothing(index_elements=[ 'tweet_raw_id', 'from_raw_id', 'to_raw_id', 'url_id', 'is_quoted_url', 'is_mention', 'tweet_type' ]) session.execute(stmt_do_nothing) session.commit() q2.put((pid, 'RUN', uusers)) logger.info('Worker process %s: tweets from %s to %s done', pid, w_open_left + 1, w_close_right)
def db_connect(): return create_engine(URL(**settings.DATABASE))
def __init__( self, domain: Optional[Domain] = None, dialect: Text = "sqlite", host: Optional[Text] = None, port: Optional[int] = None, db: Text = "rasa.db", username: Text = None, password: Text = None, event_broker: Optional[EventChannel] = None, login_db: Optional[Text] = None, ) -> None: import sqlalchemy from sqlalchemy.orm import sessionmaker from sqlalchemy.engine.url import URL from sqlalchemy import create_engine engine_url = URL( dialect, username, password, host, port, database=login_db if login_db else db, ) logger.debug( "Attempting to connect to database " 'via "{}"'.format(engine_url.__to_string__()) ) # Database might take a while to come up while True: try: self.engine = create_engine(engine_url) # if `login_db` has been provided, use current connection with # that database to create working database `db` if login_db: self._create_database_and_update_engine(db, engine_url) try: self.Base.metadata.create_all(self.engine) except ( sqlalchemy.exc.OperationalError, sqlalchemy.exc.ProgrammingError, ) as e: # Several Rasa services started in parallel may attempt to # create tables at the same time. That is okay so long as # the first services finishes the table creation. logger.error("Could not create tables: {}".format(e)) self.session = sessionmaker(bind=self.engine)() break except ( sqlalchemy.exc.OperationalError, sqlalchemy.exc.IntegrityError, ) as e: logger.warning(e) sleep(5) logger.debug("Connection to SQL database '{}' successful".format(db)) super(SQLTrackerStore, self).__init__(domain, event_broker)
# Third party lib imports from flask import Flask from sqlalchemy.engine.url import URL from flask.ext.sqlalchemy import SQLAlchemy # Local imports from naas import settings app = Flask(__name__) app.config['SQLALCHEMY_DATABASE_URI'] = URL(**settings.DATABASE) db = SQLAlchemy(app) import naas.views
def test_create_connect_args(): sfdialect = base.SnowflakeDialect() test_data = [ ( # 0: full host name and no account URL("snowflake", username="******", password="******", host='testaccount.snowflakecomputing.com', query={}), { 'autocommit': False, 'host': 'testaccount.snowflakecomputing.com', 'password': '******', 'user': '******' }), ( # 1: account name only URL("snowflake", username="******", password="******", host='testaccount', query={}), { 'autocommit': False, 'host': 'testaccount.snowflakecomputing.com', 'password': '******', 'user': '******', 'port': '443', 'account': 'testaccount' }), ( # 2: account name including region URL("snowflake", username="******", password="******", host='testaccount.eu-central-1', query={}), { 'autocommit': False, 'host': 'testaccount.eu-central-1.snowflakecomputing.com', 'password': '******', 'user': '******', 'port': '443', 'account': 'testaccount' }), ( # 3: full host including region URL("snowflake", username="******", password="******", host='testaccount.eu-central-1.snowflakecomputing.com', query={}), { 'autocommit': False, 'host': 'testaccount.eu-central-1.snowflakecomputing.com', 'password': '******', 'user': '******' }), ( # 4: full host including region and account URL("snowflake", username="******", password="******", host='testaccount.eu-central-1.snowflakecomputing.com', query={'account': 'testaccount'}), { 'autocommit': False, 'host': 'testaccount.eu-central-1.snowflakecomputing.com', 'password': '******', 'user': '******', 'account': 'testaccount' }), ( # 5: full host including region and account including region URL("snowflake", username="******", password="******", host='testaccount.eu-central-1.snowflakecomputing.com', query={'account': 'testaccount.eu-central-1'}), { 'autocommit': False, 'host': 'testaccount.eu-central-1.snowflakecomputing.com', 'password': '******', 'user': '******', 'account': 'testaccount.eu-central-1' }), ( # 6: full host including region and account including region URL("snowflake", username="******", password="******", host='snowflake.reg.local', port='8082', query={'account': 'testaccount'}), { 'autocommit': False, 'host': 'snowflake.reg.local', 'password': '******', 'user': '******', 'port': 8082, 'account': 'testaccount' }), ] for idx, ts in enumerate(test_data): _, opts = sfdialect.create_connect_args(ts[0]) assert opts == ts[1], "Failed: {0}: {1}".format(idx, ts[0])
def create_engine(self): """Create the database engine""" log.debug("Creating ENGINE") options = { 'convert_unicode': self.native_unicode, 'poolclass': GreenQueuePool, 'pool_size': self.pool_size, 'pool_recycle': self.pool_recycle, 'pool_timeout': self.pool_timeout } if self.database_uri.startswith('sqlite:'): options.pop('pool_timeout') match = _sqlite_re.match(self.database_uri) if match is None: raise ArgumentError('Could not parse rfc1738 URL') database, query = match.groups() if database is None: database = ':memory:' if query: query = url_decode(query).to_dict() else: query = {} info = URL('sqlite', database=database, query=query) pool_size = options.get('pool_size', 0) # we go to memory and the pool size was explicitly set to 0 # which is fail. Let the user know that if info.database in (None, '', ':memory:'): if pool_size == 0: raise RuntimeError('SQLite in memory database with an ' 'empty queue not possible due to data ' 'loss.') # if pool size is None or explicitly set to 0 we assume the # user did not want a queue for this sqlite connection and # hook in the null pool. elif not pool_size: log.warn("SQLite database is using the NullPool") from sqlalchemy.pool import NullPool options['poolclass'] = NullPool else: info = make_url(self.database_uri) # if mysql is the database engine, god forbid, and no connection # encoding is provided we set it to utf-8 if info.drivername == 'mysql': info.query.setdefault('charset', 'utf8') options.setdefault('pool_size', 10) options.setdefault('pool_recycle', 7200) elif info.drivername.startswith('postgresql+psycopg2'): from psycopg2 import extensions, OperationalError from gevent.socket import wait_read, wait_write options['use_native_unicode'] = self.native_unicode def wait_callback(conn, timeout=None): """ A wait callback useful to allow gevent to work with Psycopg. https://bitbucket.org/dvarrazzo/psycogreen/src/tip/gevent/ """ while True: state = conn.poll() if state == extensions.POLL_OK: break elif state == extensions.POLL_READ: wait_read(conn.fileno(), timeout=timeout) elif state == extensions.POLL_WRITE: wait_write(conn.fileno(), timeout=timeout) else: raise OperationalError( "Bad result from poll: %r" % state ) if hasattr(extensions, 'set_wait_callback'): extensions.set_wait_callback(wait_callback) dialect_cls = info.get_dialect() # get the correct DBAPI base on connection url dbapi_args = {} dbapi = dialect_cls.dbapi(**dbapi_args) # create the dialect dialect_args = {'dbapi':dbapi} dialect = dialect_cls(**dialect_args) # assemble connection arguments for this dialect (cargs, connection_params) = dialect.create_connect_args(info) log.debug("CARGS: %s; CONNECTION_PARAMS: %s;", cargs, connection_params) log.debug("Creating db engine. info: %s; options: %s;", info, options) engine = sqlalchemy.create_engine(info, **options) database_engine_created.send(self, engine=engine)
else: value = default if str(value).upper() == "TRUE": value = True elif str(value).upper() == "FALSE": value = False return value postgres_url = str( URL( drivername="postgresql+psycopg2", host=env_var("POSTGRES_HOST", "localhost"), port=env_var("POSTGRES_PORT", "5432"), username=env_var("POSTGRES_USERNAME"), password=env_var("POSTGRES_PASSWORD"), database=env_var("POSTGRES_DATABASE", "matcher"), )) postgres_test_url = str( URL( drivername="postgresql+psycopg2", host=env_var("POSTGRES_TEST_HOST", env_var("POSTGRES_HOST", "localhost")), port=env_var("POSTGRES_TEST_PORT", env_var("POSTGRES_PORT", "5432")), username=env_var("POSTGRES_TEST_USERNAME", env_var("POSTGRES_USERNAME")), password=env_var("POSTGRES_TEST_PASSWORD", env_var("POSTGRES_PASSWORD")), database=env_var("POSTGRES_TEST_DATABASE",
def do_run(self): # R0914: Too many local variables. # pylint: disable=R0914 import sqlalchemy from sqlalchemy import exc from sqlalchemy.event import listen from sqlalchemy.engine.url import URL def start_query(conn, *dummy): ''' Save time the query starts. ''' conn.info['wh_time'] = timeit.default_timer() def end_query(conn, *dummy): ''' Save time the query's finished. ''' conn.info['wh_time'] = timeit.default_timer() - conn.info['wh_time'] config = { k: v for k, v in self.config.iteritems() if k in self.config_raw['properties'].keys() } config['drivername'] = config.pop('dbtype') if config['drivername'] == 'sqlite': config['database'] = config.pop('host') query = config.pop('query') url = URL(**config) try: engine = sqlalchemy.create_engine(url) except ImportError as err: return (( 'error', '{}. Sensor checking sql requires it. Please install it.' .format(err) ),) error_msg = ( '(database {})\nError: {{}}\n Message from database: "{{}}"' .format(url.__to_string__()) ) try: connection = engine.connect() except exc.TimeoutError: return ( ('error', error_msg.format('Timeout getting connection', None)), ) except exc.SQLAlchemyError as err: return ( ('error', error_msg.format('Could not connect to database', err)), ) listen(connection, 'before_cursor_execute', start_query) listen(connection, 'after_cursor_execute', end_query) try: result = connection.execute(query).fetchall() time = connection.info['wh_time'] except exc.StatementError as err: return (( 'error', error_msg.format( 'Error executing statement {}'.format(err.statement), err) ),) except exc.SQLAlchemyError as err: return (( 'error', error_msg.format( 'Error executing statement, your query: {}'.format( self.config['query']), err.message) ),) finally: connection.close() try: if len(result) == 1 and len(result[0]) == 1: result = ('result_num', float(result[0][0])) else: raise ValueError except ValueError: result = ('result', str(result)[1:-1]) return (result, ('query_time', time))
def __getitem__(self, name): if 'DATABASES' not in settings: raise exceptions.ImproperlyConfigured( 'DATABASES not configured in project settings.') if name not in settings['DATABASES']: raise exceptions.ImproperlyConfigured( '%r not present in DATABASES configuration.' % name) config = settings['DATABASES'][name] if isinstance(config, six.string_types): url = make_url(config) options = {} else: config = dict(map(lambda i: (i[0].lower(), i[1]), config.items())) options = config.get('options', {}) url = URL( config['engine'], username=config.get('username', config.get('user')), password=config.get('password', config.get('pass')), host=config.get('hostname', config.get('host')), port=config.get('port'), database=config.get('name', config.get('database'))) # If alchemist is invoked by a test runner we should switch to using # testing databases. if settings['TESTING']: if url.drivername.startswith('sqlite'): # Switch to using an in-memory database for sqlite. url.database = ':memory:' else: # Switch to using a named testing database for other dialects. ident = threading.current_thread().ident url.database = 'test_%s_%s' % (url.database, ident) # Apply MySQL hacks to make MySQL play nice. pool_size = None pool_recycle = None if url.drivername.startswith('mysql'): pool_size = 10 pool_recycle = 7200 # Get "global" options for the database engine. pool_size = settings.get('DATABASE_POOL_SIZE', pool_size) if pool_size: options.setdefault('pool_size', pool_size) pool_recycle = settings.get('DATABASE_POOL_RECYCLE', pool_recycle) if pool_recycle: options.setdefault('pool_recycle', pool_recycle) pool_timeout = settings.get('DATABASE_POOL_TIMEOUT') if pool_timeout: options.setdefault('pool_timeout', pool_timeout) # Forward configuration to sqlalchemy and create the engine. engine = sa.create_engine(url, **options) if settings["DEBUG"]: # Create a no-op listener if we're in debug mode. from sqlalchemy.event import listen listen(engine, "after_cursor_execute", lambda *a, **kw: None) # Return the created engine. return engine