def create_project(project_id, project_description):
"""Takes arguments and create a new project."""
db.connect()
Project.create(project_id=project_id, project_description=project_description)
db.close()
def add_donations():
DONATION_AMT = 0
LAST_NAME = 1
donations = [(1.0, 'Halpert'), (1000.0, 'Beesley'), (2000.0, 'Beesley'),
(3000.0, 'Beesley'), (2.0, 'Shrute'), (3.0, 'Shrute'),
(10.0, 'Scott'), (20.0, 'Scott'), (30.0, 'Scott'),
(10.0, 'Bernard'), (20.0, 'Bernard'), (30.0, 'Bernard')]
try:
db.connect()
for donation in donations:
with db.transaction():
n = Donation.create( # noqa F403
donation=donation[DONATION_AMT],
donor=donation[LAST_NAME])
n.save()
logger.info('Donations added.')
except Exception as e:
logger.info(e)
finally:
logger.info('database closes')
db.close()
def new_task():
data: json = request.json
try:
db.connect()
task: Task = Task(
project=int(data['project_id']),
name=str(data['name']),
details=str(data['description']),
progress=int(data['status']),
start_date=datetime.datetime.strptime(data['start_date'],
'%m/%d/%Y').date()
if data['start_date'] else datetime.datetime.today().date(),
end_date=datetime.datetime.strptime(data['end_date'],
'%m/%d/%Y').date()
if data['end_date'] else datetime.datetime.today().date())
task.save()
except Exception as e:
print(e)
message = {"status": False, "error": str(e), "data": {}}
else:
message = {"status": True, "error": {}, "data": {}}
finally:
db.close()
return message
def add_people():
FIRST_NAME = 0
LAST_NAME = 1
donors = [('Jim', 'Halpert'), ('Pam', 'Beesley'), ('Dwight', 'Shrute'),
('Michael', 'Scott'), ('Andy', 'Bernard')]
try:
db.connect()
for donor in donors:
code = base64.b32encode(os.urandom(8)).decode().strip('=')
with db.transaction():
n = Donor.create( # noqa F403
code=code,
first_name=donor[FIRST_NAME],
last_name=donor[LAST_NAME])
n.save()
logger.info('People added.')
except Exception as e:
logger.info(e)
finally:
logger.info('database closes')
db.close()
def fetch(service, artist):
if service not in services:
return False
try:
metadata = utils.fetch(service, artist)
db.connect('testing')
artist_count = Artist.objects(name=artist, service=service).count()
if not artist_count:
mongo_artist = Artist(name=artist, service=service).save()
else:
mongo_artist = Artist.objects(name=artist, service=service).first()
for track in metadata[artist]['tracks']:
track_title = track
track_album = metadata[artist]['tracks'][track_title]['album']
track_encoding = metadata[artist]['tracks'][track_title]['encoding']
track_url = metadata[artist]['tracks'][track_title]['url']
track_folder = metadata[artist]['tracks'][track_title]['track_folder']
track_filename = metadata[artist]['tracks'][track_title]['track_filename']
media_count = Media.objects(artist=mongo_artist, title=track_title).count()
if not media_count:
data = open(track_folder + '/' + track_filename, 'rb')
f = File(name=track_filename, data=data).save()
media = Media(artist=mongo_artist, title=track_title, data=f).save()
return True
except:
err = sys.exc_info()[0]
return False
def db_init():
db.connect()
if not User.table_exists():
User.create_table()
if not Logs.table_exists():
Logs.create_table()
if not Shortening.table_exists():
Shortening.create_table()
def create_outgoing_stock(stock, project_id, date, quantity, username):
"""Takes arguments and create a new record for outgoing stocks."""
db.connect()
OutgoingStock.create(stock=stock, project_id=project_id, date=date, quantity=quantity,
username=username)
db.close()
def setUp(self):
"""
Initialize client to send HTTP requests
and run migrations on the test db
"""
self.test_client = app.test_client()
db.connect()
db.create_tables([Game, LetterGuessed])
def get_group(self, title):
db.connect()
try:
g = Group.get(Group.title == title)
except Group.DoesNotExist:
g = Group.create(title = title)
db.close()
return g
def create_user(**kwargs):
"""Takes arguments and create a new user.
:param kwargs:
"""
db.connect()
User.create(first_name=kwargs['first_name'], second_name=kwargs['second_name'],
phone=kwargs['phone'], role=kwargs['role'], username=kwargs['username'],
password=generate_password_hash(kwargs['password']), email=kwargs['email'])
def db_connection():
memdb = CountingSqliteDatabase(":memory:")
models = [User, Post, Follow, Like]
db.initialize(memdb)
db.connect()
db.create_tables(models)
yield memdb
db.drop_tables(models)
db.close()
def create_incoming_stock(**kwargs):
"""Takes arguments and create a new record for incoming stocks."""
db.connect()
IncomingStock.create(stock=kwargs['stock'], date=kwargs['date'],
quantity=kwargs['quantity'], price=kwargs['price'],
cost=kwargs['quantity']*kwargs['price'])
db.close()
def setup_tables():
"""
Creates the tables for the Arrested Development database.
"""
db.connect()
Joke.create_table()
Episode.create_table()
EpisodeJoke.create_table()
JokeConnection.create_table()
def setup_tables():
"""
Creates the tables for the Arrested Development database.
"""
db.connect()
Joke.create_table()
Episode.create_table()
EpisodeJoke.create_table()
JokeConnection.create_table()
def use_db():
db.connect()
db.drop_tables(app_models)
db.create_tables(app_models)
yield db
db.close()
def inner(*args, **kwargs):
db.connect()
try:
response = view(*args, **kwargs)
except:
raise
else:
return response
finally:
db.close()
def __init__(self):
try:
db.connect()
except Exception:
db.init(DB_FILE)
db.connect()
cmd_list_loaded = self.load_command_list()
if not cmd_list_loaded:
logger.error("No commands available")
def make_task(server_id, status):
sleep(10)
db.connect()
try:
server = Server.get(Server.server_id == server_id)
except Server.DoesNotExist:
db.close()
return False
server.status = status
server.save()
db.close()
return True
def main():
print('Press Ctrl+{0} to exit'.format('Break' if os.name == 'nt' else 'C'))
try:
db.connect()
sched.start()
except (KeyboardInterrupt, SystemExit):
pass
except Exception:
logging.error(traceback.print_exc())
finally:
sched.shutdown(wait=False)
db.close()
def update_entry(data):
# Do not rely that connection will be kept running.
# If it is known to last as long as the task, just make it global
logging.warn("Updating database")
print data
db.connect()
result_data = ResultData.get(task_id=data["task_id"])
result_data.status = data["status_code"]
result_data.task_status = data["status"]
result_data.reason = data["reason"]
result_data.save()
return result_data.to_json()
def get_project(project_id: int):
try:
db.connect()
project: Project = Project.get(Project.id == int(project_id))
except Exception as e:
print(e)
message = {"status": False, "error": str(e), "data": {}}
else:
message = {"status": True, "error": {}, "data": project.dict()}
finally:
db.close()
return message
def __init__(self, **options):
super().__init__(**options)
self.log = logger.get_logger('Alexis')
db.connect()
db.create_tables([Post, Ban, Redditor, Meme], True)
try:
with open('config.yml', 'r') as file:
self.config = yaml.safe_load(file)
except Exception as ex:
self.log.exception(ex)
raise
def get_project_tasks(project_id: int):
tasks: List[Task] = []
try:
db.connect()
for task in Task.select(Task.project.id == int(project_id)):
tasks.append(task.dict())
except Exception as e:
print(e)
message = {"status": False, "error": str(e), "data": {}}
else:
message = {"status": True, "error": {}, "data": tasks}
finally:
db.close()
return message
def get_projects():
projects: List[Project] = []
try:
db.connect()
for project in Project.select():
projects.append(project.dict())
except Exception as e:
print(e)
message = {"status": False, "error": str(e), "data": {}}
else:
message = {"status": True, "error": {}, "data": projects}
finally:
db.close()
return message
def main():
# Parse command line
import argparse
parser = argparse.ArgumentParser(description='Add words to the SketchWithUs database')
parser.add_argument('input', help='file containing one word per line')
args = parser.parse_args()
# Connect to the database
db.connect()
# Insert all the words
with db.transaction():
for chunk in _grouper(1000, _file_iter(args.input)):
print chunk
Word.insert_many({'text': x, 'plays': 0, 'wins': 0} for x in chunk).execute()
def query_donors():
try:
db.connect()
query = (
Donor # noqa F403
.select(Donor, Donation) # noqa F403
.join(Donation, JOIN.INNER)) # noqa F403
for row in query:
logger.info(f'{row.last_name} {row.donation.donation}')
except Exception as e:
logger.info(e)
finally:
logger.info('database closes')
db.close()
def check_donor(key):
try:
db.connect()
query = (
Donor # noqa F403
.select(Donor, Donation) # noqa F403
.join(Donation, JOIN.INNER) # noqa F403
.where(Donor.last_name == key)) # noqa F403
if query.exists():
return True
except Exception as e:
logger.info(e)
finally:
logger.info('database closes')
db.close()
def create_db():
"""
Re-creates the entire database from the scratch.
"""
import inspect
import os
import models
from models import DATABASE, db
def is_model(obj):
return (inspect.isclass(obj) and
issubclass(obj, models.BaseModel) and
obj is not models.BaseModel)
models = [value for (name, value) in inspect.getmembers(models, is_model)]
os.remove(DATABASE)
db.connect()
db.create_tables(models)
db.close()
def run():
gs = GSData()
db.connect()
with open('data.txt', encoding='utf-8') as f:
for line in f:
wx_name = line.split(' ')[0]
try:
data = gs.query(wx_name)
except GSException as ex:
print(str(ex))
continue
else:
for item in data:
url = 'https://{0}'.format(item['url'].split('://', 1)[1])
md5s = hashlib.md5(url.encode('utf-8')).hexdigest()
if Paper.select().where(Paper.url_hash == md5s).count():
continue
print(item)
p = Paper.create(
wx_name=item['wx_name'],
name=item['name'],
title=item['title'],
author=item['author'],
content=item['content'],
url=url,
url_hash=md5s,
post_time=datetime.strptime(item['posttime'],
'%Y-%m-%d %H:%M:%S'),
add_time=datetime.strptime(item['add_time'],
'%Y-%m-%d %H:%M:%S'))
if type(item['readnum_newest']) == int:
p.read_num = item['readnum_newest']
if type(item['likenum_newest']) == int:
p.like_num = item['likenum_newest']
if item['picurl']:
p.pic_url = item['picurl']
p.save()
sleep(3)
db.close()
def main(argv):
db.connect()
try:
Person.drop_table()
except sqlite3.OperationalError:
pass
Person.create_table()
with open(argv[1]) as f:
csv_file = DictReader(f)
for line in csv_file:
attrs = {
'trac_id': float(line['TRAC Assigned Identifier for Individual'].strip()),
'nationality': line['Nationality'].strip(),
'gender': line['Gender'].strip(),
}
try:
person = Person.select().where((Person.trac_id == attrs['trac_id']) & (Person.nationality == attrs['nationality'])).get()
except Person.DoesNotExist:
person = Person.create(**attrs)
def main(argv):
db.connect()
try:
Facility.drop_table()
except sqlite3.OperationalError:
pass
Facility.create_table()
with open(argv[1]) as f:
csv_file = DictReader(f)
for line in csv_file:
attrs = {
'name': line['name'].strip(),
'official_name': line['official_name'].strip(),
'url': line['url'].strip(),
}
if len(line['geo_latitude']):
attrs['latitude'] = float(line['geo_latitude'].strip())
if len(line['geo_longitude']):
attrs['longitude'] = float(line['geo_longitude'].strip())
Facility.create(**attrs)
def __init__(self, *args, **kwargs):
"""
Combine the inits for marshmallow_jsonapi.Schema, marshmallow_sqlalchemy.ModelSchema.
Forces session=db.connect().
"""
# control if unwrap_item checks for the id field in the object.
self.load_existing = True
# Each instance of the schema should have the current session with the DB
# (marshmallow-sqlschema). This must be done on instance init, not on class creation!
kwargs['session'] = db.connect()
super().__init__(*args, **kwargs)
def main(argv):
db.connect()
try:
Detention.drop_table()
except sqlite3.OperationalError:
pass
Detention.create_table()
with open(argv[1]) as f:
date_format = '%d-%b-%y'
csv_file = DictReader(f)
for line in csv_file:
person = Person.select().where(Person.trac_id == line['TRAC Assigned Identifier for Individual'].strip()).get()
facility = Facility.select().where(Facility.name == line['Detention Facility'].strip()).get()
attrs = {
'person': person,
'facility': facility,
'book_in_date': datetime.strptime(line['Book-In Date'].strip(), date_format).date(),
'book_out_date': datetime.strptime(line['Book-Out Date'].strip(), date_format).date(),
'book_out_reason': line['Reason for Book-Out'].strip(),
}
Detention.create(**attrs)
def _get_all_today_expenses() -> int:
"""Возвращает все сегодняшние расходы"""
with connect() as conn, conn.cursor() as cur:
cur.execute(
"""
SELECT SUM(amount)
FROM expense
WHERE DATE(created)=DATE('NOW')
"""
)
result = cur.fetchone()
total_expenses = result[0] if result[0] else 0
return total_expenses
def store_json_data(self, json_data_list):
# start = time.clock()
try:
db.connect()
trump_twitter_id = "25073877"
for json_data in json_data_list:
is_trump_activity = json_data['user'][
'id_str'] == trump_twitter_id
if is_trump_activity:
self.trump_twitter_analyzer.trump_statuses(json_data)
else:
is_retweet = json_data.get('retweeted_status', False)
if is_retweet:
self.trump_twitter_analyzer.retweets_of_trump_data(
json_data)
else:
self.trump_twitter_analyzer.replies_to_trump_statuses(
json_data)
except Exception as e:
print(e)
finally:
db.close()
def main():
db.connect()
db.create_tables([Languages])
for language in languages.languages:
if language.part1 != '':
try:
wp_page = wptools.page(lang=language.part1).get_query()
wp_text = wp_page.extext
except pycurl.error: # No wikipedia articles for this language
wp_text = None
else:
wp_text = None
Languages.create(
name=language.name,
part1=language.part1,
part2=language.part2b,
part3=language.part3,
text=wp_text,
)
db.commit()
def _get_total_month_expenses() -> int:
"""Возвращает сумму расходов за текущий месяц"""
first_day_of_month = _get_first_day_of_month()
with connect() as conn, conn.cursor() as cur:
cur.execute(
"""
SELECT SUM(amount)
FROM expense
WHERE DATE(created) >= %s
""",
(first_day_of_month,),
)
result = cur.fetchone()
total_month_expenses = result[0] if result[0] else 0
return total_month_expenses
def add_expense(raw_message: str) -> Expense:
parsed_message = _parse_message(raw_message)
category = Categories().get_category(parsed_message.category_text)
with connect() as conn, conn.cursor() as cur:
cur.execute(
"""
INSERT INTO expense(amount, created, category_codename, raw_text)
VALUES (%s, NOW(), %s, %s)
""",
(
parsed_message.amount,
category.codename,
parsed_message.category_text,
),
)
return Expense(
id=None, amount=parsed_message.amount, category_name=category.name
)
def last(num: int) -> List[Expense]:
"""Возвращает последние несколько расходов"""
with connect() as conn, conn.cursor() as cur:
cur.execute(
"""
SELECT
expense.id,
expense.amount,
category.name
FROM expense
JOIN category
ON expense.category_codename = category.codename
ORDER BY created DESC
LIMIT %s
""",
(num,),
)
last_expenses = [Expense(*row) for row in cur]
return last_expenses
def _load_categories(self) -> List[Category]:
with connect() as conn, conn.cursor() as cur:
cur.execute("""
SELECT
codename,
name,
is_base_expense,
aliases
FROM category
""")
categories = [
Category(
codename=codename,
name=name,
is_base_expense=is_base_expense,
aliases=self._fill_aliases(aliases, codename, name),
) for (codename, name, is_base_expense, aliases) in cur
]
return categories
def _get_base_today_expenses() -> int:
"""Возвращает базовые расходы на сегодня"""
with connect() as conn, conn.cursor() as cur:
cur.execute(
"""
SELECT SUM(amount)
FROM expense
WHERE
DATE(created)=DATE('NOW')
AND category_codename IN (
SELECT codename
FROM category
WHERE is_base_expense=true
)
"""
)
result = cur.fetchone()
base_today_expenses = result[0] if result[0] else 0
return base_today_expenses
def _get_total_base_month_expenses() -> int:
"""Возвращает сумму базовых расходов за текущей месяц"""
first_day_of_month = _get_first_day_of_month()
with connect() as conn, conn.cursor() as cur:
cur.execute(
"""
SELECT SUM(amount)
FROM expense
WHERE
DATE(created) >= %s
AND category_codename IN (
SELECT codename
FROM category
WHERE is_base_expense=true
)
""",
(first_day_of_month,),
)
result = cur.fetchone()
total_base_month_expenses = result[0] if result[0] else 0
return total_base_month_expenses
def delete_expense(expense_id: int) -> None:
with connect() as conn, conn.cursor() as cur:
cur.execute(
"DELETE FROM expense WHERE id = %s", (expense_id,),
)
def create_tables():
db.connect()
db.create_tables([Message, Room, User])
db.close()
def create_role(role_name):
"""Takes the role name as an argument and create it."""
db.connect()
Role.create(role_name=role_name)
db.close()
def __init__(self):
db.connect()
def process_request(self, req, res):
db.connect()
def _db_connect():
db.connect()
def create_tables(): db.connect() db.create_tables([Athlete, Team, Game, Play, Collaboration, Action, Observation]) db.close()
def _get_budget_limit() -> int:
"""Возвращает дневной лимит трат для основных базовых трат"""
with connect() as conn, conn.cursor() as cur:
cur.execute("SELECT daily_limit FROM budget WHERE codename = 'base'")
base_limit = int(cur.fetchone()[0])
return base_limit
def drop_tables(): db.connect() db.drop_tables([Athlete, Team, Game, Play, Collaboration, Action, Observation]); db.close()
def before_request():
db.connect()
Perform database migrations.
Example usage within Vagrant VM:
$ SOUNDLOCALE_CONFIG=configuration_vagrant ./migrate.py
"""
import peewee
from psycopg2 import ProgrammingError
from app import app
from playhouse.migrate import *
from models import db
from models.user import User
from models.sound import Sound
# from models.migration_log import MigrationLog
db.init(app.config['DB_NAME'],
**{'password': app.config['DB_PASSWORD'],
'host': app.config['DB_HOST'],
'user': app.config['DB_USER']})
db.connect()
User.create_table(fail_silently=True)
Sound.create_table(fail_silently=True)
## TODO: migrations
# MigrationLog.create_table(fail_silently=True)
# max_id = MigrationLog.select(fn.Max(MigrationLog.id)).scalar()
# Generates error about PostgresqlMigrator not being defined:
# although see http://peewee.readthedocs.org/en/latest/peewee/playhouse.html#schema-migrations
# migrator = PostgresqlMigrator(db)
def connect_database():
"""Connect to SqliteDatabase"""
db.connect()
db.create_tables([File, Tag, FileTag], safe=True)
def setUp(self): db.connect() prepDB()
def initialize_entry(data, extra_attr={}):
db.connect()
# This should save the data
result_data = ResultData.from_json(data, extra_attr=extra_attr)
return result_data.to_json()
def db_connect():
""" Connect database """
db.connect()
def create_database():
db.connect()
try:
db.create_tables((User, Stat, Media.users.get_through_model(), Media))
except OperationalError:
print 'database already exists'
