Python – SQLAlchemy – Oracle

SQLAlchemy to ORM (relacyjny maper) baz danych dla pythona, konkurencyjny względem SQLObject.

Object-relational mapping (ORM, O/RM, and O/R mapping) in computer software is a programming technique for converting data between incompatible type systems in object-oriented programming languages. This creates, in effect, a “virtual object database” that can be used from within the programming language. There are both free and commercial packages available that perform object-relational mapping, although some programmers opt to create their own ORM tools...

Definicja przytoczona za – http://en.wikipedia.org/

 

Python – PostgreSQL Database Backup Script

Simple script to backup some databases from PostgreSQL on unix\linux.

#!/usr/bin/env python
import os
import time

username = 'root'
defaultdb = 'postgres'
port = '5433'
backupdir='/www/backup/'
date = time.strftime('%Y-%m-%d')

#GET DB NAMES
get_db_names="psql -U%s -d%s -p%s --tuples-only -c '\l' | awk -F\| '{ print $1 }' | grep -E -v '(template0|template1|^$)'" % (username, defaultdb, port)

#MAKE BACKUP OF SYSTEMGRANTS
os.popen("pg_dumpall -p%s -g|gzip -9 -c > %s/system.%s.gz" % (port, backupdir, date))

#MAKING DB BACKUP
for base in os.popen(get_db_names).readlines():
        base = base.strip()
        fulldir = backupdir + base
        if not os.path.exists(fulldir):
                os.mkdir(fulldir)
        filename = "%s/%s-%s.sql" % (fulldir, base, date)
        os.popen("nice -n 19 pg_dump -C -F c -U%s -p%s %s > %s" % (username, port, base, filename))

 

Export Oracle Database to CSV

#!/usr/bin/env python

import sys
import csv
import cx_Oracle

connection = raw_input("Enter Oracle DB connection (user/pass@database) : ")
orcl = cx_Oracle.connect(connection)
curs = orcl.cursor()

printHeader = True

sql = "select * from tab"
curs.execute(sql)

for row_data in curs:
 if not row_data[0].startswith('BIN$'):
 tableName = row_data[0]

 csv_file_dest = tableName + ".csv"
 outputFile = open(csv_file_dest,'w')
 output = csv.writer(outputFile, dialect='excel')
 sql = "select * from " + tableName
 curs2 = orcl.cursor()
 curs2.execute(sql)

 if printHeader:
 cols = []
 for col in curs2.description:
 cols.append(col[0])
 output.writerow(cols)

 for row_data in curs2:
 output.writerow(row_data)

 outputFile.close()

Pass

Test