mercredi 8 juillet 2015

Converting SQLite to MySQL script (Check if correct)

i just wrote a small script to port sqlite table to mysql but im realy not sure if i did right.

Im atleast sure, it works to the mysql-connection part, so atleast it already builds mysql connection.

from __future__ import with_statement
import sqlite3
import os
import pymysql

addonPath = "Path-To-SQLITe-Database"
addonPathOrginal = -"Path-To-Database.ini"

connection = sqlite3.connect(os.path.join(addonPath, "db.sqlite"))
#connection.row_factory = sqlite3.Row
connection.text_factory = str

cursor = connection.cursor()
sqliteexecute = cursor.execute

fetchall = cursor.fetchall





databaseinfo = os.path.join(addonPathOrginal, "database.ini")
d = {}
with open(databaseinfo, "r") as fs:
    for l in fs.readlines():
        l = l.strip(" ").strip("\n")
        obj = l.split("=")
        if len(obj) != 2: continue
        if obj[1] == "": continue
        d[obj[0]] = obj[1]
mysqlcon = pymysql.connect(**d)
mysqlcursor = mysqlcon.cursor()
execute = mysqlcursor.execute
execute("SET @@autocommit=1;")
execute("SET sql_notes = 1;")
execute("SET FOREIGN_KEY_CHECKS = 0;")
tablename = "jumptimes" # Convert Table 'jumptimes'
sqliteexecute("SELECT * FROM sqlite_master WHERE type='table' AND tbl_name='%s'" tablename)
#sqliteexecute("SELECT jumptimes FROM sqlite_master")
for x in fetchall():
    print "Table: %s" % tablename
    #if tablename == "sqlite_sequence": continue
    sqliteexecute("SELECT * FROM %s" % tablename)
    stuff = fetchall()
    if len(stuff) == 0: continue
    string = ("%s," * len(stuff[0]))[:-1]
    print "Table %s, %i elements, length of string %i" % (tablename, len(stuff),len(stuff[0]))
    for y in range(0, len(stuff), 5):
        mysqlcursor.executemany("REPLACE INTO " + tablename + " VALUES (" + string + ")", stuff[y:y+5])
connection.close()
execute("SET FOREIGN_KEY_CHECKS = 1;")
mysqlcon.commit()
mysqlcon.close()

database.ini

host=""
user=""
passwd=""
db=""

Aucun commentaire:

Enregistrer un commentaire