Copy whole SQL Server database into JSON from Python

I facing an atypical conversion problem. About a decade ago I coded up a large site in ASP. Over the years this turned into ASP.NET but kept the same database.

I've just re-done the site in Django and I've copied all the core data but before I cancel my account with the host, I need to make sure I've got a long-term backup of the data so if it turns out I'm missing something, I can copy it from a local copy.

To complicate matters, I no longer have Windows. I moved to Ubuntu on all my machines some time back. I could ask the host to send me a backup but having no access to a machine with MSSQL, I wouldn't be able to use that if I needed to.

So I'm looking for something that does:

db = {}
for table in database:
    db[table.name] = [row for row in table]

And then I could serialize db off somewhere for later consumption... But how do I do the table iteration? Is ther开发者_StackOverflow中文版e an easier way to do all of this? Can MSSQL do a cross-platform SQLDump (inc data)?

For previous MSSQL I've used pymssql but I don't know how to iterate the tables and copy rows (ideally with column headers so I can tell what the data is). I'm not looking for much code but I need a poke in the right direction.

Have a look at the sysobjects and syscolumns tables. Also try:

SELECT * FROM sysobjects WHERE name LIKE 'sys%'

to find any other metatables of interest. See here for more info on these tables and the newer SQL2005 counterparts.

I've liked the ADOdb python module when I've needed to connect to sql server from python. Here is a link to a simple tutorial/example: http://phplens.com/lens/adodb/adodb-py-docs.htm#tutorial

I know you said JSON, but it's very simple to generate a SQL script to do an entire dump in XML:

SELECT  REPLACE(REPLACE('SELECT * FROM {TABLE_SCHEMA}.{TABLE_NAME} FOR XML RAW', '{TABLE_SCHEMA}',
                        QUOTENAME(TABLE_SCHEMA)), '{TABLE_NAME}', QUOTENAME(TABLE_NAME))
FROM    INFORMATION_SCHEMA.TABLES
WHERE   TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA
       ,TABLE_NAME

As an aside to your coding approach - I'd say :

• set up a virtual machine with an eval on windows
• put sql server eval on it
• restore your data
• check it manually or automatically using the excellent db scripting tools from red-gate to script the data and the schema
• if fine then you have (a) a good backup and (b) a scripted output.