Posts: 23
Threads: 11
Joined: Nov 2018
May-19-2021, 09:29 PM
(This post was last modified: May-19-2021, 09:29 PM by ogautier.
Edit Reason: Added code tags, Fixed code tags again
)
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
Posts: 24
Threads: 3
Joined: Apr 2020
can you show response from API?
Posts: 23
Threads: 11
Joined: Nov 2018
(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']}]}
Posts: 24
Threads: 3
Joined: Apr 2020
You need insert symbol if does not exist or update when exists?
Posts: 582
Threads: 1
Joined: Aug 2019
(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 ... ?
Posts: 23
Threads: 11
Joined: Nov 2018
(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
Posts: 23
Threads: 11
Joined: Nov 2018
May-20-2021, 01:29 PM
(This post was last modified: May-20-2021, 01:29 PM by ogautier.)
(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']}]}
Posts: 24
Threads: 3
Joined: Apr 2020
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
Posts: 1,095
Threads: 143
Joined: Jul 2017
May-20-2021, 11:10 PM
(This post was last modified: May-20-2021, 11:31 PM by Pedroski55.)
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()
|
