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()
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
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()
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()
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()
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)
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
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)
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)
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')
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()
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')
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
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()
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()
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)")
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()
"""
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()
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()
#!/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();
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)
def createCiq2kMdb():
pypyodbc.win_create_mdb('D:\\db.mdb')
#!/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()
'''
Created on 25 mag 2016
@author: admin
'''
import pypyodbc
pypyodbc.win_create_mdb('c:\tmp\database.mdb')
#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)
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
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()
__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()
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);""")
#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'),
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);""")
