def insert_name(name, gender):
    query = "INSERT INTO babynames(babyName, gender) " \
            "VALUES(%s,%s)"

    args = (name, gender)
 
    try:
        db_config = read_db_config()
        conn = MySQLConnection(**db_config)
 
        cursor = conn.cursor()
        cursor.execute(query, args)
 
        if cursor.lastrowid:
            print('last insert id', cursor.lastrowid)
        else:
            print('last insert id not found')
 
        conn.commit()
    except Error as error:
        print(error)
 
    finally:
        cursor.close()
        conn.close()
示例#2
0
def main():
    print('Put txt file in the same folder of exe!')
    table_name = 'lst_' + (input('Please name your list (lst_ will be add as prefix automatically):').lower())
    if table_name == 'lst_':
        sys.exit(0)
    exist_control = exist_list(table_name)
    if exist_control is None:
        create_table(table_name)
    elif exist_control == 0:
        drop_table(table_name)
        create_table(table_name)
    else:
        print('The named table is exist and contain ' + str(exist_control) + ' Records')
        ask = input('Do you want to drop ' + str(exist_control) + ' table? (yes/no)')
        if ask.lower() == 'yes':
            drop_table(table_name)
            create_table(table_name)

        else:
            print('Exit without any change!')
            sys.exit(0)

    extension = str(input('Please Enter Your Extension(ir,co.ir ,...):'))
    list_method=input('Choice your Method( 1:Auto Letter Generator 2:Import Text File ) :')
    if list_method == '1':
        letter_number = int(input('Number of Letters:'))
        keywords = [''.join(i) for i in product(ascii_lowercase, repeat=letter_number)]
        db_config = read_db_config()
        conn = MySQLConnection(**db_config)

        for line in range(0, len(keywords)):
            cursor = conn.cursor()
            Query = "insert into %s (site) values (concat('%s','.','%s'));" \
                    % (table_name, '{0}'.format(str(keywords[line])),extension)
            cursor.execute(Query)
            print(str(line+1),end='\r')
        print(str(line+1),'Records Imported!')
    elif list_method == '2':
        dic_filename_mask = str(input('Whats the Text Dictionary Filename {without extension}:'))
        filename = dic_filename_mask + '.txt'
        dic_list = open(filename)
        print('Total Count of Records: ' + str(sum(1 for _ in dic_list)))
        dic_list.close()
        db_config = read_db_config()
        conn = MySQLConnection(**db_config)
        cursor = conn.cursor()
        load_text_file_query = "LOAD DATA LOCAL INFILE '%s' INTO TABLE %s  LINES TERMINATED BY '\\r\\n' (SITE);" \
                               % (filename, table_name)
        print('Transferring List...')
        cursor.execute(load_text_file_query)
        print('Add Extension to list...')
        update_extension_query = "update %s set site=concat(site,'.','%s')" % (table_name, extension)
        cursor.execute(update_extension_query)
        conn.commit()
        cursor.close()
        conn.close()
    else:
        print('Wrong Choice,Bye!')

    print("Finish")
示例#3
0
def data_laundry(user,saldo_user):
    kodeLau = randomTiket()
    total = 0
    status = ""
    print '1. Cuci laundry'
    print '2. Cuci laundry + setrika'
    print '3. Cuci laundry + setrika + delivery'
    pilLaundry = input('Masukan pilihan : ')
    berat = input('Masukan berat pakaian : ')
    if pilLaundry == 1 :
        total = 5000 * berat
    elif pilLaundry == 2:
        total = 7000 * berat
    elif pilLaundry == 3:
        total = 7000 * berat + 3000

    fix_update = saldo_user - total
    if fix_update < 0:
        status = "Lunas"
        print "Saldo anda tidak mencukupi"
        tambah_saldo = raw_input("Ingin menambah saldo anda ? ")
        if tambah_saldo == 'y' or 'Y' or 'Ya' or 'YA' or 'ya':
            saldoTambah(saldo_user,fix_update,user)
    else:
        status = "Lunas"
        query1 = "INSERT INTO data_user (kodeLau,user,status,total)"\
             "VALUES(%s,%s,%s,%s)"
        args1 = (kodeLau,user,status,total)

        try:
            dbConfig = bacaConfig()
            connection = MySQLConnection(**dbConfig)
            query = connection.cursor()
            query.execute(query1,args1)
        
            if query.lastrowid:
                print "Data laundry berhasil dimasukan"
                query2 = "UPDATE user SET saldo = %s WHERE user = %s"
                data2 = (fix_update,user)
                try:
                    dbConfig = bacaConfig()
                    conn2 = MySQLConnection(**dbConfig)
                    cursor2 = conn2.cursor()
                    cursor2.execute(query2,data2)
                    conn2.commit()
                except Error as e:
                    print (e)
                finally:
                    cursor2.close()
                    conn2.close()
            else:
                print "Gagal menginput"
            connection.commit()

        except Error as e:
            print (e)

        finally:
            query.close()
        connection.close()
示例#4
0
def mysql_fetchall():
	'''Query a MySQL database using fetchall()'''
	try:
		db_config = read_db_config()

		#Creating a new MySQLConnection object
		conn = MySQLConnection(**db_config)
		
		#Creating a new MySQLCursor object from the MySQLConnection object
		cursor = conn.cursor()

		#Selects all rows from the space table
		cursor.execute("SELECT * from space")

		#selects all the rows  in the cursor result set
		rows = cursor.fetchall()
		print('Number of rows: %d' % cursor.rowcount)
		
		#prints the rows out
		for row in rows:
			print(row)

	except Error as error:
		print(error)

	finally:
		cursor.close()
		conn.close()
		print('Connection closed.')
示例#5
0
def mysql_fetchone():
	'''Query a MySQL database using fetchone()'''
	try:
		db_config = read_db_config()

		#Creating a new MySQLConnection object
		conn = MySQLConnection(**db_config)
		
		#Creating a new MySQLCursor object from the MySQLConnection object
		cursor = conn.cursor()

		#Selects all rows from the space table
		cursor.execute("SELECT * from space")

		#selects the next row in the cursor result set
		row = cursor.fetchone()
		
		#prints the row out and gets the next row
		while row is not None:
			print(row)
			row = cursor.fetchone()

	except Error as error:
		print(error)

	finally:
		cursor.close()
		conn.close()
		print('Connection closed.')
示例#6
0
def create_table(tbl_name_val):
    query = "CREATE TABLE IF NOT EXISTS %s ( " \
            "No int NOT NULL auto_increment," \
            "Site varchar(100)," \
            "Status varchar(30)," \
            "Email varchar(100)," \
            "Person varchar(100)," \
            "Phone varchar(25)," \
            "lockedby bigint,  " \
            "lockedat datetime," \
            "Trycount int default 0 , " \
            "Hostname varchar(50)," \
            "HFlag char(1) DEFAULT NULL," \
            "IP varchar(15) DEFAULT NULL," \
            "PRIMARY KEY (No)" \
            ") ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;" % tbl_name_val
    args = tbl_name_val

    try:
        db_config = read_db_config()
        conn = MySQLConnection(**db_config)

        cursor = conn.cursor()
        cursor.execute(query)
        print('New Table Created:' + str(tbl_name_val))

    except Error as error:
        print(error)

    finally:
        cursor.close()
        conn.close()
示例#7
0
def update_book(book_id, title):
    # read database configuration
    db_config = read_db_config()

    # prepare query and data
    query = """ UPDATE books
                SET title = %s
                WHERE id = %s """

    data = (title, book_id)

    try:
        conn = MySQLConnection(**db_config)

        # update book title
        cursor = conn.cursor()
        cursor.execute(query, data)

        # accept the changes
        conn.commit()

    except Error as error:
        print(error)

    finally:
        cursor.close()
        conn.close()
示例#8
0
def ads():
    website = request.args.get('website', 'earthtravelers.com')
    user = app.config['DB_LOGIN']
    password = app.config['DB_PW']
    host = app.config['DB_HOST']
    database = app.config['DB_NAME']

    conn = MySQLConnection(user=user, password=password, host=host, database=database)
    conn.autocommit = True
    cursor = conn.cursor()
    args = (website,)
    try:
        cursor.callproc('AdMania.prc_GetAds', args)
    except Error as e:
        print e

    # In order to handle multiple result sets being returned from a database call,
    # mysql returns results as a list of lists.
    # Therefore, even if there is only one result set, you still have to get it from the list of lists.
    for result in cursor.stored_results():
        row_set = result.fetchall()

    result_set = []
    for row in row_set:
        result_set.append(row[0].decode().replace('##DOMAIN_ID##', '7782886'))

    cursor.close()
    conn.close()

    return render_template('T1.html',resultsSET=result_set)
示例#9
0
def query_with_fetchone(Lista1,v2):
    try:
        conn = MySQLConnection(host=DB_HOST,user=DB_USER,password=DB_PASS,database=DB_NAME)
        cursor = conn.cursor()
        cursor.execute("SELECT nombre FROM clientes")

        row = cursor.fetchone()
        Lista1.delete(0,END)
        while row is not None:
            d=""
            for i in row:
                if i== "("or i==")"or i=="'"or i ==",":
                    pass
                else:
                    d = d+i
            Lista1.insert(END,d)
            row = cursor.fetchone()

    except Error as e:
        print(e)

    finally:
        Lista1.grid(column=1,row=2)
        update(v2)
        cursor.close()
        conn.close()
示例#10
0
文件: Connector.py 项目: Faoxis/Task
    def get_comments(self, article_id):
        try:
            conn = MySQLConnection(**self.db)

            query = "SELECT * FROM comment WHERE article_id=%s"

            cursor = conn.cursor()
            cursor.execute(query, (article_id,))

            list_of_comments = list()

            row_comment = cursor.fetchone()
            if row_comment is None:
                return None

            while row_comment is not None:
                list_of_comments.append(row_comment)
                row_comment = cursor.fetchone()
            return list_of_comments

        except Error as e:
            print(e)

        finally:
            cursor.close()
            conn.close()
示例#11
0
def insert_character(char_id, name, realname, gender, origin, image, siteurl, deck):
	query = "INSERT INTO characters(id, name, realname, gender, origin, image, siteurl, deck) " \
			"VALUES(%s, %s, %s, %s, %s, %s, %s, %s)"  

	args = (char_id, name, realname, gender, origin, image, siteurl, deck)

	try:
		db_config = read_db_config()
		conn = MySQLConnection(**db_config)

		cursor = conn.cursor()
		cursor.execute(query, args)
		
		if cursor.lastrowid:
			print('last insert id', cursor.lastrowid)
		else:
			print('last insert id not found')

		conn.commit()

	except Error as error:
		print error

	finally:
		cursor.close()
		conn.close()
def insert_user(name, password, email):
    query = "INSERT INTO users(username, password, email) " \
            "VALUES(%s,%s,%s)"

    args = (name, password, email)
 
    try:
        db_config = read_db_config()
        conn = MySQLConnection(**db_config)
 
        cursor = conn.cursor()
        cursor.execute(query, args)
 
        if cursor.lastrowid:
            print('last insert id', cursor.lastrowid)
        else:
            print('last insert id not found')
 
        conn.commit()
    except Error as error:
        print(error)
 
    finally:
        cursor.close()
        conn.close()
示例#13
0
def insert_one(star_name, star_location):
	'''Insert one row into a MySQL database'''
	query = ("INSERT INTO space(star_name, star_location)" 
			 "VALUES(%s, %s)")
	args = (star_name, star_location)

	try:
		db_config = read_db_config()

		#Creating a new MySQLConnection object
		conn = MySQLConnection(**db_config)
		
		#Creating a new MySQLCursor object from the MySQLConnection object
		cursor = conn.cursor()

		#Inserts one row into the database
		cursor.execute(query, args)

		if cursor.lastrowid:
			print('Insert ID: ', cursor.lastrowid)
		else:
			print('Insert Failure')

		conn.commit()

	except Error as error:
		print(error)

	finally:
		cursor.close()
		conn.close()
		print('Connection closed.')
示例#14
0
def input_data():

    for x in range(len(gabungan)):
        gabungan[x]

    query = "INSERT INTO data_test " 
    nilai = "VALUES (\"\"," + "\"" + file + "\"" + ","
    for x in range(len(gabungan)):
        nilai = nilai + str(gabungan[x])
        if x < len(gabungan)-1 :
            nilai = nilai + ", " 
            
    query = query + nilai + ")"
#    print query
        
    try:
        db_config = read_db_config()
        conn = MySQLConnection(**db_config)
 
        cursor = conn.cursor()
        cursor.execute(query)
 
        conn.commit()
    except Error as e:
        print('Error:', e)
 
    finally:
        cursor.close()
        conn.close() 
示例#15
0
def query_row(table):
    """Returns the next row of a query result set or None

        str -> tuple"""
    try:
        dbconfig = read_db_config()  # returns a dict of connection parameters
        print("Connecting " + dbconfig["user"] + " to " + dbconfig["database"] + "...")
        conn = MySQLConnection(**dbconfig)
        if conn.is_connected():
            print("Connection established.")
        else:
            print("Connection failed")

        cursor = conn.cursor(buffered=True)
        sql_command = "SELECT * FROM " + table
        print("Executed command: " + sql_command + ".")
        cursor.execute(sql_command)

        row = cursor.fetchone()
        return row

        # The fetchall method is similar but memory-consuming
        # rows = cursor.fetchall()
        # print('Total rows:', cursor.rowcount)
        # return rows

    except Error as e:
        print(e)
    finally:
        cursor.close()
        conn.close()
示例#16
0
def delete_one(star_id):
	'''Delete one row of a MySQL database'''
	query = "DELETE FROM space WHERE id = %s"

	try:
		db_config = read_db_config()

		#Creating a new MySQLConnection object
		conn = MySQLConnection(**db_config)
		
		#Creating a new MySQLCursor object from the MySQLConnection object
		cursor = conn.cursor()

		#Deletes one row in the database
		cursor.execute(query, (star_id,))

		conn.commit()

	except Error as error:
		print(error)

	finally:
		cursor.close()
		conn.close()
		print('Connection closed.')
示例#17
0
def login_data(request):
    try:
        username = request.POST.get('username','')
        phone = request.POST.get('phone', '')

        Error_dict = {}

        dbconfig = {'password': '******', 'host': 'localhost', 'user': '******', 'database': 'login_database'}
        conn = MySQLConnection(**dbconfig)
        cursor = conn.cursor()
        username = "******" + username + "'"
        cursor.execute("select COUNT(*) from client_info where Name = " + username + " AND phone_no = " + str(phone) + " ")
        count = cursor.fetchone()

        if(count[0] <= 0) :
            Error_dict['Wrong_values'] = "Wrong Username or Phone no"

        else :
            context_dict = {}
            context_dict['name'] = username
            return render_to_response('logged_in_user.html', context_dict)

    except Error as e:
        print(e)

    finally:
        cursor.close()
        conn.close()

    return render_to_response('login_form.html', Error_dict)
示例#18
0
def update_one(star_id, star_location):
	'''Update a field in a MySQL database'''
	query = ("UPDATE space" 
			 "SET star_name = %s"
			 "WHERE id = %s")
	args = (star_name, star_id)

	try:
		db_config = read_db_config()

		#Creating a new MySQLConnection object
		conn = MySQLConnection(**db_config)
		
		#Creating a new MySQLCursor object from the MySQLConnection object
		cursor = conn.cursor()

		#Updates a field in the database
		cursor.execute(query, args)

		conn.commit()

	except Error as error:
		print(error)

	finally:
		cursor.close()
		conn.close()
		print('Connection closed.')
示例#19
0
def query_with_fetchone():
    try:
        dbconfig = {'password': '******', 'host': 'localhost', 'user': '******', 'database': 'stats'}
        conn = MySQLConnection(**dbconfig)
        cursor = conn.cursor()
        cursor.execute("select COUNT(*) from clientBookings where MONTH(CheckInDate) = '2' AND YEAR(CheckInDate) = '2005' AND status = 'A'")
        accept = cursor.fetchone()
        x = accept[0]
        print(accept)
        print x

        cursor1 = conn.cursor()
        cursor1.execute("select COUNT(*) from clientBookings where MONTH(CheckInDate) = '2' AND YEAR(CheckInDate) = '2005' AND status = 'X'")
        cancel = cursor1.fetchone()
        print(cancel)

        cursor2 = conn.cursor()
        cursor2.execute("select COUNT(*) from clientBookings where MONTH(CheckInDate) = '2' AND YEAR(CheckInDate) = '2005' AND status = 'p'")
        wait = cursor2.fetchone()
        print(wait)

        cursor3 = conn.cursor()
        cursor3.execute("select COUNT(*) from clientBookings where MONTH(CheckInDate) = '2' AND YEAR(CheckInDate) = '2005' AND status = 'R'")
        request = cursor3.fetchone()
        print(request)

    except Error as e:
        print(e)

    finally:
        cursor.close()
        conn.close()
示例#20
0
def insert_many(stars):
	'''Insert multiple rows into a MySQL database'''
	query = ("INSERT INTO space(star_name, star_location)" 
			 "VALUES(%s, %s)")

	try:
		db_config = read_db_config()

		#Creating a new MySQLConnection object
		conn = MySQLConnection(**db_config)
		
		#Creating a new MySQLCursor object from the MySQLConnection object
		cursor = conn.cursor()

		#Inserts many rows into the database
		cursor.executemany(query, stars)

		conn.commit()

	except Error as error:
		print(error)

	finally:
		cursor.close()
		conn.close()
		print('Connection closed.')
def insert_Face_Details(face_id, gender,age,emotion,emotion_percentage):
    query = "INSERT INTO FaceData(face_id, gender,age,emotion,emotion_percentage) " \
            "VALUES(%s,%s,%s,%s,%s)"
    args = (face_id, gender,age,emotion,emotion_percentage)
 
    try:
        db_config = read_db_config()
        conn = MySQLConnection(**db_config) 
 
        cursor = conn.cursor()
        cursor.execute(query, args)
 
        if cursor.lastrowid:
            print('last insert id', cursor.lastrowid)
        else:
            print('last insert id not found') 
 
        conn.commit()
    except Error as error:
        print(error)
 
    finally:
        cursor.close()
        conn.close()
        
    print('Face Data transfered into Database')
示例#22
0
def insert_imei(query, args):

    try:
        db_config = read_db_config()
        conn = MySQLConnection(**db_config)

        cursor = conn.cursor()
        cursor.execute(query, args)

        if cursor.lastrowid:
            mensaje = ('Last insert id: %s' % (cursor.lastrowid,) )
            print(mensaje)
        else:
           mensaje = 'Last insert id not found'

        conn.commit()
        
    
    except Error as error:
        print(error)

    finally:
        cursor.close()
        conn.close()
        
    return mensaje    
示例#23
0
def insert_book(title, isbn):
    query = "INSERT INTO books(title, isbn) " "VALUES(%s, %s)"

    args = (title, isbn)

    try:
        db_config = read_db_config()
        conn = MySQLConnection(**db_config)

        cursor = conn.cursor()
        cursor.execute(query, args)

        if cursor.lastrowid:
            print ("last insert id", cursor.lastrowid)
        else:
            print ("last insert id not found")

        conn.commit()

    except Error as error:
        print error

    finally:
        cursor.close()
        conn.close()
def Connect():

    kwargs = ReadingMySQLConfig()
    MyConnection = MySQLConnection(**kwargs)
    try:
        if MyConnection.is_connected():
            print("Connected")
    except Error as e:
        print(e)
    finally:
        MyConnection.close()
示例#25
0
def database_update():

    config=Config().config
    user = config['DB_LOGIN']
    password = config['DB_PW']
    host = config['DB_HOST']
    database = config['DB_NAME']
    cjauth = config['CJ_AUTH']
    cjurl = config['CJ_URL']

    conn = MySQLConnection(user=user, password=password, host=host, database=database)
    conn.autocommit = True
    cursor = conn.cursor()

    page_number = 0
    records_per_page = 100 # this is the max number allowed by the affiliate api per call.
    records_returned = records_per_page
    headers = {'authorization': cjauth}

    while records_returned == records_per_page:
        page_number += 1
        params = {'website-id': '7782886', 'link-type': 'banner', 'advertiser-ids': 'joined', 'page-number': page_number, 'records-per-page': records_per_page}

        result = requests.get(cjurl, headers=headers, params=params)
        result_xml = result.text

        root = ET.fromstring(result_xml.encode('utf8'))
        records_returned = int(root.find('links').get('records-returned'))

        for link in root.iter('link'):
            link_code_html = html.fromstring(link.find('link-code-html').text)
            height = int(link_code_html.xpath('//img/@height')[0])
            width = int(link_code_html.xpath('//img/@height')[0])

            mysql_args = (
                link.find('link-id').text,
                link.find('advertiser-id').text,
                link.find('advertiser-name').text,
                link.find('category').text,
                'None' if link.find('promotion-start-date').text == None else link.find('promotion-start-date').text,
                'None' if link.find('promotion-end-date').text == None else link.find('promotion-end-date').text,
                height,
                width,
                link.find('link-code-html').text)

            try:
                cursor.callproc('AdMania.prc_UpdateAd',mysql_args)

            except Error as e:
                print e

    cursor.close()
    conn.close()
示例#26
0
    def getAllUsers(self):

        try:
            conn = MySQLConnection(host='localhost', database='mydb', user='******', password='******')
            cursor = conn.cursor()
            cursor.execute("SELECT * FROM users")
            rows = cursor.fetchall()
            return rows
        except Error as error:
            print error
        finally:
            cursor.close()
            conn.close()
示例#27
0
def create_database(cursor):
    try:
        conn = MySQLConnection(**db_data)
        cursor = conn.cursor()
        cursor.execute(
            "CREATE DATABASE {} DEFAULT CHARACTER SET 'utf8'".format(DB_NAME))

    except Error as e:
        print(e)

    finally:
        cursor.close()
        conn.close()
示例#28
0
def UpdateBooks(book_id, title):
    kwargs = ReadingMySQLConfig()
    data = (title, book_id)
    query = "UPDATE books SET title = %s WHERE id = %s"
    try:
        MyConnection = MySQLConnection(**kwargs)
        cursor = MyConnection.cursor()
        cursor.execute(query, data)
        MyConnection.commit()
    except Error as e:
        print(e)
    finally:
        MyConnection.close()
示例#29
0
def ReadImage(author_id, filename):
    kwargs = ReadingMySQLConfig()
    query = 'SELECT photo FROM authors WHERE id = %s'
    try:
        MyConnection = MySQLConnection(**kwargs)
        cursor = MyConnection.cursor()
        cursor.execute(query, (author_id,))
        photo = cursor.fetchone()[0]
        WriteFile(photo, filename)
    except Error as e:
        print(e)
    finally:
        MyConnection.close()
示例#30
0
def read_db():
    try:
        dbconfig = read_db_config()
        conn = MySQLConnection(**dbconfig)
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM temperature;")
        row = cursor.fetchone()
        while row is not None:
            print(row)
            row = cursor.fetchone()
    finally:
        cursor.close()
        conn.close()
def getRangeIdentifier(word):
	rows = []
	try:
		db_config = read_db_config()
		conn = MySQLConnection(**db_config)

		cursor = conn.cursor()
		if word: 
			word = word + "%"
			command = "SELECT word, category FROM range_identifiers WHERE word LIKE '%" + word +"'"
			#print command
			cursor.execute(command)
		
		rows = cursor.fetchall()
		return rows

	except Error as error:
		print error

	finally:
		cursor.close()
		conn.close()
示例#32
0
    def get_all(self):
        try:
            db_config = read_db_config()
            conn = MySQLConnection(**db_config)
            cursor = conn.cursor()

            cursor.callproc('getAllCustomerUserInfo')
            all_customer_info = []

            for result in cursor.stored_results():
                customers = result.fetchall()

            for x in customers:
                currentinfo = CustomerInfo()
                currentinfo.customer_id = x[0]
                currentinfo.work_phone = x[1]
                currentinfo.home_phone = x[2]
                u = User()
                u.id = x[3]
                u.password = x[4]
                u.last_login = x[5]
                u.is_superuser = x[6]
                u.username = x[7]
                u.first_name = x[8]
                u.last_name = x[9]
                u.email = x[10]
                u.is_staff = x[11]
                u.is_active = x[12]
                u.date_joined = x[13]
                currentinfo.set_user(u)
                all_customer_info.append(currentinfo)

                cursor.close()
            conn.close()
        except Error as error:
            print(error)
        except Exception as e:
            print(e)
        return all_customer_info
def insertManyRestaurants(restaurants_info):
    query = "INSERT INTO restaurants(restaurant_id,name,url,address,latitude,longitude,rating,country_id,phone,timings,average_cost_for_two,is_pure_veg,\
			sports_bar_flag,has_bar,has_ac,has_dine_in,has_delivery,takeaway_flag,accepts_credit_cards,accepts_debit_cards,\
			sheesha_flag,halal_flag,has_wifi,has_live_music,nightlife_flag,stag_entry_flag,entry_fee,has_online_delivery,\
			min_order,average_delivery_time,delivery_charge,accepts_online_payment)"    \
      "VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,%s)"

    try:
        db_config = read_db_config()
        conn = MySQLConnection(**db_config)

        cursor = conn.cursor()
        cursor.executemany(query, restaurants_info)

        conn.commit()

    except Error as error:
        print error

    finally:
        cursor.close()
        conn.close()
示例#34
0
def insert_Staff(Name, Pay, Surname, LibraryName):
    query = "INSERT INTO Staff(Name,Pay,Surname ,LibraryName) VALUES(  %s , %s , %s , %s)"
    args = (Name, Pay, Surname, LibraryName)
    try:
        db_config = read_db_config()
        conn = MySQLConnection(**db_config)

        cursor = conn.cursor()
        cursor.execute(query, args)

        if cursor.lastrowid:
            print('done')
        else:
            print('last insert id not found')

        conn.commit()
    except Error as error:
        print(error)

    finally:
        cursor.close()
        conn.close()
示例#35
0
def insert_Publisher(Name, Date_of_Est, Address):
    query = "INSERT INTO Publishers VALUES(%s,%s,%s)"
    args = (Name, Address, Date_of_Est)
    try:
        db_config = read_db_config()
        conn = MySQLConnection(**db_config)

        cursor = conn.cursor()
        cursor.execute(query, args)

        if cursor.lastrowid:
            print('done')
        else:
            print('last insert id not found')

        conn.commit()
    except Error as error:
        print(error)

    finally:
        cursor.close()
        conn.close()
示例#36
0
def delete_book(book_id):
    #read database configuration
    db_config = read_db_config()

    #prepare query and data
    query = "DELETE FROM books WHERE id = %s"

    try:
        conn = MySQLConnection(**db_config)
        #update book title
        cursor = conn.cursor()
        cursor.execute(query, (book_id, ))

        #accept the changes
        conn.commit()

    except Error as error:
        print(error)

    finally:
        cursor.close()
        conn.close()
示例#37
0
def invoice_range_update():

    query = """ UPDATE invoice_range
                SET use_flag = %s
                WHERE use_flag = %s """

    data = (2, 1)

    try:
        conn = MySQLConnection(**db_config)

        cursor = conn.cursor()
        cursor.execute(query, data)

        conn.commit()

    except Error:
        log.exception("MYSQL error occurred:")

    finally:
        cursor.close()
        conn.close()
    def query_student(self, student_id):
        query = "SELECT id, name, email, picture FROM STUDENT " \
                "WHERE STUDENT.id = %s"

        args = (student_id, )

        try:
            db_config = self.read_db_config()
            conn = MySQLConnection(**db_config)

            cursor = conn.cursor()
            cursor.execute(query, args)

            data = cursor.fetchone()

            return data
        except Error as error:
            print(error)

        finally:
            cursor.close()
            conn.close()
示例#39
0
def post_code(code):
    dbconfig = read_db_config()
    conn = MySQLConnection(**dbconfig)
    cursor = conn.cursor()
    # print(code)

    cursor.execute("SELECT * FROM `post_images` WHERE `key_name` ='" +
                   str(code) + "';")
    profile_row = cursor.fetchall()
    #cursor.execute("SELECT * FROM `posts` WHERE `uniqueID` ='" + str(code) + "';")
    #cover_row = cursor.fetchall()

    if len(profile_row) == 1:
        conn.close()
        cursor.close()
        return wall_post_gen()

    else:
        conn.close()
        cursor.close()
        # print(code)
        return code
示例#40
0
def turnosjuadorAgendaGolf(usuario, fec):
    turnosjugador = []
    query = " SELECT * FROM agenda_golf WHERE fecha = %s "
    data = (fec)
    dbconfig = read_db_config()
    try:
        conn = MySQLConnection(**dbconfig)
        cursor = conn.cursor()
        cursor.execute(query, (data, ))
        row = cursor.fetchone()
        while row is not None:
            for i in range(8, 12):
                if row[i] == usuario:
                    turnosjugador.append(row)
            print(row)
            row = cursor.fetchone()
    except Error as error:
        print(error)
    finally:
        cursor.close()
        conn.close()
    return turnosjugador
示例#41
0
def fetch_dashboard_sp(employee):
    try:
        conn = MySQLConnection(host=host,
                               database=database,
                               user=user,
                               password=password)
        cursor = conn.cursor()
        args = (employee)
        cursor.callproc('DashboardValues', [args])
        for result in cursor.stored_results():
            column_names_list = [x[0] for x in result.description]
            result_dicts = [
                dict(zip(column_names_list, row)) for row in result.fetchall()
            ]
            return result_dicts

    except Error as e:
        print(e)

    finally:
        cursor.close()
        conn.close()
示例#42
0
def connect():
    """ Connect to MySQL database """

    db_config = read_db_config("config.ini", "mysql")
    table_config = read_db_config("config.ini", "tabledata")

    try:
        print('Connecting to MySQL database...')
        conn = MySQLConnection(**db_config)

        if conn.is_connected():
            print('connection established.')
            spotifyCall(conn, table_config)
        else:
            print('connection failed.')

    except Error as error:
        print(error)

    finally:
        conn.close()
        print('Connection closed.')
示例#43
0
def insertOneDish(menu,restaurant_id):
	query = "INSERT INTO menus(menu,restaurant_id) " \
			"VALUES(%s, %s)"

	args = (menu,restaurant_id)

	try:
		db_config = read_db_config()
		conn = MySQLConnection(**db_config)

		cursor = conn.cursor()
		cursor.execute(query, args)

		conn.commit()

	except Error as error:
		print error

	finally:
		cursor.close()
		conn.close()
		print "DISHES DATA INSERTED!!!"
def getNERSemantics(ner):
	try:
		db_config = read_db_config()
		# db_config['database'] = "travel"
		conn = MySQLConnection(**db_config)

		cursor = conn.cursor()
		command = "SELECT minimum, maximum FROM ner_config WHERE category = '" + ner + "'"
		cursor.execute(command)
		
		rows = cursor.fetchall()
		if rows:
			row = rows[0]
			return row
		return ''
		
	except Error as error:
		print error

	finally:
		cursor.close()
		conn.close()
示例#45
0
def add_user(email,
             password,
             phone_1,
             ph,
             ip,
             mac,
             verification_code,
             is_confirmed=False):
    query = 'INSERT INTO `account_temp` VALUE (%s, %s, PASSWORD(%s), %s, %s, %s, %s, %s)'
    args = None, email, password, ip, mac, verification_code, is_confirmed, None
    try:
        conn = MySQLConnection(**config_eduwork_temp)
        cursor = conn.cursor()
        cursor.execute(query, args)
        conn.commit()
    except Error as err:
        print(err)
    else:
        print(' Тимчасовий обліковий запис створено! '.center(80, '='))
    finally:
        cursor.close()
        conn.close()
示例#46
0
def insertOneLocality(locality_id, name, city_id, data_resource):
    query = "INSERT INTO locality(locality_id, name, city_id, data_resource) " \
      "VALUES(%s, %s, %s, %s)"

    args = (locality_id, name, city_id, data_resource)

    try:
        db_config = read_db_config()
        conn = MySQLConnection(**db_config)

        cursor = conn.cursor()
        cursor.execute(query, args)

        conn.commit()

    except Error as error:
        print error

    finally:
        cursor.close()
        conn.close()
        print "LOCALITY DATA INSERTED!!!"
def connect():
    """ Connect to MySQL database """
 
    db_config = read_db_config()
 
    try:
        print('Connecting to MySQL database...')
        conn = MySQLConnection(**db_config)
 
        if conn.is_connected():
            print('connection established.')
            cursor=conn.cursor();
            cursor.execute("create table reg_logs(car_number_plate varchar(20),time varchar(20),date varchar(20),description varchar(20))")
        else:
            print('connection failed.')
 
    except Error as error:
        print(error)
 
    finally:
        conn.close()
        print('Connection closed.')
示例#48
0
def fetchOne():
    try:
        # Connect to the Database
        dbconfig = readDBConfig()
        conn = MySQLConnection(**dbconfig)
        # Create a "cursor" this objects helps retrieve the rows using buffering
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM employees LIMIT 20")

        row = cursor.fetchone()

        # Retrieve each row
        while row is not None:
            print(row)
            row = cursor.fetchone()

    except Error as e:
        print(e)

    finally:
        cursor.close()
        conn.close()
def findDishByName(query):
	rows = []
	try:
		db_config = read_db_config()
		conn = MySQLConnection(**db_config)

		cursor = conn.cursor()
		if query: 
			query = query + "%"
		cursor.execute("SELECT DISTINCT(dish_name) FROM menu WHERE dish_name LIKE '%s'" % query)

		rows = cursor.fetchall()
	#	for row in rows:
	#		print row

	except Error as error:
		print error

	finally:
		cursor.close()
		conn.close()
	return rows
示例#50
0
def insert_copy(Number, Books_ISBN, Position):
    query = "INSERT INTO Copy VALUES (%s,%s,%s)"
    args = (Books_ISBN, Number, Position)
    try:
        db_config = read_db_config()
        conn = MySQLConnection(**db_config)

        cursor = conn.cursor()
        cursor.execute(query, args)

        if cursor.lastrowid:
            print('done')
        else:
            print('last insert id not found')

        conn.commit()
    except Error as error:
        print(error)

    finally:
        cursor.close()
        conn.close()
示例#51
0
def db_log_history(endDT=datetime.now(), limit=100, \
                   periodTD=timedelta(days=1), db_result_list = [], db='mysql'):
    try:
        dbconfig = read_db_config(db=db)
        conn = MySQLConnection(**dbconfig)
        cursor = conn.cursor(buffered=True)
        startDT = endDT - periodTD
        startDTstr = startDT.strftime("%Y-%m-%d %H:%M:%S")
        endDTstr = endDT.strftime("%Y-%m-%d %H:%M:%S")
        query = "SELECT datetime, source, msg_type, message FROM message_log " \
                "WHERE datetime BETWEEN %s and %s " \
                "ORDER BY datetime DESC " \
                "LIMIT %s "
        args = (startDTstr, endDTstr, limit)
        cursor.execute(query, args)
        #text_result = ""
        #db_result_list = []
        for row in iter_row(cursor, 10):
            #text_result = row[0].strftime("%H:%M:%S") + " " + row[1] + " " + row[2] + " " + row[3]
            #db_result_list.append(text_result, )
            db_result_list.append(row, )
        db_result_list.reverse()
    except Error as e:
        dbMsg.appMessage("db_log_history()", dbMsg.EXCEPT, \
                         "Exception occurred in query execution")
        print(e)
    try:
        cursor.close()
    except Error as error:
        dbMsg.appMessage("db_log_history()", dbMsg.EXCEPT, \
                         "Exception occurred closing cursor")
        print(error)
    try:
        conn.close()
    except Error as error:
        dbMsg.appMessage("db_log_history()", dbMsg.EXCEPT, \
                         "Exception occurred closing connection")
        print(error)
    return db_result_list
示例#52
0
def recuperaMarcasCampos(campo):
    dbconfig = read_db_config()
    query = "SELECT marcas FROM campos_colombia WHERE campo=%s "
    data = (campo, )
    marcas = []
    try:
        conn = MySQLConnection(**dbconfig)
        cursor = conn.cursor()
        cursor.execute(query, data)
        try:
            row = cursor.fetchall()
            dato = row
        except:
            dato = 'errror'
    except Error as error:
        dato = 'errror'
    finally:
        cursor.close()
        conn.close()
    for lin in dato:
        marcas.append(lin[0])
    return marcas
示例#53
0
def insert_list_book(list_book):
    sql = """INSERT INTO list_book(id, ISBN, Name, page_num, pub_year) VALUES (%s, %s, %s, %s,%s)"""

    try:
        print('Connecting to MySQL database...')
        conn = MySQLConnection(host='localhost',
                               database='billing_simple',
                               user='******',
                               password='******')

        cursor = conn.cursor()
        cursor.executemany(sql, list_book)
        conn.commit()
        #frame.to_sql(name='billing_simple', con=conn, if_exists = 'append', index=False)

    except Error as error:
        print(error)

    finally:
        cursor.close()
        conn.close()
        print('Connection closed.')
def insertOneDish(dish_id, restaurant_id, dish_name, type, cost, data_resource):
	query = "INSERT INTO menu(dish_id, restaurant_id, dish_name, cost, type, data_resource) " \
			"VALUES(%s, %s, %s, %s, %s, %s)"

	args = (dish_id, restaurant_id, dish_name, cost, type, data_resource)

	try:
		db_config = read_db_config()
		conn = MySQLConnection(**db_config)

		cursor = conn.cursor()
		cursor.execute(query, args)

		conn.commit()

	except Error as error:
		print error

	finally:
		cursor.close()
		conn.close()
		print "DISHES DATA INSERTED!!!"
def insertOneCuisine(restaurant_id, cuisine_id):
    query = "INSERT INTO cuisine_restaurant(restaurant_id, cuisine_id) " \
      "VALUES(%s, %s)"

    args = (restaurant_id, cuisine_id)

    try:
        db_config = read_db_config()
        conn = MySQLConnection(**db_config)

        cursor = conn.cursor()
        cursor.execute(query, args)

        conn.commit()

    except Error as error:
        print error

    finally:
        cursor.close()
        conn.close()
        print "CUISINE_RESTAURANT DATA INSERTED!!!"
示例#56
0
def insertOneLocalityRestaurant(locality_id, restaurant_id):
    query = "INSERT INTO locality_restaurant(locality_id, restaurant_id) " \
      "VALUES(%s, %s)"

    args = (locality_id, restaurant_id)

    try:
        db_config = read_db_config()
        conn = MySQLConnection(**db_config)

        cursor = conn.cursor()
        cursor.execute(query, args)

        conn.commit()

    except Error as error:
        print error

    finally:
        cursor.close()
        conn.close()
        print "LOCALITY_RESTAURANT DATA INSERTED!!!"
示例#57
0
def connect():
    """
    Connect to MySQL database
    """

    db_config = read_db_config()

    try:
        print 'Connecting to MySQL Database...'
        conn = MySQLConnection(**db_config)

        if conn.is_connected():
            print 'Connection established'
        else:
            print 'Connection failed'

    except Error as e:
        print(e)

    finally:
        conn.close()
        print 'Connection Closed'
示例#58
0
def delete_book(book_id):
    db_config = read_db_config()

    query = "DELETE FROM books WHERE id = %s"

    try:
        # connect to the database server
        conn = MySQLConnection(**db_config)

        # execute the query
        cursor = conn.cursor()
        cursor.execute(query, (book_id, ))

        # accept the change
        conn.commit()

    except Error as error:
        print(error)

    finally:
        cursor.close()
        conn.close()
示例#59
0
def get_values():

    query = "SELECT year, injuries FROM fourth_table"

    try:
        config = {
            'user': '******',
            'password': '******',
            'host': 'localhost',
            'database': 'bootcamp'
        }
        conn = MySQLConnection(**config)

        cursor = conn.cursor()
        cursor.execute(query)

        # Create a container to hold these values
        tuple_container = []

        for (year, injuries) in cursor:
            tuple_container.append((year, injuries))

        print 'Original Container'
        print tuple_container

        print '\nSorter Container'
        new_container = sorted(tuple_container, key=get_key, reverse=True)
        print new_container

        # Now just find the first year
        print 'The year with the most injuries is: ', new_container[0][0]

    except Error as error:
        print(error)

    finally:
        cursor.close()
        conn.close()
示例#60
0
def event_create_insert_into_db(name, type, num_participants, startdatetime,
                                enddatetime, state):
    query = "INSERT INTO event(name,type,num_participants,startdatetime,enddatetime,state) " \
            "VALUES(%s,%s,%s,%s,%s,%s)"
    args = (name, type, num_participants, startdatetime, enddatetime, state)

    try:
        db_config = read_db_config()
        conn = MySQLConnection(**db_config)

        cursor = conn.cursor()
        cursor.execute(query, args)

        if cursor.lastrowid:
            print('last insert id', cursor.lastrowid)
        else:
            print('last insert id not found')

        last_event_id = cursor.lastrowid
        sql_event_start = "CREATE EVENT " + name + "id" + str(
            last_event_id
        ) + "start" + " ON SCHEDULE AT '" + startdatetime + "' DO UPDATE hems.event SET state = 'ready' WHERE id = " + str(
            last_event_id) + ";"
        sql_event_end = "CREATE EVENT " + name + "id" + str(
            last_event_id
        ) + "end" + " ON SCHEDULE AT '" + enddatetime + "' DO UPDATE hems.event SET state = 'deprovison' WHERE id = " + str(
            last_event_id) + ";"

        cursor.execute(sql_event_start)
        cursor.execute(sql_event_end)

        conn.commit()
    except Error as error:
        print(error)

    finally:
        cursor.close()
        conn.close()