Python Forum

Python Forum > Python Coding > General Coding Help > Python and MySql
Full Version: Python and MySql
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

ogautier

May-19-2021, 09:29 PM
Hello,

Currently I have a database (MySql) with a table called monedas where I have the following fields, simbolo and decimales.

Ae I am connecting to an API that generates a response of type dictionary.

I have saved the dictionary as a list to be able to obtain two values (simbolo and decimales), the code is as follows:
count = 0
all_symbols = [(symbol ["symbol"], symbol ["pricePrecision"]) for symbol in response ["symbols"]]
for symbol in all_symbols:
     print (symbol [0], symbol [1])
     count + = 1
     symbol = symbol [0]
     decimal = symbol [1]
In this case, I can obtain the two values correctly, but I cannot insert the new values that are different from the ones I have in the currencies table using the following code:

mycursor.execute("INSERT INTO monedap (simbolo, decimales) SELECT 'simbolo', decimales FROM monedas WHERE simbolo = %s and decimales = %s AND NOT EXISTS (SELECT * FROM monedas WHERE simbolo= ' ')", (simbolo, decimal))
Can someone suggest me what to do please.

Regards,

Orlando Gautier

kashcode

May-19-2021, 09:39 PM
can you show response from API?

ogautier

May-19-2021, 09:45 PM
(May-19-2021, 09:39 PM)kashcode Wrote: [ -> ]can you show response from API?

Yes I Can

This is the response:

response = {'timezone': 'UTC', 'serverTime': 1621287738195, 'futuresType': 'U_MARGINED', 'rateLimits': [{'rateLimitType': 'REQUEST_WEIGHT', 'interval': 'MINUTE', 'intervalNum': 1, 'limit': 2400}, {'rateLimitType': 'ORDERS', 'interval': 'MINUTE', 'intervalNum': 1, 'limit': 1200}, {'rateLimitType': 'ORDERS', 'interval': 'SECOND', 'intervalNum': 10, 'limit': 300}], 'exchangeFilters': [], 'assets': [{'asset': 'USDT', 'marginAvailable': True, 'autoAssetExchange': '-10000'}, {'asset': 'BNB', 'marginAvailable': False, 'autoAssetExchange': '0'}, {'asset': 'BUSD', 'marginAvailable': True, 'autoAssetExchange': '-10000'}, {'asset': 'BTC', 'marginAvailable': True, 'autoAssetExchange': '-0.00100000'}], 'symbols': [{'symbol': 'BTCUSDT', 'pair': 'BTCUSDT', 'contractType': 'PERPETUAL', 'deliveryDate': 4133404800000, 'onboardDate': 1569398400000, 'status': 'TRADING', 'maintMarginPercent': '2.5000', 'requiredMarginPercent': '5.0000', 'baseAsset': 'BTC', 'quoteAsset': 'USDT', 'marginAsset': 'USDT', 'pricePrecision': 2, 'quantityPrecision': 3, 'baseAssetPrecision': 8, 'quotePrecision': 8, 'underlyingType': 'COIN', 'underlyingSubType': [], 'settlePlan': 0, 'triggerProtect': '0.0500', 'filters': [{'minPrice': '556.72', 'maxPrice': '1000000', 'filterType': 'PRICE_FILTER', 'tickSize': '0.01'}, {'stepSize': '0.001', 'filterType': 'LOT_SIZE', 'maxQty': '1000', 'minQty': '0.001'}, {'stepSize': '0.001', 'filterType': 'MARKET_LOT_SIZE', 'maxQty': '1000', 'minQty': '0.001'}, {'limit': 200, 'filterType': 'MAX_NUM_ORDERS'}, {'limit': 10, 'filterType': 'MAX_NUM_ALGO_ORDERS'}, {'notional': '5', 'filterType': 'MIN_NOTIONAL'}, {'multiplierDown': '0.8500', 'multiplierUp': '1.1500', 'multiplierDecimal': '4', 'filterType': 'PERCENT_PRICE'}], 'orderTypes': ['LIMIT', 'MARKET', 'STOP', 'STOP_MARKET', 'TAKE_PROFIT', 'TAKE_PROFIT_MARKET', 'TRAILING_STOP_MARKET'], 'timeInForce': ['GTC', 'IOC', 'FOK', 'GTX']}, {'symbol': 'ETHUSDT', 'pair': 'ETHUSDT', 'contractType': 'PERPETUAL', 'deliveryDate': 4133404800000, 'onboardDate': 1569398400000, 'status': 'TRADING', 'maintMarginPercent': '2.5000', 'requiredMarginPercent': '5.0000', 'baseAsset': 'ETH', 'quoteAsset': 'USDT', 'marginAsset': 'USDT', 'pricePrecision': 2, 'quantityPrecision': 3, 'baseAssetPrecision': 8, 'quotePrecision': 8, 'underlyingType': 'COIN', 'underlyingSubType': [], 'settlePlan': 0, 'triggerProtect': '0.0500', 'filters': [{'minPrice': '39.86', 'maxPrice': '100000', 'filterType': 'PRICE_FILTER', 'tickSize': '0.01'}, {'stepSize': '0.001', 'filterType': 'LOT_SIZE', 'maxQty': '10000', 'minQty': '0.001'}, {'stepSize': '0.001', 'filterType': 'MARKET_LOT_SIZE', 'maxQty': '10000', 'minQty': '0.001'}, {'limit': 200, 'filterType': 'MAX_NUM_ORDERS'}, {'limit': 10, 'filterType': 'MAX_NUM_ALGO_ORDERS'}, {'notional': '5', 'filterType': 'MIN_NOTIONAL'}, {'multiplierDown': '0.8500', 'multiplierUp': '1.1500', 'multiplierDecimal': '4', 'filterType': 'PERCENT_PRICE'}], 'orderTypes': ['LIMIT', 'MARKET', 'STOP', 'STOP_MARKET', 'TAKE_PROFIT', 'TAKE_PROFIT_MARKET', 'TRAILING_STOP_MARKET'], 'timeInForce': ['GTC', 'IOC', 'FOK', 'GTX']}, {'symbol': 'BCHUSDT', 'pair': 'BCHUSDT', 'contractType': 'PERPETUAL', 'deliveryDate': 4133404800000, 'onboardDate': 1569398400000, 'status': 'TRADING', 'maintMarginPercent': '2.5000', 'requiredMarginPercent': '5.0000', 'baseAsset': 'BCH', 'quoteAsset': 'USDT', 'marginAsset': 'USDT', 'pricePrecision': 2, 'quantityPrecision': 3, 'baseAssetPrecision': 8, 'quotePrecision': 8, 'underlyingType': 'COIN', 'underlyingSubType': [], 'settlePlan': 0, 'triggerProtect': '0.0500', 'filters': [{'minPrice': '13.93', 'maxPrice': '100000', 'filterType': 'PRICE_FILTER', 'tickSize': '0.01'}, {'stepSize': '0.001', 'filterType': 'LOT_SIZE', 'maxQty': '10000', 'minQty': '0.001'}, {'stepSize': '0.001', 'filterType': 'MARKET_LOT_SIZE', 'maxQty': '1000', 'minQty': '0.001'}, {'limit': 200, 'filterType': 'MAX_NUM_ORDERS'}, {'limit': 10, 'filterType': 'MAX_NUM_ALGO_ORDERS'}, {'notional': '5', 'filterType': 'MIN_NOTIONAL'}, {'multiplierDown': '0.8500', 'multiplierUp': '1.1500', 'multiplierDecimal': '4', 'filterType': 'PERCENT_PRICE'}], 'orderTypes': ['LIMIT', 'MARKET', 'STOP', 'STOP_MARKET', 'TAKE_PROFIT', 'TAKE_PROFIT_MARKET', 'TRAILING_STOP_MARKET'], 'timeInForce': ['GTC', 'IOC', 'FOK', 'GTX']}, {'symbol': 'XRPUSDT', 'pair': 'XRPUSDT', 'contractType': 'PERPETUAL', 'deliveryDate': 4133404800000, 'onboardDate': 1569398400000, 'status': 'TRADING', 'maintMarginPercent': '2.5000', 'requiredMarginPercent': '5.0000', 'baseAsset': 'XRP', 'quoteAsset': 'USDT', 'marginAsset': 'USDT', 'pricePrecision': 4, 'quantityPrecision': 1, 'baseAssetPrecision': 8, 'quotePrecision': 8, 'underlyingType': 'COIN', 'underlyingSubType': [], 'settlePlan': 0, 'triggerProtect': '0.0500', 'filters': [{'minPrice': '0.0143', 'maxPrice': '100000', 'filterType': 'PRICE_FILTER', 'tickSize': '0.0001'}, {'stepSize': '0.1', 'filterType': 'LOT_SIZE', 'maxQty': '1000000', 'minQty': '0.1'}, {'stepSize': '0.1', 'filterType': 'MARKET_LOT_SIZE', 'maxQty': '1000000', 'minQty': '0.1'}, {'limit': 200, 'filterType': 'MAX_NUM_ORDERS'}, {'limit': 10, 'filterType': 'MAX_NUM_ALGO_ORDERS'}, {'notional': '5', 'filterType': 'MIN_NOTIONAL'}, {'multiplierDown': '0.8500', 'multiplierUp': '1.1500', 'multiplierDecimal': '4', 'filterType': 'PERCENT_PRICE'}], 'orderTypes': ['LIMIT', 'MARKET', 'STOP', 'STOP_MARKET', 'TAKE_PROFIT', 'TAKE_PROFIT_MARKET', 'TRAILING_STOP_MARKET'], 'timeInForce': ['GTC', 'IOC', 'FOK', 'GTX']}]}

kashcode

May-20-2021, 06:23 AM
You need insert symbol if does not exist or update when exists?

ibreeden

May-20-2021, 07:49 AM
(May-19-2021, 09:29 PM)ogautier Wrote: [ -> ]I can obtain the two values correctly, but I cannot insert the new values that are different from the ones I have in the currencies table

Why can you not insert the new values? Does it give an error message? In that case we need the complete error message.
And you say you can get the two values correctly. Are you sure? Look at lines 6 and 7 in your code.
     symbol = symbol [0]
     decimal = symbol [1]
In line 6 "symbol" is a list. But then you assign a different value to "symbol" and then symbol[1] is ... ?

ogautier

May-20-2021, 01:22 PM
(May-20-2021, 06:23 AM)kashcode Wrote: [ -> ]You need insert symbol if does not exist or update when exists?

Quote:I just need to insert symbol if not exist

ogautier

May-20-2021, 01:29 PM
(May-20-2021, 07:49 AM)ibreeden Wrote: [ -> ]
(May-19-2021, 09:29 PM)ogautier Wrote: [ -> ]I can obtain the two values correctly, but I cannot insert the new values that are different from the ones I have in the currencies table

Why can you not insert the new values? Does it give an error message? In that case we need the complete error message.
And you say you can get the two values correctly. Are you sure? Look at lines 6 and 7 in your code.
     symbol = symbol [0]
     decimal = symbol [1]
In line 6 "symbol" is a list. But then you assign a different value to "symbol" and then symbol[1] is ... ?

Quote:I do not get any errors. Yes, I can get all the values Symbol and decimal.
symbol[1] is the way to get the value of the symbols key.

This is the response I get from the API

response = {'timezone': 'UTC', 'serverTime': 1621287738195, 'futuresType': 'U_MARGINED', 'rateLimits': [{'rateLimitType': 'REQUEST_WEIGHT', 'interval': 'MINUTE', 'intervalNum': 1, 'limit': 2400}, {'rateLimitType': 'ORDERS', 'interval': 'MINUTE', 'intervalNum': 1, 'limit': 1200}, {'rateLimitType': 'ORDERS', 'interval': 'SECOND', 'intervalNum': 10, 'limit': 300}], 'exchangeFilters': [], 'assets': [{'asset': 'USDT', 'marginAvailable': True, 'autoAssetExchange': '-10000'}, {'asset': 'BNB', 'marginAvailable': False, 'autoAssetExchange': '0'}, {'asset': 'BUSD', 'marginAvailable': True, 'autoAssetExchange': '-10000'}, {'asset': 'BTC', 'marginAvailable': True, 'autoAssetExchange': '-0.00100000'}], 'symbols': [{'symbol': 'BTCUSDT', 'pair': 'BTCUSDT', 'contractType': 'PERPETUAL', 'deliveryDate': 4133404800000, 'onboardDate': 1569398400000, 'status': 'TRADING', 'maintMarginPercent': '2.5000', 'requiredMarginPercent': '5.0000', 'baseAsset': 'BTC', 'quoteAsset': 'USDT', 'marginAsset': 'USDT', 'pricePrecision': 2, 'quantityPrecision': 3, 'baseAssetPrecision': 8, 'quotePrecision': 8, 'underlyingType': 'COIN', 'underlyingSubType': [], 'settlePlan': 0, 'triggerProtect': '0.0500', 'filters': [{'minPrice': '556.72', 'maxPrice': '1000000', 'filterType': 'PRICE_FILTER', 'tickSize': '0.01'}, {'stepSize': '0.001', 'filterType': 'LOT_SIZE', 'maxQty': '1000', 'minQty': '0.001'}, {'stepSize': '0.001', 'filterType': 'MARKET_LOT_SIZE', 'maxQty': '1000', 'minQty': '0.001'}, {'limit': 200, 'filterType': 'MAX_NUM_ORDERS'}, {'limit': 10, 'filterType': 'MAX_NUM_ALGO_ORDERS'}, {'notional': '5', 'filterType': 'MIN_NOTIONAL'}, {'multiplierDown': '0.8500', 'multiplierUp': '1.1500', 'multiplierDecimal': '4', 'filterType': 'PERCENT_PRICE'}], 'orderTypes': ['LIMIT', 'MARKET', 'STOP', 'STOP_MARKET', 'TAKE_PROFIT', 'TAKE_PROFIT_MARKET', 'TRAILING_STOP_MARKET'], 'timeInForce': ['GTC', 'IOC', 'FOK', 'GTX']}, {'symbol': 'ETHUSDT', 'pair': 'ETHUSDT', 'contractType': 'PERPETUAL', 'deliveryDate': 4133404800000, 'onboardDate': 1569398400000, 'status': 'TRADING', 'maintMarginPercent': '2.5000', 'requiredMarginPercent': '5.0000', 'baseAsset': 'ETH', 'quoteAsset': 'USDT', 'marginAsset': 'USDT', 'pricePrecision': 2, 'quantityPrecision': 3, 'baseAssetPrecision': 8, 'quotePrecision': 8, 'underlyingType': 'COIN', 'underlyingSubType': [], 'settlePlan': 0, 'triggerProtect': '0.0500', 'filters': [{'minPrice': '39.86', 'maxPrice': '100000', 'filterType': 'PRICE_FILTER', 'tickSize': '0.01'}, {'stepSize': '0.001', 'filterType': 'LOT_SIZE', 'maxQty': '10000', 'minQty': '0.001'}, {'stepSize': '0.001', 'filterType': 'MARKET_LOT_SIZE', 'maxQty': '10000', 'minQty': '0.001'}, {'limit': 200, 'filterType': 'MAX_NUM_ORDERS'}, {'limit': 10, 'filterType': 'MAX_NUM_ALGO_ORDERS'}, {'notional': '5', 'filterType': 'MIN_NOTIONAL'}, {'multiplierDown': '0.8500', 'multiplierUp': '1.1500', 'multiplierDecimal': '4', 'filterType': 'PERCENT_PRICE'}], 'orderTypes': ['LIMIT', 'MARKET', 'STOP', 'STOP_MARKET', 'TAKE_PROFIT', 'TAKE_PROFIT_MARKET', 'TRAILING_STOP_MARKET'], 'timeInForce': ['GTC', 'IOC', 'FOK', 'GTX']}, {'symbol': 'BCHUSDT', 'pair': 'BCHUSDT', 'contractType': 'PERPETUAL', 'deliveryDate': 4133404800000, 'onboardDate': 1569398400000, 'status': 'TRADING', 'maintMarginPercent': '2.5000', 'requiredMarginPercent': '5.0000', 'baseAsset': 'BCH', 'quoteAsset': 'USDT', 'marginAsset': 'USDT', 'pricePrecision': 2, 'quantityPrecision': 3, 'baseAssetPrecision': 8, 'quotePrecision': 8, 'underlyingType': 'COIN', 'underlyingSubType': [], 'settlePlan': 0, 'triggerProtect': '0.0500', 'filters': [{'minPrice': '13.93', 'maxPrice': '100000', 'filterType': 'PRICE_FILTER', 'tickSize': '0.01'}, {'stepSize': '0.001', 'filterType': 'LOT_SIZE', 'maxQty': '10000', 'minQty': '0.001'}, {'stepSize': '0.001', 'filterType': 'MARKET_LOT_SIZE', 'maxQty': '1000', 'minQty': '0.001'}, {'limit': 200, 'filterType': 'MAX_NUM_ORDERS'}, {'limit': 10, 'filterType': 'MAX_NUM_ALGO_ORDERS'}, {'notional': '5', 'filterType': 'MIN_NOTIONAL'}, {'multiplierDown': '0.8500', 'multiplierUp': '1.1500', 'multiplierDecimal': '4', 'filterType': 'PERCENT_PRICE'}], 'orderTypes': ['LIMIT', 'MARKET', 'STOP', 'STOP_MARKET', 'TAKE_PROFIT', 'TAKE_PROFIT_MARKET', 'TRAILING_STOP_MARKET'], 'timeInForce': ['GTC', 'IOC', 'FOK', 'GTX']}, {'symbol': 'XRPUSDT', 'pair': 'XRPUSDT', 'contractType': 'PERPETUAL', 'deliveryDate': 4133404800000, 'onboardDate': 1569398400000, 'status': 'TRADING', 'maintMarginPercent': '2.5000', 'requiredMarginPercent': '5.0000', 'baseAsset': 'XRP', 'quoteAsset': 'USDT', 'marginAsset': 'USDT', 'pricePrecision': 4, 'quantityPrecision': 1, 'baseAssetPrecision': 8, 'quotePrecision': 8, 'underlyingType': 'COIN', 'underlyingSubType': [], 'settlePlan': 0, 'triggerProtect': '0.0500', 'filters': [{'minPrice': '0.0143', 'maxPrice': '100000', 'filterType': 'PRICE_FILTER', 'tickSize': '0.0001'}, {'stepSize': '0.1', 'filterType': 'LOT_SIZE', 'maxQty': '1000000', 'minQty': '0.1'}, {'stepSize': '0.1', 'filterType': 'MARKET_LOT_SIZE', 'maxQty': '1000000', 'minQty': '0.1'}, {'limit': 200, 'filterType': 'MAX_NUM_ORDERS'}, {'limit': 10, 'filterType': 'MAX_NUM_ALGO_ORDERS'}, {'notional': '5', 'filterType': 'MIN_NOTIONAL'}, {'multiplierDown': '0.8500', 'multiplierUp': '1.1500', 'multiplierDecimal': '4', 'filterType': 'PERCENT_PRICE'}], 'orderTypes': ['LIMIT', 'MARKET', 'STOP', 'STOP_MARKET', 'TAKE_PROFIT', 'TAKE_PROFIT_MARKET', 'TRAILING_STOP_MARKET'], 'timeInForce': ['GTC', 'IOC', 'FOK', 'GTX']}]}

kashcode

May-20-2021, 02:43 PM
In case when you need insert symbols if not exists you can create table where symbols column is unique key end use insert ignore or INSERT ... ON DUPLICATE KEY UPDATE symbol=symbol

Pedroski55

May-20-2021, 11:10 PM
You are using: cursor.commit() right?

This works well for me using pymysql:

def mysqlRemotePut(number): 
        # To connect remote MySQL database 
        conn = pymysql.connect( 
            host='localhost',            
            user='peter',  
            password = 'mypw', 
            db='allstudentsdb', 
            ) 
          
        cur = conn.cursor()
        try:
            # INSERT query
            identifyer = 1
            for key1 in data_dict.keys():
                #print('key1 is:', key1, '\n\n')
                for key2 in data_dict[key1].keys():
                    #print('key2 is:', key2, 'id is:', identifyer, 'column is:', key2, 'total is:', data_dict[key1][key2])
                #print('\n\n')
                    total = data_dict[key1][key2]
                    sql = f"INSERT INTO surveyU6_{number}_totals (id, {key2}) VALUES ({identifyer}, {total}) ON DUPLICATE KEY UPDATE {key2} = VALUES({key2})"
                    cur.execute(sql)
                    # connection is not autocommit by default. So we must commit to save our changes.
                    conn.commit()
                identifyer +=1 

        except Error as e:
            print(e)
            
        # To close the connection 
        conn.close()
Python Forum > Python Coding > General Coding Help > Python and MySql