Add new user to Wordpress using Python and MySQLdb

Question

I'm trying to add a new Wordpress user from Python. I created this function:

def adduser(domain_ip,username,password):
    db = MySQLdb.connect(host=domain_ip, user=username, passwd=password)
    cur = db.cursor()
    cur.execute("USE %s;" % "yogatrai")
    cmd1 = """INSERT INTO `wp_users` (`ID`, `user_login`, `user_pass`, `user_nicename`, `user_email`, `user_status`) VALUES ('5', 'demo1', MD5('demo1demo'), 'firstname lastname', 'email@example.com', '0');"""
    cur.execute(cmd1)

The same sql command is working in Navicat. In Python it returns ok but the new user is not present in the database. Where is my error?

score 0 · Accepted Answer · answered Dec 18 '14 at 01:22

You need to commit and close the db connection after the insert operation. Assuming that sql is your query string, do:

try:
    # Execute the SQL command
    cursor.execute(sql)
    # Commit your changes in the     database
    db.commit()
except:
    # Rollback in case there is any error
    db.rollback()

# disconnect from server
db.close()

Check out this example here: http://www.tutorialspoint.com/python/python_database_access.htm

score 0 · Answer 2 · edited May 23 '17 at 10:33

It looks like you haven't committed the transaction. MySQLdb offers a convenient way to manage the transaction context via with, like this:

with connection as cursor:
    cursor.execute(query, parameters)

Where connection is your open database connection object, query is your parameterized SQL statement, and parameters are any values supplied by the user. Parameterizing your SQL statements is very important as it helps you avoid syntax errors and protects you from malicious SQL injection. Another benefit of parameterization is that you can define your parameterized SQL outside of your function, making the function itself much easier to read.

Here's how you would implement this approach in your adduser function. First, rewrite your SQL statement as a parameterized query:

cmd1 = """
INSERT INTO `wp_users`
    (`ID`, `user_login`, `user_pass`, `user_nicename`, `user_email`, `user_status`)
VALUES
    (%s, %s, MD5(%s), %s, %s, %s);
"""

I put the statement on its own lines so I can very quickly copy and paste my SQL between the Python source code and MySQL Workbench or the command line. You can use whatever style you like; the important thing to notice is that there is a %s placeholder for each user-input value. The execute function will take care of escaping special characters, quoting strings, etc. so you don't have to.

Now, the function can be much simpler:

def adduser(domain_ip, username, password):
    db = MySQLdb.connect(host=domain_ip, user=username, passwd=password, db="yogatrai")
    args = (5, 'demo1', 'demo1demo', 'firstname lastname', 'email@example.com', 0)
    with db as cur:
        cur.execute(cmd1, args)

The db parameter to MySQLdb.connect replaces your USE statement and the with context manager takes care of transactions. If an exception is thrown inside the with block, the transaction will be rolled back; otherwise, it will be committed when the with block ends. I talk about this approach to transaction management with MySQLdb in more detail in another answer.

Your final code will look different, of course, but for testing purposes the above should work just fine. My only other advice is, try to use names that are more meaningful than cmd1. I like to treat my SQL statements as constants, so I would probably name this one something like INSERT_USER; the important thing is just to be clear and consistent.

Add new user to Wordpress using Python and MySQLdb

2 Answers2