+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Python and MySql (/thread-33710.html)
Python and MySql - ogautier - May-19-2021
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
RE: Python and MySql - kashcode - May-19-2021
can you show response from API?
RE: Python and MySql - ogautier - May-19-2021
(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']}]}
RE: Python and MySql - kashcode - May-20-2021
You need insert symbol if does not exist or update when exists?
RE: Python and MySql - ibreeden - May-20-2021
(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 ... ?
RE: Python and MySql - ogautier - May-20-2021
(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
RE: Python and MySql - ogautier - May-20-2021
(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']}]}
RE: Python and MySql - kashcode - May-20-2021
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
RE: Python and MySql - Pedroski55 - May-20-2021
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()