Python MySQLConnection.cursor方法代码示例

# 需要导入模块: from mysql.connector import MySQLConnection [as 别名]
# 或者: from mysql.connector.MySQLConnection import cursor [as 别名]
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()

# 需要导入模块: from mysql.connector import MySQLConnection [as 别名]
# 或者: from mysql.connector.MySQLConnection import cursor [as 别名]
def query_with_fetchone():
    try:
        dbconfig = {'password': 'root', 'host': 'localhost', 'user': 'root', '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()

# 需要导入模块: from mysql.connector import MySQLConnection [as 别名]
# 或者: from mysql.connector.MySQLConnection import cursor [as 别名]
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")

# 需要导入模块: from mysql.connector import MySQLConnection [as 别名]
# 或者: from mysql.connector.MySQLConnection import cursor [as 别名]
def search():
    try:
        dbconfig = read_db_config()
        conn = MySQLConnection(**dbconfig)
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM data_train")
        #row_train = cursor.fetchone()
        data_train =  list(cursor)
            
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM data_test")
        data_test = list(cursor)
        
        
        index1 = 0
        for x in range(len(data_train)) :
        
            canbera = 0
            index2 = 2
            
            for y in range(82):                
                temp1 = (data_test[0][index2] - data_train[index1][index2]) 
                temp2 = (data_test[0][index2] + data_train[index1][index2])

                #print "temp1 "+str(temp1)
                #print "temp2 "+str(temp2)
                                
                if temp2 == 0 :
                    hasil_temp = abs(temp1)
                else :
                    hasil_temp = float(abs(temp1)) / float(temp2)
                
                #print "hasil temp = " + str(hasil_temp)
                canbera = float(canbera) + float(hasil_temp)
                
                index2 += 1
            index1+=1
            
            print canbera
        
            
        #for x in range(len(gabungan)):
            #row_train[x]
        
 
    except Error as e:
        print(e)
 
    finally:
        cursor.close()
        conn.close()

# 需要导入模块: from mysql.connector import MySQLConnection [as 别名]
# 或者: from mysql.connector.MySQLConnection import cursor [as 别名]
def create_user():
	print ('create')
	data = request.json	
	browser = data['browser']
	ip = data['ip']
			
	query = "SELECT COUNT(*) FROM users WHERE browser = %s AND ip = %s"
	try:		
		dbconfig = read_db_config()
		conn = MySQLConnection(**dbconfig)
		cursor = conn.cursor()
		cursor.execute(query, (browser, ip),)
		count = cursor.fetchone()
		count = count[0]
	except Error as e:
		print(e)
	finally:
		cursor.close()
		conn.close()
	if count != 0:
		query = "SELECT id FROM users WHERE browser = %s AND ip = %s"
		try:		
			dbconfig = read_db_config()
			conn = MySQLConnection(**dbconfig)
			cursor = conn.cursor()
			cursor.execute(query, (browser, ip),)
			id = cursor.fetchone()
		except Error as e:
			print(e)
		finally:
			cursor.close()
			conn.close()
		#if user already exist return his id
		return jsonify({'state': 'fail', 'index' : id}) 
	
	#if user is not exist add him to db
	query = "INSERT INTO users (date, browser, ip) VALUES (%s, %s, %s)"    
	date = datetime.now()
	try:
		db_config = read_db_config()
		conn = MySQLConnection(**db_config)
		cursor = conn.cursor()
		cursor.execute(query, (date, browser, ip, ),)
		conn.commit()
	except Error as error:
		print(error)
	finally:
		cursor.close()
		conn.close()				
	return jsonify({'state': 'OK'})

# 需要导入模块: from mysql.connector import MySQLConnection [as 别名]
# 或者: from mysql.connector.MySQLConnection import cursor [as 别名]
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.')

# 需要导入模块: from mysql.connector import MySQLConnection [as 别名]
# 或者: from mysql.connector.MySQLConnection import cursor [as 别名]
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()

# 需要导入模块: from mysql.connector import MySQLConnection [as 别名]
# 或者: from mysql.connector.MySQLConnection import cursor [as 别名]
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.')

# 需要导入模块: from mysql.connector import MySQLConnection [as 别名]
# 或者: from mysql.connector.MySQLConnection import cursor [as 别名]
def dataDiri(user):
    pswd = getpass.getpass('Masukan password : ',stream = None)

    akses = "Anggota"
    alamat = raw_input("Masukan alamat valid : ")
    hp = input("Masukan nomor hp valid : ")    
    email = raw_input("Masukan email valid : ")
    saldo = 10000
    query = "INSERT INTO user (user,pswd,akses,saldo,alamat,hp,email)"\
            "VALUES (%s,%s,%s,%s,%s,%s,%s)"
    args = (user,pswd,akses,saldo,alamat,hp,email)

    try:
        dbConfig = bacaConfig()
        conn = MySQLConnection(**dbConfig)

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

        nilaiId = 100000
        if cursor.lastrowid:
            print('Sukses mendaftar')
        else:
            print("Last insert id not found")

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

    finally:
        cursor.close()
        conn.close

# 需要导入模块: from mysql.connector import MySQLConnection [as 别名]
# 或者: from mysql.connector.MySQLConnection import cursor [as 别名]
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()

# 需要导入模块: from mysql.connector import MySQLConnection [as 别名]
# 或者: from mysql.connector.MySQLConnection import cursor [as 别名]
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()

# 需要导入模块: from mysql.connector import MySQLConnection [as 别名]
# 或者: from mysql.connector.MySQLConnection import cursor [as 别名]
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()

# 需要导入模块: from mysql.connector import MySQLConnection [as 别名]
# 或者: from mysql.connector.MySQLConnection import cursor [as 别名]
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.')

# 需要导入模块: from mysql.connector import MySQLConnection [as 别名]
# 或者: from mysql.connector.MySQLConnection import cursor [as 别名]
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    

# 需要导入模块: from mysql.connector import MySQLConnection [as 别名]
# 或者: from mysql.connector.MySQLConnection import cursor [as 别名]
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()