hosted by

From Bibus

Here's a python script to export a MySQL bibus database to a SQLite database file. I use it to create a portable version of my database for use on a notebook. It copies all tables including ref keys and queries.

tested with bibus 1.2/MySQL 5.0.18

# -*- coding: utf-8 -*-
import sys,os
import string
import MySQLdb
import pysqlite2.dbapi2 as sqlite

# change to match your mysql server account:
bibusdb = "bibus41"
mysqlhost = "myhost"
mysqluser = "root"
mysqlpasswd = "mypass"

# output SQLite file:
destfile = "sqlite_export.db"


bibiusSqliteTabs = """
BEGIN TRANSACTION;
CREATE TABLE bibquery (query_id INTEGER PRIMARY KEY,user TEXT NOT NULL ON CONFLICT REPLACE DEFAULT '',name TEXT NOT NULL ON CONFLICT REPLACE DEFAULT 'query',query TEXT NOT NULL ON CONFLICT REPLACE DEFAULT '');
CREATE TABLE bibref (Id INTEGER PRIMARY KEY,Identifier TEXT UNIQUE,BibliographicType INTEGER NOT NULL ON CONFLICT REPLACE DEFAULT 0,Address TEXT NOT NULL ON CONFLICT REPLACE DEFAULT '',Annote TEXT NOT NULL ON CONFLICT REPLACE DEFAULT '',Author TEXT NOT NULL ON CONFLICT REPLACE DEFAULT '',Booktitle TEXT NOT NULL ON CONFLICT REPLACE DEFAULT '',Chapter TEXT NOT NULL ON CONFLICT REPLACE DEFAULT '',Edition TEXT NOT NULL ON CONFLICT REPLACE DEFAULT '',Editor TEXT NOT NULL ON CONFLICT REPLACE DEFAULT '',Howpublished TEXT NOT NULL ON CONFLICT REPLACE DEFAULT '',Institution TEXT NOT NULL ON CONFLICT REPLACE DEFAULT '',Journal TEXT NOT NULL ON CONFLICT REPLACE DEFAULT '',Month TEXT NOT NULL ON CONFLICT REPLACE DEFAULT '',Note TEXT NOT NULL ON CONFLICT REPLACE DEFAULT '',Number TEXT NOT NULL ON CONFLICT REPLACE DEFAULT '',Organizations TEXT NOT NULL ON CONFLICT REPLACE DEFAULT '',Pages TEXT NOT NULL ON CONFLICT REPLACE DEFAULT '',Publisher TEXT NOT NULL ON CONFLICT REPLACE DEFAULT '',School TEXT NOT NULL ON CONFLICT REPLACE DEFAULT '',Series TEXT NOT NULL ON CONFLICT REPLACE DEFAULT '',Title TEXT NOT NULL ON CONFLICT REPLACE DEFAULT '',Report_Type TEXT NOT NULL ON CONFLICT REPLACE DEFAULT '',Volume TEXT NOT NULL ON CONFLICT REPLACE DEFAULT '',Year TEXT NOT NULL ON CONFLICT REPLACE DEFAULT '',URL TEXT NOT NULL ON CONFLICT REPLACE DEFAULT '',Custom1 TEXT NOT NULL ON CONFLICT REPLACE DEFAULT '',Custom2 TEXT NOT NULL ON CONFLICT REPLACE DEFAULT '',Custom3 TEXT NOT NULL ON CONFLICT REPLACE DEFAULT '',Custom4 TEXT NOT NULL ON CONFLICT REPLACE DEFAULT '',Custom5 TEXT NOT NULL ON CONFLICT REPLACE DEFAULT '',ISBN TEXT NOT NULL ON CONFLICT REPLACE DEFAULT '',Abstract TEXT NOT NULL ON CONFLICT REPLACE DEFAULT '');
CREATE TABLE bibrefKey (user TEXT NOT NULL ON CONFLICT REPLACE DEFAULT '',key_Id INTEGER PRIMARY KEY,parent INTEGER,key_name TEXT NOT NULL ON CONFLICT REPLACE DEFAULT 'newkey');
CREATE TABLE bibrefLink (key_Id INTEGER,ref_Id INTEGER,UNIQUE (key_Id,ref_Id));
COMMIT;
"""



print "connecting to MySQL db..."
try:
	conn1 = MySQLdb.connect(host = mysqlhost,user =mysqluser,passwd = mysqlpasswd,db = bibusdb,use_unicode=True)
except MySQLdb.Error, e:
       print "Error %d: %s" % (e.args[0], e.args[1])
       sys.exit (1)			
       
c1 = conn1.cursor()
c1.execute ("USE " + bibusdb)

print "connecting to SQlite db..."
conn2	= sqlite.connect(destfile)
c2 		= conn2.cursor()
c2.executescript(bibiusSqliteTabs)



print "querying bibref table..."
c1.execute("SELECT * FROM bibref  ORDER BY id")
res = c1.fetchall()


print "copying bibref table..."
query = u"INSERT INTO bibref VALUES("
for x in res[0] :
	query = query + '?,'	
query = query[0:-1] + ')'
	
for src in res :
	try:
		c2.execute(query,src)
	except:
		print "Error: " + query
		break
	



print "querying bibreflink table..."
c1.execute("SELECT * FROM bibreflink")
res = c1.fetchall()


print "copying bibreflink table..."
query = u"INSERT INTO bibreflink VALUES("
for x in res[0] :
	query = query + '?,'	
query = query[0:-1] + ')'

for src in res :
	
	try:
		c2.execute(query,src)
	except:
		print "Error: " + query
		break
	
	
	
print "querying bibrefkey table..."
c1.execute("SELECT * FROM bibrefkey")
res = c1.fetchall()


print "copying bibrefkey table..."
query = u"INSERT INTO bibrefkey VALUES("
for x in res[0] :
	query = query + '?,'	
query = query[0:-1] + ')'

for src in res :
	
	try:
		c2.execute(query,src)
	except:
		print "Error: " + query
		break
		


print "querying bibquery table..."
c1.execute("SELECT * FROM bibquery")
res = c1.fetchall()


print "copying bibquery table..."
query = u"INSERT INTO bibquery VALUES("
for x in res[0] :
	query = query + '?,'	
query = query[0:-1] + ')'

for src in res :
	
	try:
		c2.execute(query,src)
	except:
		print "Error: " + query
		break
	

print "commiting changes...."


#close connection:
c1.close()
conn1.close()
conn2.commit()
c2.close()
conn2.close()