Copy whole SQL Server database into JSON from Python

Question

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 there 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.

Marcelo Cantos · Accepted Answer · 2010-03-31 12:03:14Z

1

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.

answered Mar 31, 2010 at 12:03

Marcelo Cantos

187k40 gold badges338 silver badges366 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user35288 · Accepted Answer · 2010-03-31 12:19:27Z

1

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

answered Mar 31, 2010 at 12:19

user35288

Comments

Cade Roux · Accepted Answer · 2010-03-31 15:18:52Z

1

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

answered Mar 31, 2010 at 15:18

Cade Roux

90.1k42 gold badges186 silver badges268 bronze badges

Comments

Preet Sangha · Accepted Answer · 2010-03-31 12:17:48Z

0

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.

answered Mar 31, 2010 at 12:17

Preet Sangha

65.7k20 gold badges152 silver badges227 bronze badges

Collectives™ on Stack Overflow

Copy whole SQL Server database into JSON from Python

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related