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())
import os from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.engine.url import URL from sqlalchemy.orm import sessionmaker from config import DATABASE, GMAP_API_KEY OD_BASE_URL = 'https://data.cityofnewyork.us/resource/9w7m-hzhe.json?' OD_URL_PARAM_LIMIT = '$limit=' OD_URL_PARAM_OFFSET = '$offset=' GMAP_GC_BASE_URL = 'https://maps.googleapis.com/maps/api/geocode/json?address=' GMAP_API_KEY_PARAM = '&key=' + GMAP_API_KEY engine = create_engine(URL(**DATABASE)) Base = declarative_base() Base.metadata.create_all(engine) Session = sessionmaker(bind=engine) session = Session()
from config import DBCONFIG
from service.models import metadata
from sqlalchemy.engine.url import URL
from sqlalchemy import create_engine
dbengine = create_engine(URL(DBCONFIG['DRIVER'], DBCONFIG['USER'],
DBCONFIG['PASSWORD'], DBCONFIG['HOST'],
DBCONFIG.get('PORT'), DBCONFIG['DBNAME']),
echo=True)
metadata.create_all(dbengine)
with pytest.raises(SystemExit):
with tracker_store.session_scope() as _:
pass
# error message is printed
assert (
f"Requested PostgreSQL schema '{requested_schema}' was not found in the "
f"database." in capsys.readouterr()[0])
@pytest.mark.parametrize(
"url,is_postgres_url",
[
(f"{PGDialect.name}://admin:pw@localhost:5432/rasa", True),
(f"{SQLiteDialect.name}:///", False),
(URL(PGDialect.name), True),
(URL(SQLiteDialect.name), False),
],
)
def test_is_postgres_url(url: Union[Text, URL], is_postgres_url: bool):
assert rasa.core.tracker_store.is_postgresql_url(url) == is_postgres_url
def set_or_delete_postgresql_schema_env_var(monkeypatch: MonkeyPatch,
value: Optional[Text]) -> None:
"""Set `POSTGRESQL_SCHEMA` environment variable using `MonkeyPatch`.
Args:
monkeypatch: Instance of `MonkeyPatch` to use for patching.
value: Value of the `POSTGRESQL_SCHEMA` environment variable to set.
"""
from .utils import log, logerr
pg_vars = {
'drivername': os.getenv('PG_DRIVER'),
'username': os.getenv('PG_USER'),
'password': os.getenv('PG_PASSWORD'),
'host': os.getenv('PG_HOST'),
'port': os.getenv('PG_PORT'),
'database': os.getenv('PG_DATABASE')
}
pg_ssl = os.getenv('PG_SSL')
# Setup SQL Alchemy postgres connection.
engine = create_engine(URL(**pg_vars), connect_args={'sslmode': pg_ssl})
conn = engine.connect()
# Grab a page from C.io messages API with optional next param for pagination.
def get_page(next_page=None):
params = {
'metric': os.getenv('CIO_API_METRIC'),
'type': os.getenv('CIO_API_TYPE'),
'limit': os.getenv('CIO_API_LIMIT'),
'start': next_page
}
user = os.getenv('CIO_API_USER')
password = os.getenv('CIO_API_PASSWORD')
uri = os.getenv('CIO_API_URI')
r = requests.get(uri, params=params, auth=(user, password))
import json
from sqlalchemy import create_engine
from sqlalchemy.engine.url import URL
prodb = {
'database': 'prod',
'username': '******',
'password': '******',
'host': '1.1.1.1',
'port': '3306',
'drivername': 'mysql+pymysql',
'query': {'charset': 'utf8'}
}
engine_prod = create_engine(URL(**prodb))
engine_local = create_engine('mysql+pymysql://user:pass@localhost:3306/test?charset=utf8')
def local():
excel = pd.ExcelFile("生产集群节点.xlsx")
sheet_names = excel.sheet_names
for sheet_name in sheet_names:
if 'df' not in locals():
df = pd.read_excel("生产集群节点.xlsx", sheet_name)
df['vc_cluster'] = sheet_name
else:
mapper: Map Table to class
from sqlalchemy import (
create_engine,
Table,
MetaData,
Column,
Integer,
String,
ForeignKey)
from sqlalchemy.orm import (
mapper,
relationship,
sessionmaker)
# classical mapping: map "table" to "class"
db_url = 'sqlite://'
engine = create_engine(db_url)
meta = MetaData(bind=engine)
user = Table('User', meta,
Column('id', Integer, primary_key=True),
Column('name', String),
Column('fullname', String),
Column('password', String))
addr = Table('Address', meta,
Column('id', Integer, primary_key=True),
Column('email', String),
Column('user_id', Integer, ForeignKey('User.id')))
# map table to class
class User(object):
def __init__(self, name, fullname, password):
self.name = name
self.fullname = fullname
self.password = password
class Address(object):
def __init__(self, email):
self.email = email
mapper(User, user, properties={
'addresses': relationship(Address, backref='user')})
mapper(Address, addr)
# create table
meta.create_all()
# create session
Session = sessionmaker()
Session.configure(bind=engine)
session = Session()
u = User(name='Hello', fullname='HelloWorld', password='******')
a = Address(email='*****@*****.**')
u.addresses.append(a)
try:
session.add(u)
session.commit()
# query result
u = session.query(User).filter(User.name == 'Hello').first()
print(u.name, u.fullname, u.password)
finally:
session.close()
output:
$ python map_table_class.py
Hello HelloWorld ker
Get table dynamically
from sqlalchemy import (
create_engine,
MetaData,
Table,
inspect,
Column,
String,
Integer)
from sqlalchemy.orm import (
mapper,
scoped_session,
sessionmaker)
db_url = "sqlite://"
engine = create_engine(db_url)
metadata = MetaData(engine)
class TableTemp(object):
def __init__(self, name):
self.name = name
def get_table(name):
if name in metadata.tables:
table = metadata.tables[name]
else:
table = Table(name, metadata,
Column('id', Integer, primary_key=True),
Column('name', String))
table.create(engine)
cls = type(name.title(), (TableTemp,), {})
mapper(cls, table)
return cls
# get table first times
t = get_table('Hello')
# get table secone times
t = get_table('Hello')
Session = scoped_session(sessionmaker(bind=engine))
try:
Session.add(t(name='foo'))
Session.add(t(name='bar'))
for _ in Session.query(t).all():
print(_.name)
except Exception as e:
Session.rollback()
finally:
Session.close()
output:
$ python get_table.py
foo
bar
Object Relational join two tables
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.engine.url import URL
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
name = Column(String)
addresses = relationship("Address", backref="user")
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, primary_key=True)
email = Column(String)
user_id = Column(Integer, ForeignKey('user.id'))
db_url = {'drivername': 'postgres',
'username': '******',
'password': '******',
'host': '192.168.99.100',
'port': 5432}
# create engine
engine = create_engine(URL(**db_url))
# create tables
Base.metadata.create_all(bind=engine)
# create session
Session = sessionmaker()
Session.configure(bind=engine)
session = Session()
user = User(name='user1')
mail1 = Address(email='*****@*****.**')
mail2 = Address(email='*****@*****.**')
user.addresses.extend([mail1, mail2])
session.add(user)
session.add_all([mail1, mail2])
session.commit()
query = session.query(Address, User).join(User)
for _a, _u in query.all():
print(_u.name, _a.email)
output:
$ python sqlalchemy_join.py
user1 [email protected]
user1 [email protected]
join on relationship and group_by count
from sqlalchemy import (
create_engine,
Column,
String,
Integer,
ForeignKey,
func)
from sqlalchemy.orm import (
relationship,
sessionmaker,
scoped_session)
from sqlalchemy.ext.declarative import declarative_base
db_url = 'sqlite://'
engine = create_engine(db_url)
Base = declarative_base()
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
name = Column(String)
children = relationship('Child', back_populates='parent')
class Child(Base):
__tablename__ = 'child'
id = Column(Integer, primary_key=True)
name = Column(String)
parent_id = Column(Integer, ForeignKey('parent.id'))
parent = relationship('Parent', back_populates='children')
Base.metadata.create_all(bind=engine)
Session = scoped_session(sessionmaker(bind=engine))
p1 = Parent(name="Alice")
p2 = Parent(name="Bob")
c1 = Child(name="foo")
c2 = Child(name="bar")
c3 = Child(name="ker")
c4 = Child(name="cat")
p1.children.extend([c1, c2, c3])
p2.children.append(c4)
try:
Session.add(p1)
Session.add(p2)
Session.commit()
# count number of children
q = Session.query(Parent, func.count(Child.id))\
.join(Child)\
.group_by(Parent.id)
# print result
for _p, _c in q.all():
print('parent: {}, num_child: {}'.format(_p.name, _c))
finally:
Session.remove()
output:
$ python join_group_by.py
parent: Alice, num_child: 3
parent: Bob, num_child: 1
Create tables with dynamic columns (ORM)
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String
from sqlalchemy import inspect
from sqlalchemy.engine.url import URL
from sqlalchemy.ext.declarative import declarative_base
db_url = {'drivername': 'postgres',
'username': '******',
'password': '******',
'host': '192.168.99.100',
'port': 5432}
engine = create_engine(URL(**db_url))
Base = declarative_base()
def create_table(name, cols):
Base.metadata.reflect(engine)
if name in Base.metadata.tables: return
table = type(name, (Base,), cols)
table.__table__.create(bind=engine)
create_table('Table1', {
'__tablename__': 'Table1',
'id': Column(Integer, primary_key=True),
'name': Column(String)})
create_table('Table2', {
'__tablename__': 'Table2',
'id': Column(Integer, primary_key=True),
'key': Column(String),
'val': Column(String)})
inspector = inspect(engine)
for _t in inspector.get_table_names():
print(_t)
output:
$ python sqlalchemy_dynamic_orm.py
Table1
Table2
Close database connection
from sqlalchemy import (
create_engine,
event,
Column,
Integer)
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('sqlite://')
base = declarative_base()
@event.listens_for(engine, 'engine_disposed')
def receive_engine_disposed(engine):
print("engine dispose")
class Table(base):
__tablename__ = 'example table'
id = Column(Integer, primary_key=True)
base.metadata.create_all(bind=engine)
session = sessionmaker(bind=engine)()
try:
try:
row = Table()
session.add(row)
except Exception as e:
session.rollback()
raise
finally:
session.close()
finally:
engine.dispose()
output:
$ python db_dispose.py
engine dispose
Warning
Be careful. Close session does not mean close database connection. SQLAlchemy session generally represents the transactions, not connections.
Cannot use the object after close the session
from __future__ import print_function
from sqlalchemy import (
create_engine,
Column,
String,
Integer)
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
url = 'sqlite://'
engine = create_engine(url)
base = declarative_base()
class Table(base):
__tablename__ = 'table'
id = Column(Integer, primary_key=True)
key = Column(String)
val = Column(String)
base.metadata.create_all(bind=engine)
session = sessionmaker(bind=engine)()
try:
t = Table(key="key", val="val")
try:
print(t.key, t.val)
session.add(t)
session.commit()
except Exception as e:
print(e)
session.rollback()
finally:
session.close()
print(t.key, t.val) # exception raise from here
except Exception as e:
print("Cannot use the object after close the session")
finally:
engine.dispose()
output:
$ python sql.py
key val
Cannot use the object after close the session
Logo
This project tries to provide many snippets of Python code that make life easier.
Useful Links
pysheeet website
pysheeet @ GitHub
Issue Tracker
pysheeet as a PDF
ads via Carbon
Bring your team together with Slack, the collaboration hub for work.
ads via Carbon
Table of Contents
SQLAlchemy
Set a database URL
Sqlalchemy Support DBAPI - PEP249
Transaction and Connect Object
Metadata - Generating Database Schema
Inspect - Get Database Information
Reflection - Loading Table from Existing Database
Get Table from MetaData
Create all Tables Store in “MetaData”
Create Specific Table
Create table with same columns
Drop a Table
Some Table Object Operation
SQL Expression Language
insert() - Create an “INSERT” Statement
select() - Create a “SELECT” Statement
join() - Joined Two Tables via “JOIN” Statement
Delete Rows from Table
Check Table Existing
Create multiple tables at once
Create tables with dynamic columns (Table)
Object Relational add data
Object Relational update data
Object Relational delete row
Object Relational relationship
Object Relational self association
Object Relational basic query
mapper: Map Table to class
Get table dynamically
Object Relational join two tables
join on relationship and group_by count
Create tables with dynamic columns (ORM)
Close database connection
Cannot use the object after close the session
Quick search
©2016-2019, crazyguitar. | Page source
Fork me on GitHub
def main():
engine = create_engine(URL(**DATABASE), echo=False)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
import logging
import sys
import psycopg2 as pg
import string
from datetime import datetime, timezone
import databaseconfig as config
from sqlalchemy import create_engine, MetaData, Table, select
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.engine.url import URL
from sqlalchemy.sql.expression import bindparam
from sqlalchemy.pool import NullPool
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy_utils import get_mapper
engine = create_engine(URL(**config.postgres), poolclass=NullPool, echo=False)
metadata = MetaData()
metadata.reflect(engine)
Base = automap_base(metadata=metadata)
Base.prepare(engine, reflect=True)
Session= sessionmaker(bind=engine, autocommit=True)
_logger = logging.getLogger(__name__)
logformat = "[%(asctime)s] %(levelname)s:%(name)s:%(message)s"
logging.basicConfig(level=20, stream=sys.stdout,
format=logformat, datefmt="%Y-%m-%d %H:%M:%S")
products_api = "http://apiconsulta.medicamentos.gob.sv/public/productos"
def process_products(products, session):
products_table = metadata.tables['pharmaceuticals']
for prod in products:
def connect_database():
return create_engine(URL(**settings.DATABASE))
# 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 db_connect():
return create_engine(URL(**settings.DATABASE))
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 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
base_path = config.get("base", "path")
if not base_path:
base_path = './base.db'
config.set("base", "path", "./base.db")
config.commit()
Model = declarative_base()
DATABASE = {
'drivername': 'sqlite',
'database': base_path
}
dbengine = create_engine(URL(**DATABASE), connect_args={'check_same_thread': False})
Session = sessionmaker(bind=dbengine)
LOGGER.log(logging.INFO, msg="Creating session with database %s" % base_path)
session = Session()
class Bot(Model):
__tablename__ = 'bots'
name = Column(String, primary_key=True)
platform = Column(String)
token = Column(String, unique=True)
start_message = Column(String)
default_response = Column(String)
start_callback = Column(String)
message_callback = Column(String)
callback_auth = Column(Boolean)
def db_connect():
return create_engine(URL(**ProxyFetcher.settings.DATABASE))
from settings import dictionary
def to_dictionary(instance):
d = {}
for column in instance.__table__.columns:
d[column.name] = getattr(instance, column.name)
if isinstance(d[column.name], datetime):
d[column.name] = d[column.name].isoformat(' ')
if isinstance(d[column.name], date):
d[column.name] = d[column.name].isoformat()
return d
mysql = {}
mysql['engine'] = create_engine(
URL(**dictionary['MYSQL']),
convert_unicode=True,
echo=False,
pool_recycle=15,
pool_size=25,
pool_timeout=15,
strategy='threadlocal',
)
mysql['base'] = declarative_base(
bind=mysql['engine'], metadata=ThreadLocalMetaData()
)
mysql['base'].to_dictionary = to_dictionary
mysql['session'] = scoped_session(sessionmaker(
autocommit=False,
autoflush=False,
bind=mysql['engine'],
from sqlalchemy import Column, Integer, String, ForeignKey, create_engine, DateTime, Float
from sqlalchemy.engine.url import URL
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from consts import MYSQL
DB_URL = URL(drivername='mysql+pymysql',
username=MYSQL['user'],
password=MYSQL['password'],
host=MYSQL['host'],
port=MYSQL['port'],
database=MYSQL['data_base'])
Base = declarative_base()
class NmapScan(Base):
"""
The table which holds the metadata about the scan
"""
__tablename__ = 'nmap_scan'
id = Column(Integer, primary_key=True, autoincrement=True)
ip = Column(String(24))
start_time = Column(DateTime)
elapsed = Column(Float)
status = Column(String(32))
ports = relationship("PortScan", back_populates="npm_scan")
class PortScan(Base):
"""
The table which holds all the data about the ports that were scanned.
https://www.pythonsheets.com/notes/python-sqlalchemy.html
SQLAlchemy
Set a database URL
from sqlalchemy.engine.url import URL
postgres_db = {'drivername': 'postgres',
'username': '******',
'password': '******',
'host': '192.168.99.100',
'port': 5432}
print(URL(**postgres_db))
sqlite_db = {'drivername': 'sqlite', 'database': 'db.sqlite'}
print(URL(**sqlite_db))
output:
$ python sqlalchemy_url.py
postgres://postgres:[email protected]:5432
sqlite:///db.sqlite
Sqlalchemy Support DBAPI - PEP249
from sqlalchemy import create_engine
db_uri = "sqlite:///db.sqlite"
engine = create_engine(db_uri)
# DBAPI - PEP249
# create table
engine.execute('CREATE TABLE "EX1" ('
'id INTEGER NOT NULL,'
from contextlib import contextmanager
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.engine.url import URL
from sqlalchemy.event import listen
from sqlalchemy.sql import select, func
from idb.globals import DB_CONFIG
# Make a dict of URLs and dict of engines
urls = {k:URL(**v) for k,v in DB_CONFIG.items()}
engines = {k:create_engine(v) for k,v in urls.items()}
Base = declarative_base()
def load_spatialite(dbapi_conn, connection_record):
dbapi_conn.enable_load_extension(True)
dbapi_conn.load_extension('/usr/lib/x86_64-linux-gnu/mod_spatialite.so')
def init_db(env='main', engines=engines):
import idb.models
engine = engines[env]
if engine.url.drivername.startswith('sqlite'):
listen(engine, 'connect', load_spatialite)
conn = engine.connect()
conn.execute(select([func.InitSpatialMetaData()]))
conn.close()
"""
Hold Objects for SQL Tables and Manage Database Session
"""
from contextlib import contextmanager
import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.engine.url import URL
from sqlalchemy.orm import sessionmaker
import sys
sys.path.insert(0, './')
import settings
ENGINE = sa.create_engine(URL(**settings.DATABASE))
SESSION = sessionmaker(bind=ENGINE)
@contextmanager
def session_scope():
""" Control the SQL Database Session """
session = SESSION()
try:
yield session
session.commit()
print("sql command succeeded")
except:
session.rollback()
print("sql command failed")
finally:
session.close()
if __name__ == "__main__":
from model_state import Base, State
from sys import argv
import sqlalchemy
from sqlalchemy.engine.url import URL
from sqlalchemy import create_engine
from sqlalchemy.orm import Session
mysql = {
'drivername': 'mysql+mysqldb',
'host': 'localhost',
'port': '3306',
'username': argv[1],
'password': argv[2],
'database': argv[3],
}
url = URL(**mysql)
engine = create_engine(url, pool_pre_ping=True)
Base.metadata.create_all(engine)
session = Session(engine)
query = session.query(State).filter(State.name.like('%a%'))\
.order_by(State.id)
for r in query.all():
print("{}: {}".format(r.id, r.name))
session.close()
def get_connection_string(self):
return str(URL('sqlite+pysqlite', None, None, None, None, self.dbpath))
from sqlalchemy import *
from sqlalchemy.engine.url import URL
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.schema import ForeignKeyConstraint
from sqlalchemy.dialects.postgresql import ARRAY
from sqlalchemy.orm import sessionmaker, Session
postgres_db = {
"drivername": "postgres",
"username": "******",
"password": "******",
"host": "localhost",
"port": 5432,
}
engine = create_engine(URL(**postgres_db))
metadata = MetaData()
Base = declarative_base(bind=engine, metadata=metadata)
class Games(Base):
__tablename__ = "games"
game_id = Column(BIGINT, primary_key=True)
def __repr__(self):
return f"{self.game_id}"
class GamePlayerMetadata(Base):
__tablename__ = "game_player_metadata"
def get_engine() -> Engine:
return create_engine(URL(**DATABASE))
def make_engine():
engine = create_engine(URL(**DATABASE_CONNECTION), poolclass=NullPool)
return engine.connect()
def db_connect():
"""
Performs database connection using database settings from settings.py.
Returns sqlalchemy engine instance
"""
return create_engine(URL(**frapy.settings.DATABASE))
def SQLALCHEMY_DATABASE_URI(self):
return URL(self.engine,
username=self.username,
password=self.password,
host=self.host,
database=self.database)
def __init__(
self,
credentials,
table_name,
key_columns,
fixed_length_key=True,
suppress_store_backend_id=False,
manually_initialize_store_backend_id: str = "",
store_name=None,
):
super().__init__(
fixed_length_key=fixed_length_key,
suppress_store_backend_id=suppress_store_backend_id,
manually_initialize_store_backend_id=
manually_initialize_store_backend_id,
store_name=store_name,
)
if not sqlalchemy:
raise ge_exceptions.DataContextError(
"ModuleNotFoundError: No module named 'sqlalchemy'")
if not self.fixed_length_key:
raise ge_exceptions.InvalidConfigError(
"DatabaseStoreBackend requires use of a fixed-length-key")
drivername = credentials.pop("drivername")
schema = credentials.pop("schema", None)
options = URL(drivername, **credentials)
self.engine = create_engine(options)
meta = MetaData(schema=schema)
self.key_columns = key_columns
# Dynamically construct a SQLAlchemy table with the name and column names we'll use
cols = []
for column in key_columns:
if column == "value":
raise ge_exceptions.InvalidConfigError(
"'value' cannot be used as a key_element name")
cols.append(Column(column, String, primary_key=True))
cols.append(Column("value", String))
try:
table = Table(table_name,
meta,
autoload=True,
autoload_with=self.engine)
# We do a "light" check: if the columns' names match, we will proceed, otherwise, create the table
if {str(col.name).lower()
for col in table.columns
} != (set(key_columns) | {"value"}):
raise ge_exceptions.StoreBackendError(
f"Unable to use table {table_name}: it exists, but does not have the expected schema."
)
except NoSuchTableError:
table = Table(table_name, meta, *cols)
try:
if schema:
self.engine.execute(
f"CREATE SCHEMA IF NOT EXISTS {schema};")
meta.create_all(self.engine)
except SQLAlchemyError as e:
raise ge_exceptions.StoreBackendError(
f"Unable to connect to table {table_name} because of an error. It is possible your table needs to be migrated to a new schema. SqlAlchemyError: {str(e)}"
)
self._table = table
# Initialize with store_backend_id
self._store_backend_id = None
self._store_backend_id = self.store_backend_id
def get_url():
"""
Return the URL to be used with engine creation based on configuration
"""
used = Profile.get_used()
return URL(**dict(used))
