-2

I've been stuck for a few days trying to run some code in MySQL to fill a database that I have already created. Initially upon running I got the error 1251 : "Client does not support authentication protocol requested by server; consider upgrading MySQL client". In the MySQL documentation and stackoverflow answers I found, I was led to change the default insecureAuth setting from the default false to true. Here is the code I am currently using...

import datetime
import MySQLdb as mdb
from math import ceil

def obtain_btc():
    now = datetime.datetime.utcnow()
    symbols = ['BTC', 'Crypto', 'Bitcoin', 'No Sector', 'USD', now, now]
    return symbols

def insert_btc_symbols(symbols, insecureAuth):
    db_host = 'localhost'
    db_user = 'natrob'
    db_pass = '**********'
    db_name = 'securities_master'
    con = mdb.connect(host=db_host,user=db_user,passwd=db_pass,db=db_name,{insecureAuth:true})
    column_str = "ticker, instrument, name, sector, currency, created_date, last_updated_date"
    insert_str = (("%s, ")*7)[:2]
    final_str = ("INSERT INTO symbols (%s) VALUES (%s)" % (column_str,insert_str))
    print (final_str,len(symbols))

    with con:
        cur = con.cursor()
        for i in range(0,int(ceil(len(symbols)/100.0))):
            cur.executemany(final_str,symbols[i*100:(i+1)*100-1])

if __name__ == "__main__":
    symbols = obtain_btc()
    insert_btc_symbols(symbols)

I recently have gotten the error: "non-keyword arg after keyword arg". I've tried to switch the order to no avail, which leads me to believe that I may not be changing the default setting correctly. Any help or advice is appreciated. Thank you.

Mikey Mike
  • 79
  • 1
  • 9
  • 1
    You don't need advice on changing the connections, you need to write correct python. Your indentation is off in the question, but you're also using string formatting to make your query (bad idea, look into SQL injection) and you're calling `insert_btc_symbols(symbols)` even though you've defined that function to take 2 arguments. – roganjosh Jun 21 '18 at 22:26
  • That comment comes across as harsh, which I didn't intend, but the bottom line is that no library is going to be able to fix the issues in this code, you'll have to address some fundamental python issues yourself. It's not your choice of library that's failing here. – roganjosh Jun 21 '18 at 22:35
  • "In the MySQL documentation and stackoverflow answers I found, I was led to change the default insecureAuth setting from the default false to true." Then show us the answer you found that told you to do this. What you've written is not legal Python, and it's hard to guess what you actually wanted to write, especially since [the docs](http://mysqlclient.readthedocs.io/user_guide.html#functions-and-attributes) don't list anything for `connect` that looks similar, but if you show us the code you were trying to copy, it should be pretty easy to explain how to do it right. – abarnert Jun 21 '18 at 22:42
  • @roganjosh, I originally only had one argument going into insert_btc_symbols, but added insecureAuth because MySQL had recommended it and because it gave me a less daunting error. I'm new to database management systems so I'm learning as I go. I edited the code to reflect the spacing in my script. – Mikey Mike Jun 21 '18 at 23:20
  • @abarnert, this following link is where I got the code, I modified it because I'm not taking directly from HTML. The code begins under section "Obtaining Listed Symbols Data", https://www.quantstart.com/articles/Securities-Master-Database-with-MySQL-and-Python The following an example of an instance where it suggesting changing insecureAuth, https://github.com/mysqljs/mysql/issues/1574 – Mikey Mike Jun 21 '18 at 23:23
  • @roganjosh, I changed insert_btc_symbols back to a single argument of symbols, tried the suggested answers by badger, and reported the errors I got – Mikey Mike Jun 21 '18 at 23:38

2 Answers2

0

The issue looks like is coming from {insecureAuth:true} where it is not a keyword argument. ie var=value. I'm not familiar with the library but if that is a keyword then you should be able to set it as a keyword or pass it with ** 

con = mdb.connect(host=db_host,user=db_user,passwd=db_pass,db=db_name,insecureAuth=True)

or

con = mdb.connect(host=db_host,user=db_user,passwd=db_pass,db=db_name,**{insecureAuth:true})
badger0053
  • 1,179
  • 1
  • 13
  • 19
  • when I implement your first suggestion I receive the Type error "insecureAuth is an invalid keyword argument for this function" …. when implementing your second suggestion I receive the Name error " global name 'insecureAuth is not defined", and MySQL additionally writes that 'insecureAuth' is an unresolved reference. – Mikey Mike Jun 21 '18 at 23:35
  • @MikeyMike are you sure that it's a parameter for the function connect()? can you just delete it? – badger0053 Jun 22 '18 at 12:36
  • Yes it is a connection parameter, and it's default setting is false, however, I need to find a way to set it to true in order to get through an error message that keeps getting thrown. – Mikey Mike Jun 22 '18 at 15:37
  • @MikeyMike https://mysqlclient.readthedocs.io/_mysql.html?highlight=connect() I don't see it listed as a parameter. Am I missing something? – badger0053 Jun 22 '18 at 16:57
  • https://github.com/mysqljs/mysql#connection-options I'm not sure how different these two are, because your page seems pretty focused on the connect() I'm using, but InsecureAuth is on this one. – Mikey Mike Jun 24 '18 at 00:50
  • Your documentation links to a NodeJS library, not Python. At the top of your script you have "import MySQLdb as mdb" which is the documentation I provided you. Have you tried deleting it? I would assume you would get a different error – badger0053 Jun 25 '18 at 11:05
  • Wow I can't believe I didn't notice it was for JS, thank you for pointing that out. I found my way around the password, but you saved me a lot of trouble for later trying to figure out the library issue. Thanks. – Mikey Mike Jun 25 '18 at 17:04
0

I managed to get the section of code working by getting the public key for the password and using that in place of the normal password. This was in lieu of using the insecureAuth parameters.

Mikey Mike
  • 79
  • 1
  • 9