def __init__(self, db1, db2, u1="postgres", u2="postgres", p1=None, p2=None, h1="localhost", h2="localhost", sql=0): self._db1name = db1 self._db2name = db2 self._db1user = u1 self._db2user = u2 self._db1pass = p1 self._db2pass = p2 self._db1host = h1 self._db2host = h2 self._db1 = pg.DB(dbname=self._db1name, host=self._db1host, user=self._db1user) self._db2 = pg.DB(dbname=self._db2name, host=self._db2host, user=self._db2user) self._sql = sql
def db_connect(pwd): mysql.connector.connect(host='localhost', user='******', password='') # Noncompliant # ^^^^^^^^^^^ mysql.connector.connect(host='localhost', password='', user='******') # Noncompliant mysql.connector.connect('localhost', 'sonarsource', '') # Noncompliant # ^^ mysql.connector.connect('localhost', 'sonarsource', 'hello') # OK mysql.connector.connect('localhost', 'sonarsource', password='******') # OK mysql.connector.connect('localhost', 'sonarsource', password=pwd) # OK mysql.connector.connect('localhost', 'sonarsource', pwd) # OK mysql.connector.connect('localhost', 'sonarsource') # OK mysql.connector.connect('localhost', 'sonarsource', **dict) # OK mysql.connector.connection.MySQLConnection(host='localhost', user='******', password='') # Noncompliant pymysql.connect(host='localhost', user='******', password='') # Noncompliant pymysql.connections.Connection(host='localhost', user='******', password='') # Noncompliant psycopg2.connect(host='localhost', user='******', password='') # Noncompliant pgdb.connect(host='localhost', user='******', password='') # Noncompliant pg.DB(host='localhost', user='******', passwd='') # Noncompliant pg.DB('dbname', 'localhost', 5432, 'opt', 'postgres', '') # Noncompliant pg.connect(host='localhost', user='******', passwd='') # Noncompliant pg.DB(host='localhost', user='******', passwd=pwd) # Compliant pg.DB('dbname', 'localhost', 5432, 'opt', 'postgres', pwd) # Compliant
def InsertEmployeePerson(Name, MiddleName, LastName, IdentityDoc, IdAddress, Status, IdJob, IdStore, HireDate): connection = pg.DB(dbname='datawarehouse', host='127.0.0.1', port = 5432, user='******', passwd='root') encabezado = "SELECT InsertEmployee" cuerpo = str(Name) , str(MiddleName), str(LastName) , str(IdentityDoc),IdAddress, Status,IdJob,IdStore,str(HireDate) final = encabezado + str(cuerpo) + ";" print( connection.query(str(final))) connection.close() connection = pg.DB(dbname='datawarehouse', host='127.0.0.1', port = 5432, user='******', passwd='root') data = connection.query("SELECT IdPerson FROM Person ORDER BY IdPerson DESC LIMIT 1;") connection = mysql.connector.connect(host='localhost', database= db + str(IdStore), user='******', password='******') cursor = connection.cursor() query = "INSERT INTO Person VALUES " query += str((data[0][0], Name, MiddleName, LastName, IdentityDoc, IdAddress)) + ";" print(query) cursor.execute(query) connection.commit() connection.close()
def setUp(self): # Call superclass setUp in case this does something in the future dbapi20.DatabaseAPI20Test.setUp(self) try: con = self._connect() con.close() except pgdb.Error: # try to create a missing database import pg try: # first try to log in as superuser db = pg.DB('postgres', dbhost or None, dbport or -1, user='******') except Exception: # then try to log in as current user db = pg.DB('postgres', dbhost or None, dbport or -1) db.query('create database ' + dbname)
def parse_topics(self): self.db = pg.DB(dbname=self.config['db_name'], host=self.config['db_host'], port=self.config['db_port'], user=self.config['db_user'], passwd=self.config['db_password']) self.db.begin() for i, topic_id in enumerate(self.get_next_topic_id_to_parse()): print('Parsing topic ' + str(topic_id)) post_pages = self.get_post_pages(topic_id) posts = self.parse_topic(post_pages) if not posts: print('Only empty posts in topic, mark it as invalid') self.mark_topic_as_invalid(topic_id) else: posts = self.add_topic_id_post_numbers(topic_id, posts) self.save_posts(posts) if not (i + 1) % self.config['batch_size']: print('Parsed batch of topics: commit') self.db.commit() self.db.begin() self.db.close()
def main(arguments): 'Main entry point' parser = argparse.ArgumentParser(description=''' Extracts test run data into csv files. Saves runs to run-N.csv. The default behavior is to only extract the most recent run. ''') parser.add_argument('runs', metavar='N', type=int, nargs='*', help='A run to extract to csv. Saved as run-N.csv') args = parser.parse_args(args=arguments) pgdb = pg.DB(dbname='qfp', host='localhost', user='******', passwd='qfp123') runs = args.runs if len(runs) == 0: query_runs = pgdb.query('select index from runs;') idx = query_runs.dictresult()[-1]['index'] runs.append(idx) for run in runs: filename = 'run-{0:02}.csv'.format(run) sys.stdout.write('writing ' + filename + ' ...') sys.stdout.flush() query_tests = pgdb.query('select * from tests where run = $1;', run) query_results_to_file(filename, query_tests) sys.stdout.write(' done\n') sys.stdout.flush()
def scrape_topics(config, subforum_id, first_page, last_page): db = pg.DB(dbname=config['db_name'], host=config['db_host'], port=config['db_port'], user=config['db_user'], passwd=config['db_password']) subforum_name = get_subforum_name(db, subforum_id) for page in range(first_page, last_page + 1): topics = get_topics(page, subforum_name) new_topic_count = len(topics) if not topics: print('Reached end of {} on page {}'.format(subforum_name, page)) break for topic in topics: topic['subforum_id'] = subforum_id try: db.begin() db.insert('topics', topic) except pg.IntegrityError: new_topic_count -= 1 finally: db.commit() print('Jellied {} new topics from subforum id {} page {}'.format( new_topic_count, subforum_id, page + 1)) db.close()
def phoneApp(db_name): try: db = pg.DB(dbname=db_name) # db.debug = True # print '\nDatabase opened...' except: print 'No phonebook database exists!' exit(0) if 'public.phonebook' in db.get_tables(): # print 'Phonebook accessed!' phonebook(db) else: try: db.query(""" CREATE TABLE phonebook ( id serial PRIMARY KEY, name varchar NOT NULL UNIQUE, phone varchar, email varchar UNIQUE )""") print 'Phonebook created!' phonebook(db) except: print 'Phonebook table could not be created!' phonebook(db)
def deactivateAdmin(idEmployee): idStore = getAdminStore(idEmployee) newAdmin = getNonAdminsForStore(idStore, idEmployee) if newAdmin == -1: deactivateStore(idStore) else: connection = pg.DB(host='localhost', user='******', passwd='root', dbname='datawarehouse') query = "UPDATE Employee SET Status = 0 WHERE IdPerson = " + str( idEmployee) + ";" connection.query(query) query = "UPDATE Store SET IdAdmin = " + str(newAdmin) + " " query += "WHERE IdStore = " + str(idStore) + ";" connection.query(query) query = "UPDATE EmployeeJob SET IdJob = 1 WHERE IdPerson = " + str( newAdmin) + ";" connection.query(query) print(query) connection.close()
def activateItem(idItem): query = "UPDATE Item SET Status = 1 WHERE IdItem = " + str(idItem) + ";" connectionpsql = pg.DB(dbname='datawarehouse', host='127.0.0.1', port = 5432, user='******', passwd='root') connectionpsql.query(query) connectionpsql.close() storeList = getIdStoreList() for i in storeList: try: connection = mysql.connector.connect(host='localhost', database=db + str(i), user='******', password='******') cursor = connection.cursor() cursor.execute(query) connection.commit() connection.close() except: continue
def __init__(self): status = False maximumRetry = 3 count = 0 self.conn = None while count < maximumRetry and self.conn is None: try: count += 1 self.conn = pg.DB( host="localhost", user="******", passwd="PASSWORD", dbname="DBNAME", ) if self.conn is not None: status = True break except pg.InternalError: print("Trying to reconnect with the database") except Exception as err: print(err) if status: print("Connected with the database successfully.") result = self.conn.query("SELECT fname, lname FROM employee") for firstname, lastname in result.getresult(): print(firstname, lastname) self.conn.close() else: print("Connection with the database was Unsucessful!")
def fragState(idStore): connection = pg.DB(host='localhost', user='******', passwd='root', dbname='datawarehouse') data = connection.query("SELECT * FROM State;") connection.close() connection = mysql.connector.connect(host='localhost', database=db + str(idStore), user='******', password='******') cursor = connection.cursor() for line in data: query = "INSERT INTO State (IdState, IdCountry, Name) VALUES " query += str( line ) + ";" cursor.execute(query) connection.commit() connection.close()
def fragBrand(idStore): connection = pg.DB(host='localhost', user='******', passwd='root', dbname='testpsql') data = connection.query("SELECT * FROM Brand;") connection.close() connection = mysql.connector.connect(host='localhost', database=db + str(idStore), user='******', password='******') cursor = connection.cursor() for line in data: query = "INSERT INTO Brand VALUES " query += str( line ) + ";" cursor.execute(query) connection.commit() connection.close()
def getOwners(): db = pg.DB(host=DBHOST, user=DBUSER, passwd=DBPASS, dbname=DBNAME) query = db.query("SELECT * FROM owners order by yearstarted asc") result_list = query.namedresult() db.close() print result_list return result_list
def fragJob(idStore): connection = pg.DB(host='localhost', user='******', passwd='root', dbname='testpsql') data = connection.query("SELECT * FROM Job;") connection.close() connection = mysql.connector.connect(host='localhost', database=db + str(idStore), user='******', password='******') cursor = connection.cursor() for line in data: query = "INSERT INTO Job (IdJob, Job, Salary) VALUES " query += str( (line[0], line[1], line[2]) ) + ";" cursor.execute(query) connection.commit() connection.close()
def fragAddress(idStore): fragCountry(idStore) fragState(idStore) fragCity(idStore) connection = pg.DB(host='localhost', user='******', passwd='root', dbname='testpsql') data = connection.query("SELECT * FROM Address;") connection.close() connection = mysql.connector.connect(host='localhost', database=db + str(idStore), user='******', password='******') cursor = connection.cursor() for line in data: query = "INSERT INTO Address VALUES " query += str( line ) + ";" cursor.execute(query) connection.commit() connection.close()
def generateWarehouseRequest(): connection = pg.DB(dbname='testpsql', host='127.0.0.1', port = 5432, user='******', passwd='root') data = getItemsWarehouse() idRequest = connection.query("SELECT IdRequest FROM WarehouseRequest ORDER BY IdRequest DESC;") try: idRequest = idRequest[0][0] + 1 except: idRequest = 1 today = date.today() requestDate = today.strftime("%Y-%m-%d") connection.query("INSERT INTO WarehouseRequest VALUES " + str( (idRequest, requestDate) ) + ";") for i in data: query = "INSERT INTO WarehouseRequestItem VALUES " query += str( (idRequest, i, 1) ) + ";" connection.query(query) connection.close() restockWarehouse(data)
def fragPerson(idStore): connection = pg.DB(host='localhost', user='******', passwd='root', dbname='testpsql') data = connection.query("SELECT * FROM Person;") connection.close() connection = mysql.connector.connect(host='localhost', database=db + str(idStore), user='******', password='******') cursor = connection.cursor() for line in data: query = "INSERT INTO Person (IdPerson, FirstName, MiddleName, LastName, IdentityDoc, IdAddress) VALUES " if str(line[2]) == "None": query += "(" + str(line[0]) + ", '" + str(line[1]) + "', NULL, '" + str(line[3]) + "', '" query += str(line[4]) + "', " + str(line[5]) + ");" else: query += str( (line[0], line[1], line[2], line[3], line[4], line[5]) ) + ";" cursor.execute(query) connection.commit() connection.close()
def fragItems(idStore): fragCategory(idStore) fragBrand(idStore) connection = pg.DB(host='localhost', user='******', passwd='root', dbname='datawarehouse') data = connection.query("SELECT * FROM Item;") connection.close() connection = mysql.connector.connect(host='localhost', database=db + str(idStore), user='******', password='******') cursor = connection.cursor() for line in data: query = "INSERT INTO Item VALUES " query += str( line ) + ";" cursor.execute(query) connection.commit() connection.close()
def _connect_to_postgres(self): db = pg.DB(dbname=POSTGRES_DB, host=self._POSTGRES_HOST, port=POSTGRES_PORT, user=POSTGRES_USERNAME, passwd=POSTGRES_PASSWORD) return db
def insertWithCeroStock(idItem, idStore): connectionpsql = pg.DB(dbname='datawarehouse', host='127.0.0.1', port = 5432, user='******', passwd='root') query = "INSERT INTO ItemStore VALUES " query += str( (idStore,idItem,0) ) + ";" connectionpsql.query(query)
def InsertItem(idBrand, description, idCategory, price, stores): idItem = code = getNextItem() today = date.today() entryDate = today.strftime("%Y-%m-%d") query = "INSERT INTO Item VALUES " query += str( (idItem,code,idBrand,description,idCategory,price,1,entryDate) ) + ";" connectionpsql = pg.DB(dbname='datawarehouse', host='127.0.0.1', port = 5432, user='******', passwd='root') connectionpsql.query(query) connectionpsql.close() storeIds = getIdStoreList() for id in storeIds: try: insertItemStore(query, id) except: continue for id in stores: try: insertWithCeroStock(idItem, id) insertWithCeroStockToStore(idItem, id) except: continue insertWithCeroStockToWarehouse(idItem)
def isAdmin(idEmployee): query = "SELECT E.IdPerson FROM Employee E " query += "INNER JOIN EmployeeJob EJ ON EJ.IdPerson = E.IdPerson " query += "WHERE EJ.IdJob = 1 AND E.Status = 1;" connection = pg.DB(host='localhost', user='******', passwd='root', dbname='datawarehouse') data = connection.query(query) connection.close() try: for line in data: if line[0] == idEmployee: return True except: return False return False
def db(self, pwd): mysql.connector.connect(host='localhost', user='******', password='******') # Noncompliant mysql.connector.connection.MySQLConnection(host='localhost', user='******', password='******') # OK (avoid FPs) mysql.connector.connect(host='localhost', user='******', password=pwd) # OK mysql.connector.connection.MySQLConnection(host='localhost', user='******', password=pwd) # OK mysql.connector.connection.MySQLConnection(host='localhost', user='******', password='') # OK mysql.connector.connection.MySQLConnection(host='localhost', user='******', "") # OK pymysql.connect(host='localhost', user='******', password='******') # Noncompliant pymysql.connect('localhost', 'root', 'password') # Noncompliant {{Review this potentially hard-coded credential.}} # ^^^^^^^^^^ pymysql.connections.Connection(host='localhost', user='******', password='******') # OK (avoid FPs) pymysql.connections.Connection('localhost', 'root', 'password') # Noncompliant pymysql.connect(host='localhost', user='******', password=pwd) # OK pymysql.connect('localhost', 'root', pwd) # OK pymysql.connections.Connection(host='localhost', user='******', password=pwd) # OK pymysql.connections.Connection('localhost', 'root', pwd) # OK pymysql.connect('localhost', 'root', '') # Compliant pymysql.connect(host='localhost', user='******', password='') # Compliant pymysql.connections.Connection(host='localhost', user='******', password='') # Compliant pymysql.connections.Connection('localhost', 'root', '') # Compliant psycopg2.connect(host='localhost', user='******', password='******') # Noncompliant psycopg2.connect(host='localhost', user='******', password=pwd,) # OK pgdb.connect(host='localhost', user='******', password='******') # Noncompliant pgdb.connect('localhost', 'postgres', 'password') # Noncompliant pgdb.connect(host='localhost', user='******', password=pwd) # OK pgdb.connect('localhost', 'postgres', pwd) # OK pg.DB(host='localhost', user='******', passwd='Azerty123') # Noncompliant pg.DB(None, 'localhost', 5432, None, 'postgres', 'password') # Noncompliant pg.DB(host='localhost', user='******', passwd=pwd) # OK pg.DB(None, 'localhost', 5432, None, 'postgres', pwd) # OK pg.connect(host='localhost', user='******', passwd='Azerty123') # Noncompliant pg.connect(None, 'localhost', 5432, None, 'postgres', 'password') # Noncompliant pg.connect(host='localhost', user='******', passwd=pwd) # OK pg.connect(None, 'localhost', 5432, None, 'postgres', pwd) # OK pg.connect(host='localhost', user='******', passwd='') # Compliant pg.connect(None, 'localhost', 5432, None, 'postgres', '') # Compliant random.call(None, password = 42) # OK random.call(None, password = "******") # Noncompliant random.call(None, password = "") # OK pg.connect(*unpack, 'localhost', 5432, None, 'postgres', pwd) # OK
def ConsultStore(IdStore): connection = pg.DB(dbname='sk8database', host='127.0.0.1', port = 5432, user='******', passwd='root') query = "SELECT * FROM ConsultStore(" + str(IdStore) + ");" print( connection.query(query)) connection.close()
def getPromoStore(IdStore): connection = pg.DB(dbname='testpsql', host='127.0.0.1', port = 5432, user='******', passwd='root') query = "SELECT * FROM getPromoStore(" + str(IdStore) + ");" print( connection.query(query)) connection.close()
def get_connection(): """ Sets up the postgreSQL connection. Uses settings from the config file. """ return pg.DB(host=os.environ.get('DBHOST', True), user=os.environ.get('DBUSER', True), passwd=os.environ.get('DBPASS', True), dbname=os.environ.get('DBNAME', True))
def ConsultEmployee(IdEmpleado): connection = pg.DB(dbname='datawarehouse', host='127.0.0.1', port = 5432, user='******', passwd='root') query = "SELECT * FROM ConsultEmployee(" + str(IdEmpleado) + ");" print( connection.query(query)) connection.close()
def get_connection(): """ Sets up the postgreSQL connection by loading in db settings from config """ return pg.DB(host=config.DBHOST, user=config.DBUSER, passwd=config.DBPASS, dbname=config.DBNAME)
def InsertEmployee_aux(idperson): connection = pg.DB(dbname='datawarehouse', host='127.0.0.1', port = 5432, user='******', passwd='root') query = "INSERT INTO Employee VALUES ( " + str(idperson) + ", 1 );" data = connection.query(query) connection.close()