Author: johannes
Date: 2007-04-19 09:52:05 -0500 (Thu, 19 Apr 2007)
New Revision: 270
Added:
address/db-migration/
address/db-migration/align-csv.py
address/db-migration/dumps.sh
address/db-migration/imp-csv.py
Log:
Added migration script for database (mdb to sqlite3)
Added: address/db-migration/align-csv.py
===================================================================
--- address/db-migration/align-csv.py 2007-04-10 14:25:24 UTC (rev 269)
+++ address/db-migration/align-csv.py 2007-04-19 14:52:05 UTC (rev 270)
@@ -0,0 +1,35 @@
+#!/usr/bin/python
+# -*- encoding: utf-8 -*-
+
+import sys
+
+marker = '<<###EOR###>>'
+
+header = None
+last_line = ''
+rows = []
+
+for line in sys.stdin.readlines():
+ if header is None:
+ header = line.strip()
+ line = ''
+
+ if marker in line:
+ (lead, rest) = line.split(marker, 1)
+ last_line += lead
+ rows.append(last_line)
+
+ while marker in rest:
+ (komp, rest) = rest.split(marker, 1)
+ rows.append(komp)
+
+ last_line = rest
+ else:
+ last_line += line
+
+print header
+for ix, zeile in enumerate(rows):
+ text = zeile.replace('\r\n', '<NEWLINE>')
+ text = text.replace('\n', '<NEWLINE>')
+ text = text.replace('\r', '<NEWLINE>')
+ print text
Property changes on: address/db-migration/align-csv.py
___________________________________________________________________
Name: svn:executable
+ *
Added: address/db-migration/dumps.sh
===================================================================
--- address/db-migration/dumps.sh 2007-04-10 14:25:24 UTC (rev 269)
+++ address/db-migration/dumps.sh 2007-04-19 14:52:05 UTC (rev 270)
@@ -0,0 +1,15 @@
+#!/bin/bash
+
+dbname=promo.mdb
+EOR='<<###EOR###>>'
+
+echo "Anrede ..."
+mdb-export -R$EOR $dbname Tab_Anrede | ./align-csv.py > anrede.csv
+echo "Kategorie ..."
+mdb-export -R$EOR $dbname Tab_Kategorie | ./align-csv.py > kategorie.csv
+echo "Ansprechpartner ..."
+mdb-export -R$EOR $dbname Tab_ASP | ./align-csv.py > ansp.csv
+echo "Firma ..."
+mdb-export -R$EOR $dbname Tab_Firma | ./align-csv.py > firma.csv
+
+./imp-csv.py
Property changes on: address/db-migration/dumps.sh
___________________________________________________________________
Name: svn:executable
+ *
Added: address/db-migration/imp-csv.py
===================================================================
--- address/db-migration/imp-csv.py 2007-04-10 14:25:24 UTC (rev 269)
+++ address/db-migration/imp-csv.py 2007-04-19 14:52:05 UTC (rev 270)
@@ -0,0 +1,180 @@
+#!/usr/bin/python
+# -*- encoding: utf-8 -*-
+#
+# Import CSV into SQLite3
+
+import pysqlite2.dbapi2 as dbapi
+import csv
+
+# =============================================================================
+# Clear all rows from a given table
+# =============================================================================
+
+def clear_table(db, table):
+
+ cur = db.cursor()
+ cur.execute('DELETE FROM %s' % table)
+ cur.close()
+
+
+# =============================================================================
+# Import the salutaions
+# =============================================================================
+
+def salutations(db):
+
+ clear_table(db, 'salutation')
+ reader = csv.DictReader(open('anrede.csv'))
+
+ cur = db.cursor()
+
+ result = {}
+ for row in reader:
+ text = unicode(row['Anr1'], 'utf-8')
+ cur.execute('INSERT INTO salutation (text) VALUES (?)', [text])
+ result[text.upper()] = cur.lastrowid
+
+ cur.close()
+ return result
+
+
+# =============================================================================
+# Import the categories
+# =============================================================================
+
+def categories(db):
+
+ clear_table(db, 'category')
+ reader = csv.DictReader(open('kategorie.csv'))
+
+ cur = db.cursor()
+
+ result = {}
+ for row in reader:
+ text = unicode(row['Kategorie'], 'utf-8')
+ cur.execute('INSERT INTO category (text) VALUES (?)', [text])
+ result[text.upper()] = cur.lastrowid
+
+ cur.close()
+
+ return result
+
+
+# =============================================================================
+# Get the ID for a given category
+# =============================================================================
+
+def fetch_lookup(value, lookup):
+
+ result = lookup.get(value.upper())
+ return result
+
+
+# =============================================================================
+# Re-Format a memo text
+# =============================================================================
+
+def fetch_memo(value):
+
+ result = value.replace('<NEWLINE>', '\n')
+ return result
+
+
+# =============================================================================
+# Import companies
+# =============================================================================
+
+def company(db, category):
+
+ clear_table(db, 'company')
+
+ reader = csv.DictReader(open('firma.csv'))
+ cur = db.cursor()
+
+ for row in reader:
+ cmd = 'INSERT INTO company (id, matchcode, name1, name2, street, ' \
+ ' country, zip, city, vatid, compreg, phone, fax, web, ' \
+ ' email, customer, bwcustomer, bwsupplier, shipment, ' \
+ ' category, memo, quicksearch) VALUES (?, ?, ?, ?, ?, ?, ' \
+ '?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
+
+ cur.execute(cmd, [row['ID_Firma'],
+ row['Matchcode'],
+ row['Firma 1'],
+ row['Firma 2'],
+ row['Straße'],
+ row['Land'],
+ row['PLZ'],
+ row['Stadt'],
+ row['UID'],
+ row['FNr'],
+ row['Tel_Haupt'],
+ row['Fax_zentr'],
+ row['INet'],
+ row['eMail'],
+ row['unsere_KdNr'],
+ row['Ihre_KdNr'],
+ row['Ihre_LieferantenNr'],
+ row['Versand'],
+ fetch_lookup(row['Kategorie'], category),
+ fetch_memo(row['Kund_Info']),
+ False])
+
+ cur.close()
+
+
+# =============================================================================
+# Import the contacts
+# =============================================================================
+
+def contact(db, salut):
+
+ clear_table(db, 'contact')
+
+ reader = csv.DictReader(open('ansp.csv'))
+ cur = db.cursor()
+
+ for row in reader:
+ cmd = 'INSERT INTO contact (id, company, salutation, title, ' \
+ ' firstname, surname, department, duty, phone1, phone2, ' \
+ ' fax, email, memo) ' \
+ ' VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
+
+ cur.execute(cmd, [row['ID_Person'],
+ row['ID_Firma'],
+ fetch_lookup(row['Anr'], salut),
+ row['Titel'],
+ row['Vorname'],
+ row['Zuname'],
+ row['Abt'],
+ row['Funkt'],
+ row['Asp_Tel1'],
+ row['Asp_Tel2'],
+ row['Fax'],
+ row['E-Mail'],
+ fetch_memo(row['Asp_Memo'])])
+
+ cur.close()
+
+
+
+
+# =============================================================================
+# Main program
+# =============================================================================
+
+db = dbapi.connect('address.db')
+
+print "Importing into salutation ..."
+salut = salutations(db)
+print "Importing into category ..."
+categ = categories(db)
+print "Importing into company ..."
+company(db, categ)
+print "Importing into contact ..."
+contact(db, salut)
+
+print "Committing ..."
+db.commit()
+
+print "Done"
Property changes on: address/db-migration/imp-csv.py
___________________________________________________________________
Name: svn:executable
+ *
lmpx.com only provides a reader for public news (NNTP) servers. It is not
affiliated with the servers or forums shown here and is not responsible for
the content of articles, which is written by their respective authors.