| | |
- add_table(source, dest, column_mapping=None, catch_exception=False)
- adds data from source to dest, primary keys are re-generated.
This means that ALL rows from source are copied to dest by generating
new primary keys. In order for this to work, all primary keys
have to be of type integer and there must only be one single primary key.
parameters:
source -- the source table object
dest -- the source table object
column_mapping -- a dictionary mapping source columns to
dest-columns. Destination columns that are
not present are set to later on set
to "None". If this parameter is not given,
it is expected that all columns in source
must be matched to columns of the same name
in dest.
We first connect to a sqlite database in memory:
>>> (meta,conn)=connect_memory()
We now create now table objects in sqlalchemy:
>>> tbl = make_test_table(meta,"mytable" ,("id:int:primary","name:str"))
>>> tbl2= make_test_table(meta,"mytable2",("my-id:int:primary",
... "my-name:str","my-other:str"))
>>> set_table(tbl2,((1,"1","a"),(3,"3","c")))
>>> set_table(tbl ,((1,"1new"),(2,"2new")))
This is the content of the two tables:
>>> print_table(tbl2, Format.PLAIN)
('my-id', 'my-name', 'my-other')
(1, '1', 'a')
(3, '3', 'c')
>>> print_table(tbl, Format.PLAIN)
('id', 'name')
(1, '1new')
(2, '2new')
now we define a map mapping columns from tbl to tbl2:
>>> column_mapping= pdict.OneToOne({"id":"my-id","name":"my-name"})
Now we add tbl to tbl2:
>>> add_table(tbl, tbl2, column_mapping)
This is the result:
>>> print_table(tbl2, Format.PLAIN)
('my-id', 'my-name', 'my-other')
(1, '1', 'a')
(3, '3', 'c')
(4, '1new', None)
(5, '2new', None)
- auto_primary_key_possible(table_obj)
- checks if there is a single integer primary key.
Here is an example:
We first connect to a sqlite database in memory:
>>> (meta,conn)=connect_memory()
We now create now table objects in sqlalchemy:
>>> tbl = make_test_table(meta,"mytable" ,("id:int:primary","name:str"))
>>> tbl2= make_test_table(meta,"mytable2",("id:int:primary",
... "id2:int:primary","name:str"))
>>> tbl3= make_test_table(meta,"mytable3",("id:str:primary","name:str"))
>>> auto_primary_key_possible(tbl)
True
>>> auto_primary_key_possible(tbl2)
False
>>> auto_primary_key_possible(tbl3)
False
- column_dict(table_obj)
- returns a dictionary mapping column-names to column-objects.
Here is an example:
We first connect to a sqlite database in memory:
>>> (meta,conn)=connect_memory()
We now create tow table objects in sqlalchemy:
>>> tbl= make_test_table(meta,"mytable",("id:int:primary","name:str"))
>>> d= column_dict(tbl)
>>> for k in sorted(d.keys()):
... print "Name:",k
... print "Value",repr(d[k])
...
Name: ID
Value Column('id', Integer(), table=<mytable>, primary_key=True, nullable=False)
Name: NAME
Value Column('name', String(length=None, convert_unicode=False, assert_unicode=None, unicode_error=None, _warn_on_bytestring=False), table=<mytable>)
Name: id
Value Column('id', Integer(), table=<mytable>, primary_key=True, nullable=False)
Name: name
Value Column('name', String(length=None, convert_unicode=False, assert_unicode=None, unicode_error=None, _warn_on_bytestring=False), table=<mytable>)
- column_info(table_obj)
- returns a list of (name,type) tuples for columns.
Here is an example:
We first connect to a sqlite database in memory:
>>> (meta,conn)=connect_memory()
We now create tow table objects in sqlalchemy:
>>> tbl= make_test_table(meta,"mytable",("id:int:primary","name:str"))
>>> for tp in column_info(tbl):
... print tp
...
('id', 'INTEGER')
('name', 'VARCHAR')
- column_name_list(table_obj, upper_case=True, col_info=None)
- returns a list of column names in upper- or lower-case.
parameters:
table_obj -- the sqlalchemy table object
upper_case -- if True, convert column names to upper-case,
otherwise convert them to lower-case
col_info -- column info list that was created by
column_info(table_obj). If this parameter is
not given, column_name_list calls
column_info(table_obj) itself
returns:
a list of strings, representing the column names in
upper-case letters
Here is an example:
We first connect to a sqlite database in memory:
>>> (meta,conn)=connect_memory()
We now create tow table objects in sqlalchemy:
>>> tbl= make_test_table(meta,"mytable",("id:int:primary","name:str"))
column_name_list returns the column names as a list
of upper-case strings:
>>> column_name_list(tbl)
['ID', 'NAME']
- compare_types(type1, type2)
- compares two database or sqlalchemy types.
This function returns True, when the two types
are basically the same, e.g. when both represent
decimal numbers or both represent strings.
It uses the internal function pdb_type_from_str()
in order to compare the types. Note that
the types must be given as strings.
parameters:
type1 -- the first type as a string
type2 -- the second type as a string
returns:
True when the two types are basiclly the same
False otherwise
Here are some examples:
>>> compare_types("NUMERIC(38, 0)","Integer")
True
>>> compare_types("NUMERIC(38, 3)","Integer")
False
>>> compare_types("NUMERIC(38, 3)","Float")
True
>>> compare_types("VARCHAR(40)","String")
True
- connect_database(user=None, password=None, dialect='oracle', host='devices', dbname=None, echo=False, extra_args={})
- returns a metadata object and connection object.
This function creates a sql-engine and a metadata object.
Parameters:
user -- the user name, may be omitted
password -- the password, may be omitted
dialect -- the type of the database, for example
"sqlite","mysql","postgres" or "oracle"
host -- the hostname where the database instance
is running
dbname -- the name of the database.
echo -- echo all SQL statements to stdout
extra_args -- extra arguments that are passed to
sqlalchemy.create_engine()
An example how to connect to the bessy oracle database is:
(meta, conn)= connect_database(username,password,host="devices")
Here is another example:
>>> (meta,conn)=connect_database(dialect="sqlite",host=None,dbname=":memory:")
>>> repr(meta)
'MetaData(Engine(sqlite:///:memory:))'
>>> type(conn)
<class 'sqlalchemy.engine.base.Connection'>
- connect_memory(echo=False, extra_args={})
- returns connection to sqlite database in memory.
This function simply calls connect_database with a sqlite
dialect and a memory database.
Parameters:
echo -- echo all SQL statements to stdout
extra_args -- extra arguments that are passed to
sqlalchemy.create_engine()
Here is an example:
>>> (meta,conn)=connect_memory()
>>> repr(meta)
'MetaData(Engine(sqlite:///:memory:))'
>>> type(conn)
<class 'sqlalchemy.engine.base.Connection'>
- dtt_filter_fh(in_fh, out_fh, filter_func)
- remove tables from a dbitable collection file.
This function is used to remove tables from a dbitable collection.
This is a file with several tables in it stored in the dbitable
format. All lines from the input-file are read, and all filtered
lines are written to the output-file. A typical application of
this function is to re-write some but not all tables in a dbitable
collection file.
parameters:
in_fh -- filehandle of the opened input-file
out_fh -- filehandle of the opened output-file
filter_func -- this function is called with each tag found and
the filehandle of the new written file. If this
function returns "False", the tag is skipped,
if it returns "True", the table is copied.
Since the function has access to the newly
written file, it may write independently
a table to the file. When the last data in the
source file is encountered, filter_func is
called a last time with "None" as a tag.
Here is an example:
>>> txt_3_tables='''
... [Tag table1]
... [Version 1.0]
... [Properties]
... TABLE=table1 TYPE=file
... PK="ID1"
... FETCH_CMD="select * from table1"
...
... [Aliases]
...
... [Column-Types]
... number, string
... [Columns]
... ID1, NAME1
... [Table]
... 1|ab1
... 2|cd1
... #============================================================
... [Tag table2]
... [Version 1.0]
... [Properties]
... TABLE=table2 TYPE=file
... PK="ID2"
... FETCH_CMD="select * from table2"
...
... [Aliases]
...
... [Column-Types]
... number, string
... [Columns]
... ID2, NAME2
... [Table]
... 1|ab2
... 2|cd2
... #============================================================
... [Tag table3]
... [Version 1.0]
... [Properties]
... TABLE=table3 TYPE=file
... PK="ID3"
... FETCH_CMD="select * from table3"
...
... [Aliases]
...
... [Column-Types]
... number, string
... [Columns]
... ID3, NAME3
... [Table]
... 1|ab3
... 2|cd3
... #============================================================
... '''
>>> input= io.StringIO(txt_3_tables) # in python 2 StringIO.StringIO
>>> dtt_filter_fh(input,sys.stdout,lambda x,fh:x in ["table1","table3"])
<BLANKLINE>
[Tag table1]
[Version 1.0]
[Properties]
TABLE=table1 TYPE=file
PK="ID1"
FETCH_CMD="select * from table1"
<BLANKLINE>
[Aliases]
<BLANKLINE>
[Column-Types]
number, string
[Columns]
ID1, NAME1
[Table]
1|ab1
2|cd1
#============================================================
[Tag table3]
[Version 1.0]
[Properties]
TABLE=table3 TYPE=file
PK="ID3"
FETCH_CMD="select * from table3"
<BLANKLINE>
[Aliases]
<BLANKLINE>
[Column-Types]
number, string
[Columns]
ID3, NAME3
[Table]
1|ab3
2|cd3
#============================================================
- dtt_filter_file(filename, filter_func, replace_ext='bak')
- remove tables from a dbitable collection file.
This function is used to remove tables from a dbitable collection.
This is a file with several tables in it stored in the dbitable
format. All lines from the input-file are read, and all filtered
lines are written to the output-file. A typical application of
this function is to re-write some but not all tables in a dbitable
collection file.
- dtt_from_qsource(conn, tag, qsource_obj, trim_columns=True)
- return qsource converted to dbitable-text.
parameters:
conn -- the database connection object
tag -- the tag name that is used.
qsource_obj -- the Qsource object that is used to
generate the data. This is either a table
or a query.
trim_columns -- spaces are appended to values in order
to make aligned columns
Here are some examples:
>>> (meta,conn)=connect_memory()
>>> tbl= make_test_table(meta,"mytable",("id:int:primary","name:str"))
>>> set_table(tbl, ((1,"cd"),
... (40,"p|ped"),
... (2 ,"ab"),
... (30,"'quoted'"),
... (50,"back\slashed")))
>>> print dtt_from_qsource(conn,"complete table",
... Qsource(table=tbl,order_by=["name"],
... where="id>1"))
[Tag complete table]
[Version 1.0]
[Properties]
TABLE=mytable TYPE=file
PK="ID"
FETCH_CMD="SELECT mytable.id, mytable.name FROM mytable WHERE id>1 ORDER BY mytable.name"
<BLANKLINE>
[Aliases]
<BLANKLINE>
[Column-Types]
number, string
[Columns]
ID, NAME
[Table]
30|\'quoted\'
2 |ab
50|back\\slashed
40|p\|ped
#============================================================
<BLANKLINE>
>>> print dtt_from_qsource(conn,"partial table",
... Qsource(query="select * from mytable where id<10",
... pks=["id"]))
[Tag partial table]
[Version 1.0]
[Properties]
TYPE=file
PK="ID"
FETCH_CMD="select * from mytable where id<10"
<BLANKLINE>
[Aliases]
<BLANKLINE>
[Column-Types]
number, string
[Columns]
ID, NAME
[Table]
1|cd
2|ab
#============================================================
<BLANKLINE>
- dtt_read_tables(metadata, filename, tag_filter=None, dtt_dict={}, rstrip_mode=True, quote_mode=False, row_filter=None)
- reads a table from a dbitable compatible format.
This function creates a new table from a text in a dbitabletext
format that is read from a file.
parameters:
metadata -- the metadata object which is used to create
a new table
filename -- the name of the dbitabletext file
tag_filter -- a function that is called with each found tag.
If it returns None, that table is skipped. If it returns
an empty string, the table is read. If it returns a non-empty
string, the table is read and is stored with a name that is
equal to that string.
If this parameter is None, all tables are read from the file.
dtt_dict -- a dictionary mapping tags to DttResult objects.
If a table from the dbitabletext source is already
present here, all data is, after a compability check,
added there.
rstrip_mode -- if True, rstrip is performed on each read
value
quote_mode -- if True, single quotes around values are
removed and pipe "|" characters within
quoted sections are ignored.
row_filter -- This function is called with (flags_dict, value_dict).
The flags_dict provides some information about the table,
"tag" is the tag-name, "table" the table name and
"pks" is a list of primary key columns in lower-case.
The value_dict is a dictionary of all values in the
current row, together with their column names. The
function should return a (possibly modified) value_dict
or "None", in which case the row is skipped.
returns:
a dictionary mapping table names to table objects. All table objects
are new tables that are created in sqlite:memory. Note that the
table object names are all in lower-case.
for examples have a look at dtt_to_tables().
- dtt_read_tables_fh(metadata, fh, tag_filter=None, dtt_dict={}, rstrip_mode=True, quote_mode=False, row_filter=None)
- reads a table from a dbitable compatible format.
This function creates a new table from a text in a dbitabletext
format that is read from a file.
parameters:
metadata -- the metadata object which is used to create
a new table
fh -- a file-handle to an open file.
tag_filter -- a function that is called with each found tag.
If it returns None, that table is skipped. If it returns
an empty string, the table is read. If it returns a non-empty
string, the table is read and is stored with a name that is
equal to that string.
If this parameter is None, all tables are read from the file.
Each table is stored with it's tag in the table dictionary.
dtt_dict -- a dictionary mapping tags to DttResult objects.
If a table from the dbitabletext source is already
present here, all data is, after a compability check,
added there.
rstrip_mode -- if True, rstrip is performed on each read
value
quote_mode -- if True, single quotes around values are
removed and pipe "|" characters within
quoted sections are ignored.
row_filter -- This function is called with (flags_dict, value_dict).
The flags_dict provides some information about the table,
"tag" is the tag-name, "table" the table name and
"pks" is a list of primary key columns in lower-case.
The value_dict is a dictionary of all values in the
current row, together with their column names. The
function should return a (possibly modified) value_dict
or "None", in which case the row is skipped.
returns:
A new dictionary mapping tag names to dttresult objects. These objects
contain some of the dtt metadata as well as the created table objects.
All table objects are new tables created with the given metadata
parameter. Note that the table object names are all in lower-case.
for examples have a look at dtt_to_tables().
- dtt_tag_filter(tags)
- a utility for dtt_read_tables...
This function returns a tag-filter function for the simple
case tags that are in a list of tags shall be read and that
the names of tables are not to be altered.
- dtt_to_tables(metadata, txt, tag_filter=None, dtt_dict={}, rstrip_mode=True, quote_mode=False, row_filter=None)
- read a dbitable compatible text, return property list.
This function creates a new table from a text in a dbitabletext
format that is read from a file.
parameters:
metadata -- the metadata object which is used to create
a new table
txt -- a string containing the data.
tag_filter -- a function that is called with each found tag.
If it returns None, that table is skipped. If it returns
an empty string, the table is read. If it returns a non-empty
string, the table is read and is stored with a name that is
equal to that string.
If this parameter is None, all tables are read from the file.
dtt_dict -- a dictionary mapping tags to DttResult objects.
If a table from the dbitabletext source is already
present here, all data is, after a compability check,
added there.
rstrip_mode -- if True, rstrip is performed on each read
value
quote_mode -- if True, single quotes around values are
removed and pipe "|" characters within
quoted sections are ignored.
row_filter -- This function is called with (flags_dict, value_dict).
The flags_dict provides some information about the table.
Currently only "pks" is defined, which is a list of
primary key columns in lower-case. The value_dict is
a dictionary of all values in the current row, together
with their column names. The function should return
a (possibly modified) value_dict or "None", in which
case the row is skipped.
returns:
a dictionary mapping table names to table objects. All table objects
are new tables that are created in sqlite:memory. Note that the
table object names are all in lower-case.
Here are some examples:
First we define a dbitable-text:
>>> txt='''
... [Tag mytable]
... [Version 1.0]
... [Properties]
... TABLE=mytable TYPE=file
... PK="ID"
... FETCH_CMD="select * from mytable"
...
... [Aliases]
...
... [Column-Types]
... number, string
... [Columns]
... ID, NAME
... [Table]
... 1|cd
... 2|ab
... 3|\'quoted\'
... 4|p\|ped
... 5|back\\slashed
... '''
We connect to a memory sqlite database:
>>> (meta,conn)=connect_memory()
and we read the table from the text, note that
escaped characters (leading "\") are taken literally:
>>> tdict=dtt_to_tables(meta,txt,dtt_tag_filter(["mytable"]))
>>> tdict.keys()
['mytable']
>>> print tdict["mytable"]
DttResult:
tag=mytable
is_table=True
dtt_table_name=mytable
table_name=mytable
query_text=select * from mytable
primary_keys=set(['id'])
column_names=['id', 'name']
column_types=PDB_INT,PDB_STRING
table_obj=table_obj<mytable>
<BLANKLINE>
>>> print_table(tdict["mytable"].table_obj, Format.PLAIN)
('id', 'name')
(1, 'cd')
(2, 'ab')
(3, "'quoted'")
(4, 'p|ped')
(5, 'back\\slashed')
Now we show how data may be added to the table that was just
read:
>>> txt2='''
... [Tag mytable]
... [Version 1.0]
... [Properties]
... TABLE=mytable TYPE=file
... PK="ID"
... FETCH_CMD="select * from mytable"
...
... [Aliases]
...
... [Column-Types]
... number, string
... [Columns]
... ID, NAME
... [Table]
... 6|xy
... 7|zz
... '''
>>> tdict=dtt_to_tables(meta,txt2,dtt_tag_filter(["mytable"]),tdict)
>>> print_table(tdict["mytable"].table_obj, Format.PLAIN)
('id', 'name')
(1, 'cd')
(2, 'ab')
(3, "'quoted'")
(4, 'p|ped')
(5, 'back\\slashed')
(6, 'xy')
(7, 'zz')
Now we show how to read single tables from a dbitabletext collection,
this is a dbitabletext with several tables in it:
>>> txt_3_tables='''
... [Tag table1]
... [Version 1.0]
... [Properties]
... TABLE=table1 TYPE=file
... PK="ID1"
... FETCH_CMD="select * from table1"
...
... [Aliases]
...
... [Column-Types]
... number, string
... [Columns]
... ID1, NAME1
... [Table]
... 1|ab1
... 2|cd1
... #============================================================
... [Tag table2]
... [Version 1.0]
... [Properties]
... TABLE=table2 TYPE=file
... PK="ID2"
... FETCH_CMD="select * from table2"
...
... [Aliases]
...
... [Column-Types]
... number, string
... [Columns]
... ID2, NAME2
... [Table]
... 1|ab2
... 2|cd2
... #============================================================
... [Tag table3]
... [Version 1.0]
... [Properties]
... TABLE=table3 TYPE=file
... PK="ID3"
... FETCH_CMD="select * from table3"
...
... [Aliases]
...
... [Column-Types]
... number, string
... [Columns]
... ID3, NAME3
... [Table]
... 1|ab3
... 2|cd3
... #============================================================
... '''
Now we fetch just "table2" from this text:
>>> tdict= dtt_to_tables(meta,txt_3_tables,dtt_tag_filter(["table2"]))
>>> print tdict.keys()
['table2']
>>> print_table(tdict["table2"].table_obj, Format.PLAIN)
('id2', 'name2')
(1, 'ab2')
(2, 'cd2')
Now we do the same but this time we change the name of the table:
>>> tdict= dtt_to_tables(meta,txt_3_tables,
... lambda x: x+"xx" if x=="table2" else None)
>>> print tdict.keys()
['table2']
>>> tdict["table2"].table_name
'table2xx'
>>> print_table(tdict["table2"].table_obj, Format.PLAIN)
('id2', 'name2')
(1, 'ab2')
(2, 'cd2')
Now we fetch all tables, we create a new metadata object in
order to dispose the tables created so far:
>>> (meta,conn)=connect_memory()
>>> tdict= dtt_to_tables(meta,txt_3_tables,None)
>>> print sorted(tdict.keys())
['table1', 'table2', 'table3']
>>> for t in sorted(tdict.keys()):
... print "\nTable %s:" % t
... print_table(tdict[t].table_obj,Format.TABLE)
<BLANKLINE>
Table table1:
id1 | name1
----+------
1 | ab1
2 | cd1
<BLANKLINE>
Table table2:
id2 | name2
----+------
1 | ab2
2 | cd2
<BLANKLINE>
Table table3:
id3 | name3
----+------
1 | ab3
2 | cd3
Now we demonstrate how the row_filter function can be used to
filter or change the rows that are read. First we define a
dbitabletext with a single table in it:
>>> txt='''
... [Tag mytable]
... [Version 1.0]
... [Properties]
... TABLE=mytable TYPE=file
... PK="ID"
... FETCH_CMD="select * from mytable"
...
... [Aliases]
...
... [Column-Types]
... number, string
... [Columns]
... ID, NAME
... [Table]
... 1|cd
... 2|ab
... '''
We define a simple tag-filter function:
>>> def filter_tag(tag_wanted,new_name=""):
... def f(t):
... if t==tag_wanted:
... return new_name
... return None
... return f
We now use a filter-function to read only lines
where the id is smaller than 4:
>>> def below4(flags,values):
... if values["id"]<4:
... return values
... return None
...
>>> (meta,conn)=connect_memory()
>>>
>>> tdict=dtt_to_tables(meta,txt,filter_tag("mytable"),row_filter= below4)
>>> print_table(tdict["mytable"].table_obj, Format.PLAIN)
('id', 'name')
(1, 'cd')
(2, 'ab')
For the next tests, we need a source where some
rows have an empty primary key. We show different ways
to handle this.
>>> txt='''
... [Tag mytable]
... [Version 1.0]
... [Properties]
... TABLE=mytable TYPE=file
... PK="ID"
... FETCH_CMD="select * from mytable"
...
... [Aliases]
...
... [Column-Types]
... number, string
... [Columns]
... ID, NAME
... [Table]
... |cd
... 1|ab
... |\'quoted\'
... 2|p\|ped
... 3|back\\slashed
... '''
First we define a simple iterator that is
used to create the missing primary keys. The disadvantage is,
however, that this doesn't ensure that the generated primary
keys do not already exist:
>>> def myit(start):
... x=start
... while True:
... yield x
... x+=1
...
>>> m= myit(100)
>>>
>>> def pk_get(flags,values):
... for c in flags["pks"]:
... if values[c] is None:
... i= m.next()
... values.update( [(p,i) for p in flags["pks"]] )
... break
... return values
...
>>> (meta,conn)=connect_memory()
>>> tdict=dtt_to_tables(meta,txt,filter_tag("mytable"),row_filter= pk_get)
>>> print_table(tdict["mytable"].table_obj, Format.PLAIN)
('id', 'name')
(1, 'ab')
(2, 'p|ped')
(3, 'back\\slashed')
(100, 'cd')
(101, "'quoted'")
In the following example, we read the dbitable-text twice. First we fetch
all rows where the primary key is defined:
>>> def pk_defined(flags,values):
... for c in flags["pks"]:
... if values[c] is None:
... return None
... return values
...
>>> (meta,conn)=connect_memory()
>>> tdict= dtt_to_tables(meta,txt,filter_tag("mytable"),
... row_filter= pk_defined)
>>> print_table(tdict["mytable"].table_obj, Format.PLAIN)
('id', 'name')
(1, 'ab')
(2, 'p|ped')
(3, 'back\\slashed')
Now we can determine the largest primary key from that table and
use this to generate new primary keys for rows where these are
missing. The results, however, are stored in a separate table.
In order not to collide with the existing table, we have to
give it a different name, "mytable2":
>>> def pk_gen_gen(table):
... pks= primary_keys(table)
... max=func_query_as_dict(table,sqlalchemy.func.max)
... def pk_gen(flags,values):
... for c in flags["pks"]:
... if values[c] is not None:
... return None
... values.update( [(p,max[p]) for p in flags["pks"]] )
... for p in flags["pks"]:
... values[p]= max[p]
... max[p]+= 1
... return values
... return pk_gen
...
>>> (meta,conn)=connect_memory()
>>> tdict= dtt_to_tables(meta,txt,filter_tag("mytable","mytable2"),
... row_filter= pk_gen_gen(tdict["mytable"].table_obj))
In the following lines we see, that this table starts with a primary key
that is just one bigger than the largest primary key in "mytable" (see above):
>>> print_table(tdict["mytable"].table_obj, Format.PLAIN)
('id', 'name')
(3, 'cd')
(4, "'quoted'")
- dtt_write_qsource_fh(conn, tag, qsource, fh=<open file '<stdout>', mode 'w'>, trim_columns=True)
- write a Qsource object.
- dtt_write_qsources(conn, qsource_dict, filename='', trim_columns=True)
- write table to a filehandle in dbitabletext format.
parameters:
conn -- the database connection object
qsource_dict -- a dictionary of Qsource objects. A Qsource object
is either a sqlalchemy table or a sql
query string. The dtt_dict maps tags to Qsource
objects.
filename -- the name of the file where the results are
stored.
trim_columns -- spaces are appended to values in order
to make aligned columns
returns:
nothing
Here is an example:
In this example, we create a dbitabletext collection with a single
table. Then we add another table and finally we change the
contents of the first table. Note that the order of the tables
within the file changes when we do this. This is the standard
behaviour if we re-write tables in a collection.
# import bii_scripts.ptestlib as t
>>> (meta,conn)=connect_memory()
>>> tbl= make_test_table(meta,"mytable",("id:int:primary","name:str"))
>>> set_table(tbl, ((1,"cd"),(2,"ab")))
>>> t.inittestdir()
>>> filename= t.tjoin("table.txt")
>>> dtt_write_qsources(conn,{"mytable":Qsource(table=tbl)},filename)
>>> t.ls()
table.txt
<BLANKLINE>
>>> t.catfile("table.txt")
[Tag mytable]
[Version 1.0]
[Properties]
TABLE=mytable TYPE=file
PK="ID"
FETCH_CMD="SELECT mytable.id, mytable.name FROM mytable ORDER BY mytable.id"
<BLANKLINE>
[Aliases]
<BLANKLINE>
[Column-Types]
number, string
[Columns]
ID, NAME
[Table]
1|cd
2|ab
#============================================================
>>> tbl2= make_test_table(meta,"mytable2",("id2:int:primary","name2:str"))
>>> set_table(tbl2, ((1,"cd2"),(2,"ab2")))
>>> dtt_write_qsources(conn,{"mytable2":Qsource(table=tbl2)},filename)
>>> t.ls()
table.txt
table.txt.bak
<BLANKLINE>
>>> t.catfile("table.txt")
[Tag mytable]
[Version 1.0]
[Properties]
TABLE=mytable TYPE=file
PK="ID"
FETCH_CMD="SELECT mytable.id, mytable.name FROM mytable ORDER BY mytable.id"
<BLANKLINE>
[Aliases]
<BLANKLINE>
[Column-Types]
number, string
[Columns]
ID, NAME
[Table]
1|cd
2|ab
#============================================================
[Tag mytable2]
[Version 1.0]
[Properties]
TABLE=mytable2 TYPE=file
PK="ID2"
FETCH_CMD="SELECT mytable2.id2, mytable2.name2 FROM mytable2 ORDER BY mytable2.id2"
<BLANKLINE>
[Aliases]
<BLANKLINE>
[Column-Types]
number, string
[Columns]
ID2, NAME2
[Table]
1|cd2
2|ab2
#============================================================
>>> set_table(tbl, ((3,"ef"),(4,"gh")))
>>> dtt_write_qsources(conn,{"mytable":Qsource(table=tbl)},filename)
>>> t.ls()
table.txt
table.txt.bak
<BLANKLINE>
>>> t.catfile("table.txt")
[Tag mytable]
[Version 1.0]
[Properties]
TABLE=mytable TYPE=file
PK="ID"
FETCH_CMD="SELECT mytable.id, mytable.name FROM mytable ORDER BY mytable.id"
<BLANKLINE>
[Aliases]
<BLANKLINE>
[Column-Types]
number, string
[Columns]
ID, NAME
[Table]
1|cd
2|ab
3|ef
4|gh
#============================================================
[Tag mytable2]
[Version 1.0]
[Properties]
TABLE=mytable2 TYPE=file
PK="ID2"
FETCH_CMD="SELECT mytable2.id2, mytable2.name2 FROM mytable2 ORDER BY mytable2.id2"
<BLANKLINE>
[Aliases]
<BLANKLINE>
[Column-Types]
number, string
[Columns]
ID2, NAME2
[Table]
1|cd2
2|ab2
#============================================================
>>> t.cleanuptestdir()
- dtt_write_query_fh(conn, tag, query_text, primary_keys=[], fh=<open file '<stdout>', mode 'w'>, trim_columns=True)
- write a query result to a filehandle in dbitabletext format.
parameters:
conn -- the database connection object
tag -- the tag name that is used.
query_text -- the sql query
primary_keys -- a list of primary keys (optional)
fh -- an (open) filehandle where the result
is written to
trim_columns -- spaces are appended to values in order
to make aligned columns
returns:
nothing
In order to see an example have a look at dtt_from_qsource(),
this function calls dtt_write_query_fh().
- dtt_write_table_fh(tag, table_obj, fh=<open file '<stdout>', mode 'w'>, trim_columns=True, order_by=[], where_part='', query_text='', schema_name=None)
- write table to a filehandle in dbitabletext format.
parameters:
tag -- tag under which the table is stored
table_obj -- the sql table object
fh -- an (open) filehandle where_part the result
is written to
trim_columns -- spaces are appended to values in order
to make aligned columns
order_by -- a list of column names by which the output
is ordered. The default is to take the
primary keys.
where_part -- an optional string, the where_part part that
is added to query the table
query_text -- if this parameter is given, the table is
written "as if" it would be a query, that
means the table-name is not written, but
the query_text. However, ALL rows of the
table are written, the query text is not
really executed. If where_part is specified,
the resulting query overrides query_text.
schema_name -- An optional schema name that is written to the dtt
file. This parameter is only used when the schema
name of the table object is none. This is for
example needed to keep a schema name in the dtt
file although the database where the table object
was created doesn't know of schemas. An example is
sqlite, this database has no concept of schemas.
returns:
nothing
In order to see an example have a look at dtt_from_tables(),
this function calls dtt_write_tables_fh().
- fetchall(conn, sql_text)
- executes the statement and return a list of rows.
- foreign_key_to_tuple(foreign_key)
- convert foreign_key to a tuple of strings.
returns: (local-column-name,table-name,column-name)
Here is an example:
We first connect to a sqlite database in memory:
>>> (meta,conn)=connect_memory()
We now create tow table objects in sqlalchemy:
>>> tbl= make_test_table(meta,"myreftable",("id:int:primary","name:str"))
>>> tbl2= make_test_table(meta,"mytable",("id:int:primary",
... "other::foreign(myreftable.id)","name:str"))
Now we get a list of all foreign keys in the table "mytable":
>>> fks= [k for k in tbl2.foreign_keys]
The function foreign_key_to_tuple applied to the first key in the
list returns a tuple consisting of the local column name, the name of
the foreign table and the foreign column name:
>>> foreign_key_to_tuple(fks[0])
('other', 'myreftable', 'id')
- foreign_keys(table_obj)
- returns a list of foreign key tuples.
returns:
a list of tuples, each of the form:
(local-column-name,table-name,column-name)
Here is an example:
We first connect to a sqlite database in memory:
>>> (meta,conn)=connect_memory()
We now create tow table objects in sqlalchemy:
>>> tbl= make_test_table(meta,"myreftable",("id:int:primary","name:str"))
>>> tbl= make_test_table(meta,"mytable" ,("id:int:primary",
... "other_id::foreign(myreftable.id)",
... "other_name::foreign(myreftable.name)",
... "name:str"))
Now we get a list of all foreign keys in the table "mytable":
>>> foreign_keys(tbl)
[('other_id', 'myreftable', 'id'), ('other_name', 'myreftable', 'name')]
- func_query(table_obj, func, columns)
- applies a function to each of the given columns and returns a query.
Here are some examples:
We first connect to a sqlite database in memory:
>>> (meta,conn)=connect_memory()
We now create now table objects in sqlalchemy:
>>> tbl= make_test_table(meta,"mytable",("id:int:primary","name:str"))
>>> set_table(tbl, ((1,"cd"),(2,"ab")))
We now create a query to apply the count-function to all
columns:
>>> q= func_query(tbl, sqlalchemy.func.count, column_name_list(tbl, False))
Here is the SQL code that is generated:
>>> str(q)
'SELECT count(mytable.id) AS count_1, count(mytable.name) AS count_2 \nFROM mytable'
And here is the query executed:
>>> for r in q.execute():
... print r
...
(2, 2)
- func_query_as_dict(table_obj, func, columns=None)
- executes func_query but returns the results as a dict.
The query should return only a single line, otherwise
a ValueError exception is raised.
Here are some examples:
We first connect to a sqlite database in memory:
>>> (meta,conn)=connect_memory()
We now create now table objects in sqlalchemy:
>>> tbl= make_test_table(meta,"mytable",("id:int:primary","name:str"))
>>> set_table(tbl, ((1,"cd"),(2,"ab")))
We now count values:
>>> print _repr(func_query_as_dict(tbl,sqlalchemy.func.count))
{'id': 2, 'name': 2}
Now we determine "max":
>>> print _repr(func_query_as_dict(tbl,sqlalchemy.func.max))
{'id': 2, 'name': 'cd'}
- make_test_table(meta, name, column_spec, schema=None)
- create a table for test-purposes on the fly.
This function is used to create tables for test purposes
with one simple call. The column_spec parameter is a list of
strings, each specifying one column. Such a string consists
of one up to three parts, the column-name, the column-type and
a flag. The default for the column-type is sqlalchemy.Integer.
Known column-types are "int","str" and "" which is used
for foreign key columns. The following flag strings are known:
"primary" for primary key columns and "foreign(foreign-column-name)"
for foreign key columns.
parameters:
meta -- the metadata object, to which the table
will be connected.
name -- the name of the table
column_spec -- a list of strings specifying the columns.
schema -- if this parameter is given, this schema name
will be used to create the table.
returns:
a sqlalchemy table object
Here are some examples:
>>> (meta,conn)=connect_memory()
>>> tbl= make_test_table(meta,"mytable",("id:int:primary","name:str"))
>>> set_table(tbl, ((1,"test"),))
>>> print_table(tbl,Format.TABLE)
id | name
---+-----
1 | test
>>> tbl2= make_test_table(meta,"mytable2",("other::foreign(mytable.id)",
... "othername:str"))
>>> print repr(tbl2).replace(",",",\n")
Table('mytable2',
MetaData(Engine(sqlite:///:memory:)),
Column('other',
Integer(),
ForeignKey('mytable.id'),
table=<mytable2>),
Column('othername',
String(length=None,
convert_unicode=False,
assert_unicode=None,
unicode_error=None,
_warn_on_bytestring=False),
table=<mytable2>),
schema=None)
- ordered_query(table_obj, column_names=[], ascending=True)
- generate a query object, ordered by a list of column names.
If no columns are given, the query is ordered according to
the primary keys.
Here is an example:
We first connect to a sqlite database in memory:
>>> (meta,conn)=connect_memory()
We now create now table objects in sqlalchemy:
>>> tbl= make_test_table(meta,"mytable",("id:int:primary","name:str"))
>>> set_table(tbl, ((3,"ab"),(1,"cd"),(2,"ef")))
Now we can use the query to fetch all rows from the table:
>>> for row in ordered_query(tbl).execute():
... print _repr(row)
...
(1, 'cd')
(2, 'ef')
(3, 'ab')
Or we can order the query by some rows:
>>> for row in ordered_query(tbl,["name","id"]).execute():
... print _repr(row)
...
(3, 'ab')
(1, 'cd')
(2, 'ef')
The following lines show the SQL statement that the query
object contains:
>>> str(ordered_query(tbl,["name","id"]))
'SELECT mytable.id, mytable.name \nFROM mytable ORDER BY mytable.name, mytable.id'
Here we require a descending order:
>>> str(ordered_query(tbl,["name","id"], False))
'SELECT mytable.id, mytable.name \nFROM mytable ORDER BY mytable.name DESC, mytable.id DESC'
- pdb_column_type_dict(table_obj, col_info=None)
- create a dictionary mapping column-names to pdb-types.
This function creates a dictionary that maps column names of
the table object to pdb-types. For each column, it's uppercase
and it's lowercase name is added to the dictionary.
parameters:
table_obj -- the sqlalchemy table object
col_info -- column info list that was created by
column_info(table_obj). If this parameter is
not given, pdb_column_type_dict calls
column_info(table_obj) itself
returns:
a dictionary mapping the column names to their types. Note that
for each column-name there exist two entries, one for the
upper-case and one for the lower-case name.
Here is an example:
We first connect to a sqlite database in memory:
>>> (meta,conn)=connect_memory()
We now create tow table objects in sqlalchemy:
>>> tbl= make_test_table(meta,"mytable",("id:int:primary","name:str"))
>>> d= pdb_column_type_dict(tbl)
>>> for k in sorted(d.keys()):
... print "%-8s -> %s" % (k, str(d[k]))
...
ID -> PDB_INT
NAME -> PDB_STRING
id -> PDB_INT
name -> PDB_STRING
- pdb_column_types(table_obj, col_info=None)
- returns a list of column types.
parameters:
table_obj -- the sqlalchemy table object
col_info -- column info list that was created by
column_info(table_obj). If this parameter is
not given, pdb_column_types calls
column_info(table_obj) itself
returns:
a list of pdb_coltype values, representing the column types
Here is an example:
We first connect to a sqlite database in memory:
>>> (meta,conn)=connect_memory()
We now create tow table objects in sqlalchemy:
>>> tbl= make_test_table(meta,"mytable",("id:int:primary","name:str"))
We now print the generalized column types:
>>> for t in pdb_column_types(tbl):
... print str(t)
...
PDB_INT
PDB_STRING
- pdb_type_from_str(string)
- converts a string to an internal type.
Here are some examples:
>>> str(pdb_type_from_str('NUMERIC(38, 0)'))
'PDB_INT'
>>> str(pdb_type_from_str('NUMERIC(38, 1)'))
'PDB_FLOAT'
>>> str(pdb_type_from_str('NUMERIC(38)'))
'PDB_INT'
>>> str(pdb_type_from_str('NUMERIC'))
'PDB_INT'
>>> str(pdb_type_from_str('VARCHAR(32)'))
'PDB_STRING'
>>> str(pdb_type_from_str('Integer'))
'PDB_INT'
>>> str(pdb_type_from_str('String'))
'PDB_STRING'
>>> str(pdb_type_from_str('@String'))
Traceback (most recent call last):
...
ValueError: string "@String" is not a known a column type
- primary_keys(table_obj)
- returns a list of primary keys for the table.
Note: the column names returned are lower-case.
Here is an example:
We first connect to a sqlite database in memory:
>>> (meta,conn)=connect_memory()
We now create table objects in sqlalchemy:
>>> tbl = make_test_table(meta,"mytable" ,("id:int:primary","name:str"))
>>> tbl2= make_test_table(meta,"mytable2",("id:int:primary",
... "id2:int:primary","name:str"))
primary_keys returns a list of strings, representing
all primary keys of the table:
>>> primary_keys(tbl)
['id']
>>> primary_keys(tbl2)
['id', 'id2']
- print_query(conn, query_text, format=EnumValue(<bii_scripts.p_enum.Enum object at 0x7f1dfb83f450>, 2, 'TABLE'), order_by=[], where_part='', do_print=True)
- pretty-prints a query.
parameters:
conn -- a sqlalchemy connection object
query_text -- the SQL query as a string
format -- This enumeration type determines the output format,
Format.PLAIN : simple row format with repr() strings
Format.TABLE_SPC: table with spaces
Format.TABLE : table with column lines
Format.CSV : comma-separated values,
commas within fields are escaped with
"", all in fields "" are doubled
order_by -- a list of column names by which the results are ordered
where_part -- the "WHERE" part of the sql query, this can be used
to filter the results
do_print -- if True, print to the screen, otherwise
return a list of lines.
returns:
a list of strings or None, depending on the value of do_print
Here are some examples:
We first connect to a sqlite database in memory:
>>> (meta,conn)=connect_memory()
We now create now table objects in sqlalchemy:
>>> tbl= make_test_table(meta,"mytable",("id:int:primary","name:str"))
>>> set_table(tbl, ((1,"cd"),(2,"ab")))
>>> print_query(conn,"select * from mytable",Format.PLAIN)
('id', 'name')
(1, 'cd')
(2, 'ab')
>>> print_query(conn,"select * from mytable",Format.TABLE_SPC)
id name
1 cd
2 ab
>>> print_query(conn,"select * from mytable",Format.TABLE)
id | name
---+-----
1 | cd
2 | ab
>>> print_query(conn,"select * from mytable",Format.CSV)
id,name
1,cd
2,ab
>>> print_query(conn,"select id as id2, name as myname from mytable",
... Format.TABLE)
id2 | myname
----+-------
1 | cd
2 | ab
- print_table(table_object, format=EnumValue(<bii_scripts.p_enum.Enum object at 0x7f1dfb83f450>, 2, 'TABLE'), order_by=[], where_part='', do_print=True)
- pretty-prints a table.
parameters:
table_object -- the sqlalchemy table object
format -- This enumeration type determines the output format,
Format.PLAIN : simple row format with repr() strings
Format.TABLE_SPC: table with spaces
Format.TABLE : table with column lines
Format.CSV : comma-separated values,
commas within fields are escaped with
"", all in fields "" are doubled
order_by -- a list of column names by which the results are ordered
where_part -- the "WHERE" part of the sql query, this can be used
to filter the results
do_print -- if True, print to the screen, otherwise
return a list of lines.
returns:
a list of strings or None, depending on the value of do_print
Here are some examples:
We first connect to a sqlite database in memory:
>>> (meta,conn)=connect_memory()
We now create now table objects in sqlalchemy:
>>> tbl= make_test_table(meta,"mytable",("id:int:primary","name:str"))
>>> set_table(tbl, ((1,"cd"),(2,"ab")))
>>> print_table(tbl, Format.PLAIN)
('id', 'name')
(1, 'cd')
(2, 'ab')
>>> print_table(tbl, Format.TABLE_SPC)
id name
1 cd
2 ab
>>> print_table(tbl, Format.TABLE)
id | name
---+-----
1 | cd
2 | ab
>>> print_table(tbl, Format.CSV)
id,name
1,cd
2,ab
>>> print_table(tbl,Format.PLAIN,do_print=False)
["('id', 'name')", "(1, 'cd')", "(2, 'ab')"]
>>> print_table(tbl,Format.TABLE_SPC,do_print=False)
['id name', '1 cd ', '2 ab ']
>>> print_table(tbl,Format.TABLE,do_print=False)
['id | name', '---+-----', '1 | cd ', '2 | ab ']
>>> print_table(tbl,Format.CSV,do_print=False)
['id,name', '1,cd', '2,ab']
- set_table(table, rows)
- quickly add some rows to a table.
Here is an example:
>>> (meta,conn)=connect_memory()
>>> tbl= make_test_table(meta,"mytable",("id:int:primary","name:str"))
>>> set_table(tbl, ((1,"first"),(2,"second")))
>>> print_table(tbl,Format.TABLE)
id | name
---+-------
1 | first
2 | second
- table_object(table_name, metadata, schema=None)
- returns a table object.
This function returns a table object associated with
the given metadata. If the metadata object is bound to
a database engine, and a table of the given name exists,
the properties of that table are queried from the database.
parameters:
table_name -- the name of the table
metadata -- the metadata object, to which the table
will be connected.
schema -- if this parameter is given, this schema name will be
used to open the table.
returns:
a sqlalchemy table object
Here is an example:
We first connect to a sqlite database in memory:
>>> (meta,conn)=connect_memory()
We now create a table object in sqlalchemy:
>>> tbl= make_test_table(meta,"mytable",("id:int","name:str"))
Now we create a second table object with table_object() that accesses
the table we just created:
>>> rbk_tbl=table_object("mytable",meta)
>>> print repr(rbk_tbl).replace(",",",\n")
Table('mytable',
MetaData(Engine(sqlite:///:memory:)),
Column('id',
Integer(),
table=<mytable>),
Column('name',
String(length=None,
convert_unicode=False,
assert_unicode=None,
unicode_error=None,
_warn_on_bytestring=False),
table=<mytable>),
schema=None)
- update_table(source, dest, column_mapping=None, do_deletes=False)
- copies data from source to dest.
This means that all columns in source must be present
in dest and have a comparible type and that the primary
key is the same.
parameters:
source -- the source table object
dest -- the source table object
column_mapping -- a dictionary mapping source columns to
dest-columns. Destination columns that are
not present are set to later on set
to "None". If this parameter is not given,
it is expected that all columns in source
must be matched to columns of the same name
in dest.
do_deletes -- delete rows that are not present in source
from dest
We first connect to a sqlite database in memory:
>>> (meta,conn)=connect_memory()
We now create now table objects in sqlalchemy:
>>> tbl= make_test_table(meta,"mytable" ,("id:int:primary",
... "loc:int:primary",
... "name:unicode"))
>>> tbl2=make_test_table(meta,"mytable2",("my-id:int:primary",
... "my-loc:int:primary",
... "my-name:unicode",
... "my-other:unicode"))
>>> set_table(tbl,((1,1,u"1-1"),(1,2,u"1-2"),(2,2,u"2-2")))
>>> set_table(tbl2,((1,1,u"xx",u"a"),(1,2,u"1-2",u"b"),(2,3,u"2-3",u"c")))
This is the content of the two tables:
>>> print_table(tbl, Format.PLAIN)
('id', 'loc', 'name')
(1, 1, '1-1')
(1, 2, '1-2')
(2, 2, '2-2')
>>> print_table(tbl2, Format.PLAIN)
('my-id', 'my-loc', 'my-name', 'my-other')
(1, 1, 'xx', 'a')
(1, 2, '1-2', 'b')
(2, 3, '2-3', 'c')
now we define a map mapping columns from tbl to tbl2:
>>> column_mapping= pdict.OneToOne({"id":"my-id","loc":"my-loc","name":"my-name"})
now we update tbl2 without delete:
>>> update_table(tbl,tbl2,column_mapping)
>>> print_table(tbl2, Format.PLAIN)
('my-id', 'my-loc', 'my-name', 'my-other')
(1, 1, '1-1', 'a')
(1, 2, '1-2', 'b')
(2, 2, '2-2', None)
(2, 3, '2-3', 'c')
now we reset tbl2 to it's previous state and update with delete:
>>> result= tbl2.delete().execute()
>>> set_table(tbl2,((1,1,u"xx",u"a"),(1,2,u"1-2",u"b"),(2,3,u"2-3",u"c")))
>>> print_table(tbl2, Format.PLAIN)
('my-id', 'my-loc', 'my-name', 'my-other')
(1, 1, 'xx', 'a')
(1, 2, '1-2', 'b')
(2, 3, '2-3', 'c')
>>> update_table(tbl,tbl2,column_mapping,do_deletes=True)
>>> print_table(tbl, Format.PLAIN)
('id', 'loc', 'name')
(1, 1, '1-1')
(1, 2, '1-2')
(2, 2, '2-2')
>>> print_table(tbl2, Format.PLAIN)
('my-id', 'my-loc', 'my-name', 'my-other')
(1, 1, '1-1', 'a')
(1, 2, '1-2', 'b')
(2, 2, '2-2', None)
|