def create_book(sqlite_file=None,
uri_conn=None,
currency="EUR",
overwrite=False,
keep_foreign_keys=False,
db_type=None,
db_user=None,
db_password=None,
db_name=None,
db_host=None,
db_port=None,
check_same_thread=True,
**kwargs):
"""Create a new empty GnuCash book. If both sqlite_file and uri_conn are None, then an "in memory" sqlite book is created.
:param str sqlite_file: a path to an sqlite3 file (only used if uri_conn is None)
:param str uri_conn: a sqlalchemy connection string
:param str currency: the ISO symbol of the default currency of the book
:param bool overwrite: True if book should be deleted and recreated if it exists already
:param bool keep_foreign_keys: True if the foreign keys should be kept (may not work at all with GnuCash)
:param str db_type: type of database in ["postgres","mysql"]
:param str db_user: username of database
:param str db_password: password for the use of database
:param str db_name: name of database
:param str db_host: host of database
:param str db_port: port of database
:param bool check_same_thread: sqlite flag that restricts connection use to the thread that created (see False for use in ipython/flask/... but read first https://docs.python.org/3/library/sqlite3.html)
:return: the document as a gnucash session
:rtype: :class:`GncSession`
:raises GnucashException: if document already exists and overwrite is False
"""
from sqlalchemy_utils.functions import database_exists, create_database, drop_database
VERSION_FORMAT = "3.0"
uri_conn = build_uri(
sqlite_file,
uri_conn,
db_type,
db_user,
db_password,
db_name,
db_host,
db_port,
check_same_thread,
)
# create database (if DB is not a sqlite in memory)
if uri_conn != "sqlite:///:memory:":
if database_exists(uri_conn):
if overwrite:
drop_database(uri_conn)
else:
raise GnucashException(
"'{}' db already exists".format(uri_conn))
create_database(uri_conn)
engine = create_piecash_engine(uri_conn, **kwargs)
# drop constraints if we de not want to keep them (keep_foreign_keys=False), the default
if not keep_foreign_keys:
for n, tbl in DeclarativeBase.metadata.tables.items():
# drop index constraints
for idx in tbl.indexes:
if idx.name.startswith("ix_") or idx.name.startswith("_"):
event.listen(tbl,
"after_create",
DropIndex(idx),
once=True)
# drop FK constraints
for cstr in tbl.constraints:
if isinstance(cstr, PrimaryKeyConstraint):
continue
else:
event.listen(tbl,
"before_drop",
DropConstraint(cstr),
once=True)
#
# create all (tables, fk, ...)
DeclarativeBase.metadata.create_all(engine)
s = Session(bind=engine)
# create all rows in version table
assert VERSION_FORMAT in version_supported, "The 'version_format'={} is not supported. " \
"Choose one of {}".format(VERSION_FORMAT,
list(version_supported.keys()))
for table_name, table_version in version_supported[VERSION_FORMAT].items():
s.add(
Version(table_name=table_name,
table_version=table_version if isinstance(
table_version, int) else max(table_version)))
# create book and merge with session
b = Book()
s.add(b)
adapt_session(s, book=b, readonly=False)
# create commodities and initial accounts
from .account import Account
b.root_account = Account(
name="Root Account",
type="ROOT",
commodity=factories.create_currency_from_ISO(currency),
book=b)
b.root_template = Account(name="Template Root",
type="ROOT",
commodity=None,
book=b)
b.save()
return b
def test_create_currency_from_ISO_web(self, book_basic):
if is_not_on_web():
return
assert factories.create_currency_from_ISO(
"CAD", from_web=True).fullname == "Canadian Dollar"
def test_create_currency_from_ISO(self, book_basic):
assert factories.create_currency_from_ISO(
"CAD").fullname == "Canadian Dollar"
with pytest.raises(ValueError):
factories.create_currency_from_ISO("EFR").fullname
def test_create_currency_from_ISO(self, book_basic):
assert factories.create_currency_from_ISO("CAD").fullname == "Canadian Dollar"
with pytest.raises(ValueError):
factories.create_currency_from_ISO("EFR").fullname
#!/usr/bin/env python
# # @file
# @brief Creates a basic set of accounts and a couple of transactions
# @ingroup python_bindings_examples
from decimal import Decimal
import os
import tempfile
from piecash import create_book, Account, Transaction, Split, Commodity
from piecash.core.factories import create_currency_from_ISO
FILE_1 = os.path.join(tempfile.gettempdir(), "example.gnucash")
with create_book(FILE_1, overwrite=True) as book:
root_acct = book.root_account
cad = create_currency_from_ISO("CAD")
expenses_acct = Account(parent=root_acct,
name="Expenses",
type="EXPENSE",
commodity=cad)
savings_acct = Account(parent=root_acct,
name="Savings",
type="BANK",
commodity=cad)
opening_acct = Account(parent=root_acct,
name="Opening Balance",
type="EQUITY",
commodity=cad)
num1 = Decimal("4")
num2 = Decimal("100")
num3 = Decimal("15")
#!/usr/bin/env python
## @file
# @brief Example Script simple sqlite create
# @ingroup python_bindings_examples
from __future__ import print_function
import os
from piecash import create_book, Account, Commodity, open_book
from piecash.core.factories import create_currency_from_ISO
filename = os.path.abspath("test.blob")
if os.path.exists(filename):
os.remove(filename)
with create_book(filename) as book:
a = Account(
parent=book.root_account,
name="wow",
type="ASSET",
commodity=create_currency_from_ISO("CAD"),
)
book.save()
with open_book(filename) as book:
print(book.root_account.children)
print(book.commodities.get(mnemonic="CAD"))
os.remove(filename)
def test_create_currency_from_ISO_web(self, book_basic):
if is_not_on_web():
return
assert factories.create_currency_from_ISO("CAD", from_web=True).fullname == "Canadian Dollar"
#!/usr/bin/env python
## @file
# @brief Example Script simple sqlite create
# @ingroup python_bindings_examples
from __future__ import print_function
import os
from piecash import create_book, Account, Commodity, open_book
from piecash.core.factories import create_currency_from_ISO
filename = os.path.abspath('test.blob')
if os.path.exists(filename):
os.remove(filename)
with create_book(filename) as book:
a = Account(parent=book.root_account,
name="wow",
type="ASSET",
commodity=create_currency_from_ISO("CAD"))
book.save()
with open_book(filename) as book:
print(book.root_account.children)
print(book.commodities.get(mnemonic="CAD"))
os.remove(filename)
def create_book(sqlite_file=None,
uri_conn=None,
currency="EUR",
overwrite=False,
keep_foreign_keys=False,
db_type=None,
db_user=None,
db_password=None,
db_name=None,
db_host=None,
db_port=None,
check_same_thread=True,
**kwargs):
"""Create a new empty GnuCash book. If both sqlite_file and uri_conn are None, then an "in memory" sqlite book is created.
:param str sqlite_file: a path to an sqlite3 file (only used if uri_conn is None)
:param str uri_conn: a sqlalchemy connection string
:param str currency: the ISO symbol of the default currency of the book
:param bool overwrite: True if book should be deleted and recreated if it exists already
:param bool keep_foreign_keys: True if the foreign keys should be kept (may not work at all with GnuCash)
:param str db_type: type of database in ["postgres","mysql"]
:param str db_user: username of database
:param str db_password: password for the use of database
:param str db_name: name of database
:param str db_host: host of database
:param str db_port: port of database
:param bool check_same_thread: sqlite flag that restricts connection use to the thread that created (see False for use in ipython/flask/... but read first https://docs.python.org/3/library/sqlite3.html)
:return: the document as a gnucash session
:rtype: :class:`GncSession`
:raises GnucashException: if document already exists and overwrite is False
"""
from sqlalchemy_utils.functions import database_exists, create_database, drop_database
VERSION_FORMAT = "3.0"
uri_conn = build_uri(sqlite_file, uri_conn,
db_type, db_user, db_password, db_name, db_host, db_port,
check_same_thread,
)
# create database (if DB is not a sqlite in memory)
if uri_conn != "sqlite:///:memory:":
if database_exists(uri_conn):
if overwrite:
drop_database(uri_conn)
else:
raise GnucashException("'{}' db already exists".format(uri_conn))
create_database(uri_conn)
engine = create_piecash_engine(uri_conn, **kwargs)
# drop constraints if we de not want to keep them (keep_foreign_keys=False), the default
if not keep_foreign_keys:
for n, tbl in DeclarativeBase.metadata.tables.items():
# drop index constraints
for idx in tbl.indexes:
if idx.name.startswith("ix_") or idx.name.startswith("_"):
event.listen(tbl,
"after_create",
DropIndex(idx),
once=True)
# drop FK constraints
for cstr in tbl.constraints:
if isinstance(cstr, PrimaryKeyConstraint):
continue
else:
event.listen(tbl,
"before_drop",
DropConstraint(cstr),
once=True)
#
# create all (tables, fk, ...)
DeclarativeBase.metadata.create_all(engine)
s = Session(bind=engine)
# create all rows in version table
assert VERSION_FORMAT in version_supported, "The 'version_format'={} is not supported. " \
"Choose one of {}".format(VERSION_FORMAT,
list(version_supported.keys()))
for table_name, table_version in version_supported[VERSION_FORMAT].items():
s.add(Version(table_name=table_name, table_version=table_version))
# create book and merge with session
b = Book()
s.add(b)
adapt_session(s, book=b, readonly=False)
# create commodities and initial accounts
from .account import Account
b.root_account = Account(name="Root Account", type="ROOT",
commodity=factories.create_currency_from_ISO(currency),
book=b)
b.root_template = Account(name="Template Root", type="ROOT", commodity=None, book=b)
b.save()
return b
def get_or_create_currency(book, currency):
output = book.commodities(namespace="CURRENCY", mnemonic=currency)
if output is None:
output = create_currency_from_ISO(currency)
book.add(output)
return output
