What Is Twining?

Twining is an IronPython module designed to automate some of the common tasks one encounters when working with databases with language. What kinds of tasks?  How about exporting data from a table to a file?  Or copying data from one location to another?  Or backing up the database?

These kinds of tasks can all be expressed in a single, terse line of code.

cn = "my connection string"
database(cn).table("MyTable").copyto.csv("c:\\export.csv")

Twining leverages features of the Python language to make it simple to deal with some of the complexities of these tasks.  Suppose, for example, that a transformation needs to occur during a copy or export process.  Python lambda expressions make this simple parts of the language you use to express yourself.

cn = "my connection string"
mytransform = {"mycolumn": lambda x: x[::-1]}
database(cn).table("MyTable").transform(mytransform).copyto.csv("c:\\export.csv")

There are a lot of operations that have been automated with Twining. On the right side of this page you can see them in entirety.  If you think of something Twining could use, or would like to contribute, submit feedback.  My goal is to make Twining a community project; a place where we can all benefit from each other.

Unfortunately Twining only supports Microsoft SQL Server 2005 although this is a constraint of time, not technical implementation.
 

Complete Examples

#assume cn as the connection string for all samples
cn = "my connection string"

#Ping (is it up?)

# test a connection string
#success[0] has reference to true/false, success[1] has message (success or error)
success = database(cn).ping()

#Copy/Export Operations

# export data to a CSV file
database(cn).table("MyTable").copyto.csv("path")

# export data to a delimited file
database(cn).table("MyTable").copyto.delimited("\t", "c:\\foaf_no_trans.tsv")

# export data to an XML file with fields as attributes
database(cn).table("MyTable").copyto.xmlFieldsAsAttributes("path")

# export data to an XML file with fields as attributes
database(cn).table("MyTable").copyto.xmlFieldsAsElements("path")

# copy data to another database
cn2 = "my connection to new database"
database(cn).table("MyTable").copyto.database(cn2)

# Copy with transforms

# copying with transforms is simple; use a dictionary,
# set up field names and matching transform expressions
# use the transform after specifying source table
mytrans = {"Field1":"upper","Field2": lambda x: x.upper()}
database(cn).table("MyTable").transform(mytrans).copyto.csv("path")
database(cn).table("MyTable").transform(mytrans).copyto.xmlFieldsAsElements("path")

#Importing Data

# bring in data from a CSV file
database(cn).table("MyTable").importfrom.csv("path")

# sometimes you need to map columns on the import
# syntax is destination:source because you may want to have the same source
# use multiple times
colmap = {"DestinationField1":1, "DestinationField2":2, "DestinationField3":1}
database(cn).table("MyTable").mapcolumns(colmap).importfrom.csv("C:\\foo.csv")

#Backing up data

# back up to a .BAK file
database(cn).backupto("C:\\temp\\loungin.bak")

#Generate Scripts

# an insert script with the records from given table
database(cn).table("MyTable").copyto.script("C:\\temp\\my_insert.sql")