Пример #1
0
 def CreateAccessDatabase(self, fname):
     pypyodbc.win_create_mdb(fname)
     connection = pypyodbc.win_connect_mdb(fname)
     connection.cursor().execute(
         'CREATE TABLE t1 (id COUNTER PRIMARY KEY, name CHAR(25));').commit(
         )
     connection.close()
Пример #2
0
def create_db(path_to_directory):
    db_path = path_to_directory + '/DB.mdb'
    if os.path.exists(db_path):
        os.remove(db_path)
    pypyodbc.win_create_mdb(db_path)
    db = pypyodbc.win_connect_mdb(db_path) 
    return db
Пример #3
0
def write_ms_access_file(savFilename, mdbFilename=None, overwrite=True):
    """Write the actual MS Access file"""
    if not sys.platform.startswith("win"):
        raise EnvironmentError("Sorry, Windows only")
    if not mdbFilename:
        mdbFilename = os.path.splitext(savFilename)[0] + ".mdb"
        mdbFilename = mdbFilename.replace(" ", "_")
    if os.path.exists(mdbFilename) and overwrite:
        os.remove(mdbFilename)
    pypyodbc.lowercase = False
    create_table = sql_create_table(savFilename)
    insert_table = sql_insert_template(savFilename)
    pypyodbc.win_create_mdb(mdbFilename)

    #cnx = pyodbc.connect("DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=B:\\spss_python\\someFile.mdb", autocommit=True)
    #cnx = pyodbc.connect("DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=someFile.mdb", autocommit=True)
    conn_string = r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + os.getcwd(
    ) + "\\%s" % mdbFilename

    cnx = pyodbc.connect(conn_string, autocommit=True)

    try:

        cursor = cnx.cursor()
        cursor.execute(create_table)
        with savReaderWriter.SavReader(savFilename) as reader:
            for record in reader:
                cursor.execute(insert_table, tuple(record))
        cursor.commit()
    finally:
        cnx.close()
Пример #4
0
def tomdb(result,dest):
    dest = dest.replace(" ","_")
    pypyodbc.win_create_mdb(dest)   #creating mdb file 
    MDB = dest; DRV = '{Microsoft Access Driver (*.mdb)}'; PWD = ''     #setting up some requirements
    con = pyodbc.connect('DRIVER={};DBQ={};PWD={}'.format(DRV,MDB,PWD))     #making a connection to mdb
    cur = con.cursor()      #making a cursor to connection
    for tblname in result:
         s = "CREATE TABLE `" + tblname + "` (`" 
         for cols in result[tblname][0]:
             s += str(cols[0])+"` " + str(cols[1]) +",`"
         s= s[:-2] + ");"
         cur.execute(s)
         con.commit()
         for row in result[tblname][1]: 
            s = "INSERT INTO `" + tblname + "` VALUES ("
            for field in row:
                f = field[1:-1]
                f = f.replace("'"," ")
                field = field[0]+f+field[-1]
                field = BeautifulSoup(field).prettify().strip()
                s += field + ","
            s = s[:-1] + ");"
            cur.execute(s)
            con.commit()
    cur.close()
    con.close()
Пример #5
0
 def write_schema(self, mdb_file_path, **field_types):
     """
     :param mdb_file_path: The file path of the mdb database to create
     :param field_types: A mapping of table, field pairs to field types.
                         Allowable field types are text, float and int
                         If missing, primary key fields are text, and data
                         fields are float
     :return:
     """
     verify(dictish(field_types), "field_types should be a dict")
     for k,v in field_types.items() :
         verify(k in self.tic_dat_factory.all_tables, "%s isn't a table name"%k)
         verify(dictish(v), "Need a mapping from field names to field types for %s"%k)
         for fld,type_ in v.items() :
             verify(fld in self.tic_dat_factory.primary_key_fields.get(k[0], ()) +
                       self.tic_dat_factory.data_fields.get(k[0], ()),
                    "%s isn't a field name for table %s"%(fld, k))
             verify(type_ in ("text", "float", "int"),
                    "For table %s, field %s, %s isn't one of (text, float, int)"%(k, fld, type_))
     get_fld_type = lambda tbl, fld, default : field_types.get(tbl, {}).get(fld, default)
     if not os.path.exists(mdb_file_path) :
         verify(self.can_write_new_file, "Writing to a new file not enabled")
         py.win_create_mdb(mdb_file_path)
     with py.connect(_connection_str(mdb_file_path)) as con:
         for t in self.tic_dat_factory.all_tables:
             str = "Create TABLE %s (\n"%t
             strl = ["%s %s"%(f, get_fld_type(t, f, "text")) for
                     f in self.tic_dat_factory.primary_key_fields.get(t, ())] + \
                    ["%s %s"%(f, get_fld_type(t, f, "float"))
                     for f in self.tic_dat_factory.data_fields.get(t, ())]
             if self.tic_dat_factory.primary_key_fields.get(t) :
                 strl.append("PRIMARY KEY(%s)"%",".join
                     (self.tic_dat_factory.primary_key_fields[t]))
             str += ",\n".join(strl) +  "\n);"
             con.cursor().execute(str).commit()
Пример #6
0
 def get_connection(self):
     """Gets the db connection."""
     if current_platform != "windows":
         raise Exception("MS Access can only be used in Windows.")
     import pypyodbc as dbapi
     self.get_input()
     if not os.path.exists(self.opts['file']) and self.opts['file'].endswith('.mdb'):
         dbapi.win_create_mdb(self.opts['file'])
     connection_string = ("DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" +
                          os.path.abspath(self.opts["file"]).replace("/", "//") + ";")
     return dbapi.connect(connection_string, autocommit=False)
Пример #7
0
def createDatabase():
    """
    Crée la bdd, les tables et vide les tables si la bdd existe déjà
    """
    file = getConfig("General", "home_dir") + "woocommerce.mdb"
    if (os.path.exists(file)):
        connection = pypyodbc.win_connect_mdb(file)
        #vide la bdd
        cur = connection.cursor()
        cur.execute("DELETE FROM woocommerce_produit").commit()
        cur.execute("DELETE FROM woocommerce_meta").commit()
        cur.execute("DELETE FROM woocommerce_commande").commit()
        cur.close()
        return connection
    else:
        pypyodbc.win_create_mdb(file)
        connection = pypyodbc.win_connect_mdb(file)
        #NumCommande est l'id de la commande
        fields = '''
        Id_Commande LONG,
        NumCommande LONG,
        StatutCommande VARCHAR(20),
        Date_creation DATETIME,  
        Prenom VARCHAR(25),
        Nom VARCHAR(25),
        Adresse VARCHAR(150),
        CodePostal VARCHAR(5),
        Ville VARCHAR(50),	
        Tel VARCHAR(20),
        Email VARCHAR(50),
        Type_Paiement VARCHAR(30),
        Date_Paiement DATETIME,
        Date_Termine DATETIME,
        Commentaire  VARCHAR(200)
        '''
        connection.cursor().execute('CREATE TABLE woocommerce_commande (%s);' %
                                    fields).commit()

        fields = '''
        Id_Produit INTEGER,
        Quantite BYTE,
        Id_Commande LONG
        '''
        connection.cursor().execute('CREATE TABLE woocommerce_produit (%s);' %
                                    fields).commit()

        fields = '''
        key_meta VARCHAR(30),
        valeur_meta VARCHAR(30),        
        Id_Commande LONG
        '''
        connection.cursor().execute('CREATE TABLE woocommerce_meta (%s);' %
                                    fields).commit()
        return connection
Пример #8
0
 def get_connection(self):
     """Gets the db connection."""
     if current_platform != "windows":
         raise Exception("MS Access can only be used in Windows.")
     import pypyodbc as dbapi
     self.get_input()
     if not os.path.exists(self.opts['file']) and self.opts['file'].endswith('.mdb'):
         dbapi.win_create_mdb(self.opts['file'])
     connection_string = ("DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" +
                          os.path.abspath(self.opts["file"]).replace("/", "//") + ";")
     return dbapi.connect(connection_string, autocommit=False)
Пример #9
0
    def get_connection(self):
        """Gets the db connection."""
        current_platform = platform.system().lower()
        if current_platform != "windows":
            raise Exception("MS Access can only be used in Windows.")
        import pypyodbc as dbapi

        self.get_input()
        file_name = self.opts["file"]
        file_dir = self.opts["data_dir"]
        ms_file = os.path.join(file_dir, file_name)

        if not os.path.exists(ms_file) and ms_file.endswith('.mdb'):
            dbapi.win_create_mdb(ms_file)
        connection_string = ("DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" +
                             os.path.abspath(ms_file).replace("/", "//") + ";")
        return dbapi.connect(connection_string, autocommit=False)
Пример #10
0
def db_connect(**kwargs):
    '''
        Takes DSN='connection name' or MDB='path to access file'
        returns a connection to the specified database.
        throws Keyerror if you provide the wrong thing.
        Throws pypyodbc connect error if connection string is not valid.
    '''
    if 'DSN' in kwargs:
        return db.connect('DSN=' + kwargs['DSN'], autocommit=True)
    if 'MDB' in kwargs:
        try:
            return db.win_connect_mdb(kwargs['MDB'])
        except:
            pass
        #try creating it then connecting
        db.win_create_mdb(kwargs['MDB'])
        return db.win_connect_mdb(kwargs['MDB'])
    else:
        raise KeyError('Provide either a DNS keyword or MDB keyword')
Пример #11
0
def create_mdb(fileName):
    pypyodbc.win_create_mdb(fileName)
    conn = pypyodbc.connect('Driver={Microsoft Access Driver (*.mdb)};DBQ=' +
                            fileName)
    cur = conn.cursor()

    cur.execute(
        '''CREATE TABLE GEOMETRY (SEAT Number PRIMARY KEY,DETECTDATE Date,ST VARCHAR(30),
SETLOC VARCHAR(30),KM Number,SPEED Number,HEIGHT Number,STAGGER Number,HEIGHTDIFF Number,DISTANCE Number);'''
    )
    cur.commit()

    cur.execute(
        '''INSERT INTO GEOMETRY(SEAT,DETECTDATE,ST,SETLOC,KM,SPEED,HEIGHT,STAGGER,HEIGHTDIFF,DISTANCE)
VALUES(?,?,?,?,?,?,?,?,?,?)''',
        (11, '2015-01-11 15:38:00', 'bj11', 'zt11', 12, 13, 14, 15, 16, 18))
    cur.commit()

    conn.close()
Пример #12
0
def db_connect(**kwargs):
    '''
        Takes DSN='connection name' or MDB='path to access file'
        returns a connection to the specified database.
        throws Keyerror if you provide the wrong thing.
        Throws pypyodbc connect error if connection string is not valid.
    '''
    if 'DSN' in kwargs:
        return db.connect('DSN=' + kwargs['DSN'], autocommit=True)
    if 'MDB' in kwargs:
        try:
            return db.win_connect_mdb(kwargs['MDB'])
        except:
            pass
        #try creating it then connecting
        db.win_create_mdb(kwargs['MDB'])
        return db.win_connect_mdb(kwargs['MDB'])
    else:
        raise KeyError('Provide either a DNS keyword or MDB keyword')
Пример #13
0
def database_create(DBfile):
    try:
        if os.path.exists(DBfile) == False:
            pypyodbc.win_create_mdb(DBfile)
            conn = pypyodbc.connect(
                r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" +
                DBfile + ";Uid=;Pwd=;",
                charset="utf8")
            cur = conn.cursor()
            sql1 = '''CREATE TABLE ''' + "excelparams" + '''(\
            ID Text PRIMARY KEY,\
            username Text,\
            function_id Text,\
            function_name Text,\
            func_id Text,\
            style_type Text,\
            params Text,\
            paramstext Text,\
            periodicity Int,\
            periods Text,\
            beginTime DATETIME,\
            function_url Text,\
            paramsurl Text,\
            filename Text\
            );'''
            sql2 = '''CREATE TABLE ''' + "tasklist" + '''(\
            ID Text,\
            execute_date Int ,\
            execute_flag Int,\
            error_flag Int,\
            PRIMARY KEY(ID,execute_date)
            );'''
            cur.execute(sql1)
            cur.execute(sql2)
            cur.commit()
            conn.close()
    except Exception, e:
        print e
Пример #14
0
def write_ms_access_file(savFilename, mdbFilename=None, overwrite=True):
    """Write the actual MS Access file"""
    if not sys.platform.startswith("win"):
        raise EnvironmentError("Sorry, Windows only")
    if not mdbFilename:
        mdbFilename = os.path.splitext(savFilename)[0] + ".mdb"
        mdbFilename = mdbFilename.replace(" ", "_")
    if os.path.exists(mdbFilename) and overwrite:
        os.remove(mdbFilename)

    create_table = sql_create_table(savFilename)
    insert_table = sql_insert_template(savFilename)
    pypyodbc.win_create_mdb(mdbFilename)
    try:
        conn_string = 'Driver={Microsoft Access Driver (*.mdb)};DBQ=%s'
        connection = pypyodbc.connect(conn_string % mdbFilename)
        cursor = connection.cursor()
        cursor.execute(create_table)
        with savReaderWriter.SavReader(savFilename) as reader:
            for record in reader:
                cursor.execute(insert_table, tuple(record))
        cursor.commit()
    finally:
        connection.close()
Пример #15
0
def write_ms_access_file(savFilename, mdbFilename=None, overwrite=True):
    """Write the actual MS Access file"""
    if not sys.platform.startswith("win"):
        raise EnvironmentError("Sorry, Windows only")
    if not mdbFilename:
        mdbFilename = os.path.splitext(savFilename)[0] + ".mdb"
        mdbFilename = mdbFilename.replace(" ", "_")
    if os.path.exists(mdbFilename) and overwrite:
        os.remove(mdbFilename)

    create_table = sql_create_table(savFilename)
    insert_table = sql_insert_template(savFilename)
    pypyodbc.win_create_mdb(mdbFilename)
    try:
        conn_string = 'Driver={Microsoft Access Driver (*.mdb)};DBQ=%s'
        connection = pypyodbc.connect(conn_string % mdbFilename)
        cursor = connection.cursor()
        cursor.execute(create_table)
        with savReaderWriter.SavReader(savFilename) as reader:
            for record in reader:
                cursor.execute(insert_table, tuple(record))
        cursor.commit()
    finally:
        connection.close()
Пример #16
0
import pypyodbc
from array import *

file_Name = "ODBC;DSN=QuickBooks Data;SERVER=QODBC"

pypyodbc.win_create_mdb(file_Name)

conn = pypyodbc.win_connect_mdb(file_Name) 
cur = conn.cursor()

cur.execute(u"sp_report ARAgingSummary show Current_Title, Amount_Title, Text, Label, Current, Amount parameters DateMacro = 'ThisWeek', AgingAsOf = 'ThisWeek';")
results = cur.fetchall()
print results

# cur.execute(u"CREATE TABLE User_Log (ID INTEGER PRIMARY KEY, Time_Stamp String, User String, Type String, Update String)")

# cur.execute(u"CREATE TABLE Customer (ID INTEGER PRIMARY KEY, TimeCreated Date, TimeModified Date, FullName String, IsActive String, Salutation String, FirstName String, LastName String, BillAddressAddr1 String, BillAddressAddr2 String, BillAddressAddr3 String, BillAddressAddr4 String, BillAddressCity String, BillAddressNote String, Phone String, AltPhone String, Fax String, Email String, Contact String, AltContact String, SalesRepRefFullName String, ResaleNumber Integer, CreditLimit Integer)") 

# cur.execute(u"CREATE TABLE Vendor (ID INTEGER PRIMARY KEY, Day String, Ticker String, Open Double, High Double, Low Double, Close Double)")

# cur.execute(u"CREATE TABLE Item (ID INTEGER PRIMARY KEY, Day String, Ticker String, Open Double, High Double, Low Double, Close Double)")

# cur.execute(u"CREATE TABLE Inventory (ID INTEGER PRIMARY KEY, Day String, Ticker String, Open Double, High Double, Low Double, Close Double)")

# cur.execute(u"CREATE TABLE AR_Summary (ID INTEGER PRIMARY KEY, Day String, Ticker String, Open Double, High Double, Low Double, Close Double)")

# cur.execute(u"CREATE TABLE AP_Summary (ID INTEGER PRIMARY KEY, Day String, Ticker String, Open Double, High Double, Low Double, Close Double)")

# cur.execute(u"CREATE TABLE Sales_Customer_Summary (ID INTEGER PRIMARY KEY, Day String, Ticker String, Open Double, High Double, Low Double, Close Double)")

# cur.execute(u"CREATE TABLE Sales_Item_Summary (ID INTEGER PRIMARY KEY, Low Double, Close Double)")
Пример #17
0
 def CreateAccessDatabase(self, fname):
     pypyodbc.win_create_mdb(fname)
     connection = pypyodbc.win_connect_mdb(fname)
     connection.cursor().execute('CREATE TABLE t1 (id COUNTER PRIMARY KEY, name CHAR(25));').commit()
     connection.close()
Пример #18
0
"""

Testing to see if possible to connect using odbc connection

pip install pypyodbc

cd Desktop/Python
python SQLconnect.py

"""
"""
Simply try pypyodbc: 
"""

import pypyodbc

pypyodbc.win_create_mdb('D:\database.mdb')

connection_string = 'Driver={Microsoft Access Driver (*.mdb)};DBQ=D:\database.mdb'

connection = pypyodbc.connect(connection_string)

SQL = 'CREATE TABLE saleout (id COUNTER PRIMARY KEY,product_name VARCHAR(25));'

connection.cursor().execute(SQL).commit()
Пример #19
0
 def write_schema(self, mdb_file_path, **field_types):
     """
     :param mdb_file_path: The file path of the mdb database to create
     :param field_types: Named arguments are table names. Argument values
                         are mapping of field name to field type.
                         Allowable field types are text, double and int
                         If missing, primary key fields are text, and data
                         fields are double
     :return:
     """
     verify(
         not self.tic_dat_factory.generic_tables,
         "generic_tables are not compatible with write_schema. " +
         "Use write_file instead.")
     _standard_verify(self.tic_dat_factory.generic_tables)
     verify(dictish(field_types), "field_types should be a dict")
     for k, v in field_types.items():
         verify(k in self.tic_dat_factory.all_tables,
                "%s isn't a table name" % k)
         verify(dictish(v),
                "Need a mapping from field names to field types for %s" % k)
         for fld, type_ in v.items():
             verify(
                 fld in self.tic_dat_factory.primary_key_fields.get(k, ()) +
                 self.tic_dat_factory.data_fields.get(k, ()),
                 "%s isn't a field name for table %s" % (fld, k))
             verify(
                 type_ in ("text", "double", "int"),
                 "For table %s, field %s, %s isn't one of (text, double, int)"
                 % (k, fld, type_))
     get_fld_type = lambda tbl, fld, default: field_types.get(tbl, {}).get(
         fld, default)
     if not os.path.exists(mdb_file_path):
         verify(
             mdb_file_path.endswith(".mdb")
             or mdb_file_path.endswith(".accdb"),
             "For file creation, specify either an .mdb or .accdb file name"
         )
         if mdb_file_path.endswith(".mdb"):
             verify(
                 py,
                 "pypyodbc needs to be installed to create a new .mdb file")
             verify(self.can_write_new_file,
                    "Creating a new file not enabled for this OS")
             py.win_create_mdb(mdb_file_path)
         else:
             blank_accdb = os.path.join(_code_dir(), "blank.accdb")
             verify(
                 os.path.exists(blank_accdb)
                 and os.path.isfile(blank_accdb),
                 "You need to run accdb_create_setup.py as a post pip install operation "
                 + "to configure writing to new .accdb files.")
             shutil.copy(blank_accdb, mdb_file_path)
     with _connect(_connection_str(mdb_file_path)) as con:
         for t in self.tic_dat_factory.all_tables:
             str = "Create TABLE [%s] (\n" % t
             strl = ["[%s] %s"%(f, get_fld_type(t, f, "text")) for
                     f in self.tic_dat_factory.primary_key_fields.get(t, ())] + \
                    ["[%s] %s"%(f, get_fld_type(t, f, "double"))
                     for f in self.tic_dat_factory.data_fields.get(t, ())]
             if self.tic_dat_factory.primary_key_fields.get(t):
                 strl.append("PRIMARY KEY(%s)" % ",".join(
                     _brackets(self.tic_dat_factory.primary_key_fields[t])))
             str += ",\n".join(strl) + "\n);"
             con.cursor().execute(str).commit()
Пример #20
0
#!/usr/bin/python
# tested on python 3.4
# author : Samy Kacem
# Only on PC!!!!!!
# Install first https://pypi.python.org/pypi/pypyodbc

import pypyodbc;

# create db
pypyodbc.win_create_mdb('C:\\base.accdb');

conn = pypyodbc.connect('Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\\base.accdb;Uid=Admin;Pwd=;');

#create table
cur = conn.cursor();

cur.execute('''CREATE TABLE users (
id AUTOINCREMENT PRIMARY KEY, 
lname VARCHAR(255),
fname VARCHAR(255),
email VARCHAR(255) ); ''') ;

cur.commit();

#add first data
cur.execute('''INSERT INTO users(fname,lname,email) VALUES(?,?,?)''',(u'John','Do','*****@*****.**'));

cur.commit();

# Don't forget to close the connection
conn.close();
Пример #21
0
    def run(self):
        dirname = os.getcwd()
        try:
            if os.stat("log.txt").st_size != 0:
                os.remove(os.path.join(dirname, 'log.txt'))  # CLEAN OLD LOG
        except PermissionError:
            pass
        except FileNotFoundError:
            pass
        logging.basicConfig(filename="log.txt", level=logging.INFO)

        def createConfig():
            config = configparser.RawConfigParser()
            config.optionxform = str
            config['GENERAL'] = {
                'ALTIUM_LIB_PATH': 'default',
                'ODBC_DRIVER': 'Microsoft Access Driver (*.mdb, *.accdb)',
                'OPEN_ALTIUM': 'false',
                'ALTIUM_PATH': 'C:\Program Files\Altium\AD19\X2.EXE',
                'GIT_CHECKOUT.': 'false',
                'GIT_PULL': 'false'
            }

            with open('config.ini', 'w') as configfile:
                config.write(configfile)

        def createSettings():
            settings = configparser.RawConfigParser()
            settings.optionxform = str

            settings['STRUCTURE'] = {'Antennas And RF Components': 'example'}

            settings['EXCEL'] = {
                'set_formulas_as_text':
                'true',
                'formlas_conflict':
                'Device,Family,Value,Color,Manufacturer 1 Part Number'
            }

            with open('settings.ini', 'w') as configfile:
                settings.write(configfile)

        config = configparser.RawConfigParser()
        config.optionxform = str

        settings = configparser.RawConfigParser()
        settings.optionxform = str

        try:
            if os.stat("config.ini").st_size == 0:
                createConfig()
        except PermissionError:
            pass
        except FileNotFoundError:
            createConfig()

        config.read('config.ini')

        try:
            if os.stat("settings.ini").st_size == 0:
                createSettings()
        except PermissionError:
            pass
        except FileNotFoundError:
            createSettings()

        settings.read('settings.ini')
        if config['GENERAL']['ALTIUM_LIB_PATH'] == 'default':
            libPath = os.getcwd()
        else:
            libPath = config['GENERAL']['ALTIUM_LIB_PATH']

        if config.getboolean('GENERAL', 'GIT_CHECKOUT.'):
            os.popen("git checkout .")

        if config.getboolean('GENERAL', 'GIT_PULL'):
            os.popen("git pull")

        def constructDB(name, tables):
            return {'name': name, 'tables': tables}

        noerror = 0
        line = list()
        databases = list()
        for key in settings['STRUCTURE']:
            names.append(key)
            databases.append(
                constructDB(key, settings['STRUCTURE'][key].split(",")))

        for i in range(0, len(names)):
            itemsSignals.append(activator())

        while not start:
            continue
        sleep(0.1)
        for i, database in enumerate(databases):
            itemsSignals[i].sig.emit()
            for table in database['tables']:

                try:
                    # TEXT FILE CLEAN
                    CSVfilepath = "CSV/" + table + ".csv"
                    with open(os.path.join(libPath, CSVfilepath),
                              'r') as reader, open(
                                  os.path.join(dirname, 'CSV/temp.csv'),
                                  'w') as tempwriter, open(
                                      os.path.join(dirname, 'CSV/Clean.csv'),
                                      'w') as writer:
                        read_csv = csv.reader(reader, delimiter=',')
                        tempwrite_csv = csv.writer(tempwriter,
                                                   lineterminator='\n')
                        write_csv = csv.writer(writer, lineterminator='\n')
                        for ReadedLine in read_csv:
                            try:
                                if len(ReadedLine[1]) > 0:
                                    del line[:]
                                    for word in ReadedLine:
                                        if settings.getboolean(
                                                'EXCEL',
                                                'set_formulas_as_text'):
                                            for conflict in settings['EXCEL'][
                                                    'formlas_conflict'].split(
                                                        ","):
                                                if word is None:
                                                    continue
                                                word = word.replace(
                                                    "'=" + conflict + "'",
                                                    "=" + conflict)
                                        line.append(word)
                                    write_csv.writerow(line)

                            except IndexError:
                                logging.error(f"EMPTY CSV: {table}.csv")
                                #print(f"     EMPTY CSV: {table}.csv")
                                noerror = 0
                                '''
                            except AttributeError:
                                print(f"     atribute")
                                noerror = 0
                                '''
                            else:
                                noerror = 1
                    if noerror:
                        reader.close()
                        tempwriter.close()
                        writer.close()
                        #shutil.move('CSV/temp.csv', CSVfilepath)

                        MDBfilepath = "DataBase/" + database['name'] + ".mdb"
                        access_path = os.path.join(libPath, MDBfilepath)

                        if not os.path.exists(
                                os.path.join(libPath, "DataBase/")):
                            os.makedirs(os.path.join(libPath, "DataBase/"))

                        if not os.path.exists(access_path):
                            pypyodbc.win_create_mdb(
                                access_path)  # CREATE MDB IF NOT EXIST
                            logging.info(
                                f"CREATING MDB: {database['name']}.mdb")
                            #print(f"CREATING MDB: {database['name']}.mdb")

                        # DATABASE CONNECTION
                        con = pyodbc.connect("DRIVER={" + config['GENERAL']['ODBC_DRIVER'] + "};" + \
                                            "DBQ=" + access_path + ";")
                        cur = con.cursor()

                        if not cur.tables(table=table,
                                          tableType='TABLE').fetchone():
                            strSQL = "CREATE TABLE " + table  #CREATE TABLE IF NOT EXIST
                            logging.info(
                                f"CREATING TABLE: {table} in {database['name']}.mdb"
                            )
                            #print(f"CREATING TABLE: {table} in {database['name']}.mdb")
                            cur.execute(strSQL)

                        # RUN QUERY
                        strSQL = "DROP TABLE " + table
                        logging.info(f"CONVERTING CSV: {table}.csv")
                        #print(f"CONVERTING CSV: {table}.csv")

                        cur.execute(strSQL)

                        strSQL = "SELECT * INTO " + table + " FROM [text;HDR=Yes;FMT=Delimited(,);" + \
                                "Database=" + os.path.join(dirname, 'CSV') + "].Clean.csv"
                        cur = con.cursor()
                        cur.execute(strSQL)
                        con.commit()

                        con.close()  # CLOSE CONNECTION
                        os.remove(os.path.join(
                            dirname, 'CSV/Clean.csv'))  # DELETE CLEAN TEMP
                except FileNotFoundError:
                    noerror = 0
                    try:
                        os.remove(os.path.join(
                            dirname, 'CSV/Clean.csv'))  # DELETE CLEAN TEMP
                    except PermissionError:
                        pass
                    except FileNotFoundError:
                        pass
                    logging.error(f"NOT FOUND CSV: {table}.csv")
                    #print(f" NOT FOUND CSV: {table}.csv")
                except pyodbc.InterfaceError:
                    noerror = 0
                    logging.error("ODBC DRIVER ERROR")
                    #print("ODBC DRIVER ERROR")
        try:
            os.remove(os.path.join(dirname,
                                   'CSV/temp.csv'))  # DELETE CLEAN TEMP
        except PermissionError:
            pass
        except FileNotFoundError:
            pass
        #print("DONE!")
        endSignal.sig.emit()

        if config.getboolean('GENERAL', 'OPEN_ALTIUM'):
            os.startfile(config['GENERAL']['ALTIUM_PATH'])

        sleep(0.700)
        sys.exit(0)
Пример #22
0
def createCiq2kMdb():
	pypyodbc.win_create_mdb('D:\\db.mdb')
Пример #23
0
#!/usr/bin/python
# tested on python 3.4
# author : Samy Kacem
# Only on PC!!!!!!
# Install first https://pypi.python.org/pypi/pypyodbc

import pypyodbc

# create db
pypyodbc.win_create_mdb('C:\\base.accdb')

conn = pypyodbc.connect(
    'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\\base.accdb;Uid=Admin;Pwd=;'
)

#create table
cur = conn.cursor()

cur.execute('''CREATE TABLE users (
id AUTOINCREMENT PRIMARY KEY, 
lname VARCHAR(255),
fname VARCHAR(255),
email VARCHAR(255) ); ''')

cur.commit()

#add first data
cur.execute('''INSERT INTO users(fname,lname,email) VALUES(?,?,?)''',
            (u'John', 'Do', '*****@*****.**'))

cur.commit()
Пример #24
0
'''
Created on 25 mag 2016

@author: admin
'''

import pypyodbc

pypyodbc.win_create_mdb('c:\tmp\database.mdb')

Пример #25
0
#rang = '[uvw]'
#rang = '[xyz]'
#rang = '[a-g]'
#rang = '[h-n]'
#rang = '[o-t]'
rang = '[(0-9)u-z]'

# Logging to file
logfile = open('f:\\Dropbox\\Documents\\Data_mining\\Projects\\21Sep13_'+rang+'.log', 'w')
log_observer = ScrapyFileLogObserver(logfile, level=logging.DEBUG)
log_observer.start()

dbfilepath = 'f:\\Dropbox\\Documents\\Data_mining\\Projects\\companies_'+rang+'.accdb'
if os.path.isfile(dbfilepath):
	os.remove(dbfilepath)
pypyodbc.win_create_mdb(dbfilepath)
conn =  pypyodbc.connect('Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=%s'%dbfilepath)
cur = conn.cursor()
cur.execute('''create table "Company Information" (
		CID counter primary key,
		"Company Name" text,
		Website text,
		Overview memo,
		Founded text,
		Employees text,
		Valuation text
		)''')
cur.execute('''create table Competitors (
		CID integer not null,
		Company text,
		Competitors text
def create_mdb(path, filename):
    if not os.path.exists(path):
        os.makedirs(path)
    return pypyodbc.win_create_mdb(path + filename)
Пример #27
0
 def create_db(self):
     db_path = os.getcwd() + '/DB_s_e.mdb'
     if (not os.path.exists(db_path)):
         pypyodbc.win_create_mdb(db_path)
     db = pypyodbc.win_connect_mdb(db_path)
     return db
Пример #28
0
 def __init__(self, db_name, password=""):
     if not os.path.exists(db_name):
         pypyodbc.win_create_mdb(db_name)
     self.db_name = db_name
     self.password = password
     self.connectDB()
Пример #29
0
__author__ = 'hooloongge'
#coding=utf-8
import pypyodbc
pypyodbc.win_create_mdb('salesdb.mdb')
conn = pypyodbc.connect('Driver={Microsoft Access Driver (*.mdb)};DBQ=salesdb.mdb')
cur = conn.cursor()

cur.execute('''CREATE TABLE saleout (
ID COUNTER PRIMARY KEY,
customer_name VARCHAR(25), 
product_name VARCHAR(30), 
price float, 
volume int,
sell_time datetime);''')

cur.commit()

cur.execute('''INSERT INTO saleout(customer_name,product_name,price,volume,sell_time)
VALUES(?,?,?,?,?)''',(u'江文','Huawei Ascend mate','5000.5',2,'2012-1-21'))

cur.commit()
cur.execute('''INSERT INTO saleout(customer_name,product_name,price,volume,sell_time)
VALUES(?,?,?,?,?)''',(u'杨天真','Apple IPhone 5','6000.1',1,'2012-1-21'))
cur.execute('''INSERT INTO saleout(customer_name,product_name,price,volume,sell_time)
VALUES(?,?,?,?,?)''',(u'郑现实','Huawei Ascend D2','5100.5',1,'2012-1-22'))
cur.execute('''INSERT INTO saleout(customer_name,product_name,price,volume,sell_time)
VALUES(?,?,?,?,?)''',(u'莫小闵','Huawei Ascend D2','5200.5',1,'2012-1-22'))
cur.execute('''INSERT INTO saleout(customer_name,product_name,price,volume,sell_time)
VALUES(?,?,?,?,?)''',(u'顾小白','Huawei Ascend mate','5000.5',1,'2012-1-22'))

cur.commit()
Пример #30
0
    print ('file: '+str(x))

    pypyodbc = __import__(sys.argv[1])
    #pypyodbc.pooling = False

    print ('Running with pypyodbc %s' %pypyodbc.version)
    fix = str(x%10)
    
    if test_mdb is True:
        if os.path.exists(temp_folder + u'PerformanceTest'+fix+'.mdb'):
             os.remove(temp_folder + u'PerformanceTest'+fix+'.mdb')
        if os.path.exists(temp_folder + u'PerformanceTest'+fix+'c.mdb'):
             os.remove(temp_folder + u'PerformanceTest'+fix+'c.mdb')
        if os.path.exists(temp_folder + u'PerformanceTest'+fix+'copy.mdb'):
             os.remove(temp_folder + u'PerformanceTest'+fix+'copy.mdb')
        win_create_mdb( temp_folder + u'PerformanceTest'+fix+'.mdb' )
    
    
        conn = pypyodbc.connect(u'Driver={Microsoft Access Driver (*.mdb)};DBQ='+temp_folder + u'PerformanceTest'+fix+'.mdb')
    else:
        conn = pypyodbc.connect(connection_string)
        
    print (conn.getinfo(pypyodbc.SQL_DRIVER_NAME))
    cur = conn.cursor()
    
    if test_mdb:
        cur.execute(u"""create table saleout (ID COUNTER PRIMARY KEY, customer_name varchar(255),
                                product_name varchar(255), 
                                price float, 
                                volume int,
                                sell_time datetime);""")
Пример #31
0
 #import pyodbc as pypyodbc
 if 'pyodbc' in sys.argv:
     import pyodbc as pypyodbc
     print ('Running with pyodbc %s' %pypyodbc.version)
 else:
     import pypyodbc as pypyodbc
     print ('Running with pypyodbc %s' %pypyodbc.version)
 t_begin = time.time()
 fix = str(x%10)
 if os.path.exists(u'D:\\pypyodbc_mdb_test\\YourMDBfilepath'+fix+'.mdb'):
      os.remove(u'D:\\pypyodbc_mdb_test\\YourMDBfilepath'+fix+'.mdb')
 if os.path.exists(u'D:\\pypyodbc_mdb_test\\YourMDBfilepath'+fix+'c.mdb'):
      os.remove(u'D:\\pypyodbc_mdb_test\\YourMDBfilepath'+fix+'c.mdb')
 if os.path.exists(u'D:\\pypyodbc_mdb_test\\YourMDBfilepath'+fix+'copy.mdb'):
      os.remove(u'D:\\pypyodbc_mdb_test\\YourMDBfilepath'+fix+'copy.mdb')
 win_create_mdb( u'D:\\pypyodbc_mdb_test\\YourMDBfilepath'+fix+'.mdb' )
 
 
 conn = pypyodbc.connect(u'Driver={Microsoft Access Driver (*.mdb)};DBQ=D:\\pypyodbc_mdb_test\\YourMDBfilepath'+fix+'.mdb')
 print (conn.getinfo(pypyodbc.SQL_DRIVER_NAME))
 cur = conn.cursor()
 cur.execute(u"""create table saleout (ID COUNTER PRIMARY KEY, customer_name varchar(255),
                         product_name varchar(255), 
                         price float, 
                         volume int,
                         sell_time datetime);""")
 
 conn.commit()
 for b in range(2500):
     cur.executemany(u'''INSERT INTO saleout(customer_name,product_name,price,volume,sell_time) 
     VALUES(?,?,?,?,?)''',      [(u'杨天真','Apple IPhone 5','5500.1',1,'2012-1-21'),
Пример #32
0
    print ('file: '+str(x))

    pypyodbc = __import__(sys.argv[1])
    #pypyodbc.pooling = False

    print ('Running with pypyodbc %s' %pypyodbc.version)
    fix = str(x%10)
    
    if test_mdb is True:
        if os.path.exists(temp_folder + u'PerformanceTest'+fix+'.mdb'):
             os.remove(temp_folder + u'PerformanceTest'+fix+'.mdb')
        if os.path.exists(temp_folder + u'PerformanceTest'+fix+'c.mdb'):
             os.remove(temp_folder + u'PerformanceTest'+fix+'c.mdb')
        if os.path.exists(temp_folder + u'PerformanceTest'+fix+'copy.mdb'):
             os.remove(temp_folder + u'PerformanceTest'+fix+'copy.mdb')
        win_create_mdb( temp_folder + u'PerformanceTest'+fix+'.mdb' )
    
    
        conn = pypyodbc.connect(u'Driver={Microsoft Access Driver (*.mdb)};DBQ='+temp_folder + u'PerformanceTest'+fix+'.mdb')
    else:
        conn = pypyodbc.connect(connection_string)
        
    print (conn.getinfo(pypyodbc.SQL_DRIVER_NAME))
    cur = conn.cursor()
    
    if test_mdb:
        cur.execute(u"""create table saleout (ID COUNTER PRIMARY KEY, customer_name varchar(255),
                                product_name varchar(255), 
                                price float, 
                                volume int,
                                sell_time datetime);""")