Change database table in a workbook

[ebolz]

I've run the sample code to change the database in a workbook. I can change the Server name, the Database name, the User name, and the Port, but there does not seem to be any way to change the database table. This is what I can do. sourceWB.datasources[i].connections[0].server = 'myServer' sourceWB.datasources[i].connections[0].dbname = 'myDbName' sourceWB.datasources[i].connections[0].username = 'myUserName'

What I'd like to be able to do is specify a new database table as well. Of course this is a little more complicated if I'm joining multiple tables but all I need is to be able to iterate through the tables in each connection and change them. I don't need to be able to change the joins. I want to do something like this:

for i in range(0, len(sourceWB.datasources[0].connections[0].tables)):
      sourceWB.datasources[0].connections[0].tables[i] = 'tableName'

[KernpunktAnalytics]

If you're looking for a quick fix for single tables, you might try this snippet:

db_table = "my_new_db_table" # change this to your liking
tds = sourceWB.datasources[0] # change this too
relation = tds._datasourceTree.find(".//connection/relation") # find the respective node
relation.set('name', db_table) # change the name
relation.set('table', '[public].[{}]'.format(db_table)) # change the table

But I'd +1 the idea to package this functionality in a more accessible way somehow. Not overly familiar with the technical details of joins etc. in a TDS, so this is probably something for the core developers.

[ebolz]

Well, my workbooks have more than one datasource but just for fun I created a test workbook with just one datasource. But your solution did not work. I copied all your code and then changed the first line table name to 'ffd_18' and then ran the script. I got this in the workbook, But my workbook has a bunch of records where and are set to the table name and those did not get changed. And the resulting workbook did not open properly.

Your solution is close (thanks for that) but since my workbooks have more than one connection, I'll need to wait for a more complete solution.

[ebolz]

Just curious if there is any interest in adding this capability, namely to enhance the API so that one can change the name of a database table in a workbook? I would find this most useful. I have 7 different workbook templates (.twbx files) that each get duplicated about 50 times and the only difference is the database tables. When changes to the dashboards are needed, I manually edit the templates and duplicate them 50 times. That's easy, but then I must manually edit each of the 350 workbooks to specify the correct database tables. That's a pain in the $#&@% and prone to error.

[t8y8]

@ebolz this would be relatively simple to implement, I believe. Would you like to submit a PR?

We're starting to run against a problem where the connection model is getting quite large (but that can be discussed separately from your issue)

For anyone who may look to pick this up, all we have to do is copy what we do for dbname in the Connection class

[ebolz]

Not being familiar with GitHub I don't know what a PR is, much less how to create one. I did open this issue and I saw it was classified as a feature request. That's not enough? What should I do?

[t8y8]

I didn't mean to imply that your issue wasn't enough -- it's perfectly reasonable to not want to submit the code, I was just asking if you did want to (This is open source, we love community patches!).

The issue you filed is clear and includes an example of how you would like to use the code -- that's great, thank you.

There is interest in adding this and we'll take a look. I can't comment on when as most of the core team is pretty busy at the moment but we'll try and get to it in more detail as soon as possible.

[adambra]

This would definitely be useful. We would be able to use this to repoint a bunch of datasources from one schema to another within the same database.