PyMySQL with Flask: %d format: a number is required, not str with stored procedure

[SeppPenner]

Hi, I'm trying to use the cursors from PyMySQL together with stored procedures. As far as I understood, I can use the cursor as it's described in the documentation: http://pymysql.readthedocs.io/en/latest/modules/cursors.html

However, when I call the callproc method:

cursor.callproc('storingSystemCreateCategory', args)

I get the following error:

%d format: a number is required, not str

My stored procedure is a simple one using only one IN parameter:

DELIMITER //
CREATE PROCEDURE storingSystemCreateCategory(IN name VARCHAR(255))
 BEGIN
 INSERT INTO StoringSystem.Category(`createdAt`, `updatedAt`, `deleted`, `name`)
 VALUES (CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, False, name);
 END //
DELIMITER ;

Is there a way to get this to work without the issue? I mean, the sense of stored procedures is to prevent SQL injections. So I would most likely want to use the validation on database side if possible.

Before you ask: Yes, the database access in general works. I use another project called Flask-MySQL which basically only simplifies the database initialization and connection from PyMySQL:

The issue is on stackoverflow, too: https://stackoverflow.com/questions/51176160/pymysql-with-flask-d-format-a-number-is-required-not-str-with-stored-procedu

[methane]

Why don't you paste traceback?

[SeppPenner]

Why don't you paste traceback?
Hi @methane. Let's just say it was too late in the evening when I posted the issue. I will provide more details on my setup and the traceback from the client soon.

[SeppPenner]

The traceback is:

[2018-07-05 20:31:52,080] DEBUG in api: Received data: {'name': 'test124'}
[2018-07-05 20:31:52,080] DEBUG in api: Given category name: test124
[2018-07-05 20:31:52,080] DEBUG in api: Connecting to MySQL database
[2018-07-05 20:31:52,080] DEBUG in api: Getting a cursor
Traceback (most recent call last):
  File ".\api.py", line 39, in createCategory
    cursor = mysql.get_db().cursor()
  File "C:\Program Files (x86)\Python\lib\site-packages\flaskext\mysql.py", line 64, in get_db
    ctx.mysql_db = self.connect()
  File "C:\Program Files (x86)\Python\lib\site-packages\flaskext\mysql.py", line 53, in connect
    return pymysql.connect(**self.connect_args)
  File "C:\Program Files (x86)\Python\lib\site-packages\pymysql\__init__.py", line 90, in Connect
    return Connection(*args, **kwargs)
  File "C:\Program Files (x86)\Python\lib\site-packages\pymysql\connections.py", line 704, in __init__
    self.connect()
  File "C:\Program Files (x86)\Python\lib\site-packages\pymysql\connections.py", line 964, in connect
    self.host_info = "socket %s:%d" % (self.host, self.port)
TypeError: %d format: a number is required, not str
127.0.0.1 - - [2018-07-05 20:31:52] "POST /api/category/create HTTP/1.1" 500 229 0.000000

More information regarding the setup:

1. Create the database and table:

CREATE DATABASE storingsystem;
USE storingsystem;
CREATE TABLE IF NOT EXISTS Category(
	id BIGINT NOT NULL AUTO_INCREMENT,
	createdAt TIMESTAMP NOT NULL,
	updatedAt TIMESTAMP NOT NULL,
	deleted BOOLEAN NOT NULL,
	name VARCHAR(255) NOT NULL,
	PRIMARY KEY (id)
);

2. Create the procedure:

DELIMITER //
CREATE PROCEDURE storingSystemCreateCategory(IN name VARCHAR(255))
 BEGIN
 INSERT INTO StoringSystem.Category(`createdAt`, `updatedAt`, `deleted`, `name`)
 VALUES (CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, False, name);
 END //
DELIMITER ;

3. Install the requirements:

pip install flask
pip install flask-mysql
pip install gevent

4. Use the app:

from flask import Flask, jsonify, request
from flaskext.mysql import MySQL
from gevent.pywsgi import WSGIServer
import traceback

mysql = MySQL()
app = Flask("My app")

app.config['MYSQL_DATABASE_USER'] = 'user'
app.config['MYSQL_DATABASE_PASSWORD'] = 'password'
app.config['MYSQL_DATABASE_DB'] = 'storingsystem'
app.config['MYSQL_DATABASE_HOST'] = 'localhost'
app.config['MYSQL_DATABASE_PORT'] = '3306'

mysql.init_app(app)

@app.route('/api/category/create', methods=['POST'])
def createCategory():
	try:
		if request.method == 'POST':
			data = request.get_json(force=True)
			app.logger.debug('Received data: ' + str(data))
			if 'name' in data:
				_name = str(data['name'])
				if not _name:
					return unprocessable_entity('The name must not be empty or null')
				else:
					app.logger.debug('Given category name: ' + _name)
					app.logger.debug('Connecting to MySQL database')
					app.logger.debug('Getting a cursor')
					cursor = mysql.get_db().cursor()
					app.logger.debug('Executing query')
					args = [_name]
					cursor.callproc('storingSystemCreateCategory', args) #id, createdAt, updatedAt, name, deleted
					createdCategory = cursor.fetchall()
					app.logger.debug(str(createdCategory[0]))
					jsonCategory = {
					  'id': createdCategory[0],
					  'name': createdCategory[1],
					  'deleted': createdCategory[2]
					}
					if len(createdCategory) is 0:
						return {'StatusCode':'500','Message': 'Internal error: ' + str(createdCategory[0])}
					else:
						conn.commit()
						#return {'StatusCode':'200', jsonify(jsonCategory)}
						return {'StatusCode':'200'}
			else:
				return unprocessable_entity('The name must not be empty or null')
		else:
			return not_found('Wrong HTTP method')
	except Exception as e:
		traceback.print_exc()
		return internal_server_error(e)

@app.errorhandler(404)
def not_found(error):
    message = {
		'status': 404,
		'message': 'Not Found: ' + str(error)
    }
    resp = jsonify(message)
    resp.status_code = 404
    return resp
	
@app.errorhandler(422)
def unprocessable_entity(error):
    message = {
		'status': 422,
		'message': 'Unprocessable entity: ' + str(error)
    }
    resp = jsonify(message)
    resp.status_code = 422
    return resp
	
@app.errorhandler(500)
def internal_server_error(error):
	message = None
	if mode == 'productive':
		message = {
			'status': 500,
			'message': 'Internal server error'
		}
	else:
		message = {
			'status': 500,
			'message': 'Internal server error: ' + str(error)
		}
	resp = jsonify(message)
	resp.status_code = 500
	return resp

app.debug = True
http_server = WSGIServer(('127.0.0.1', 5000), app)
http_server.serve_forever()

Do you need any more details? @methane

[methane]

Yes. Stacktrace tells everything I need:

  File "C:\Program Files (x86)\Python\lib\site-packages\pymysql\connections.py", line 964, in connect
    self.host_info = "socket %s:%d" % (self.host, self.port)
TypeError: %d format: a number is required, not str

You passed string for port argument, but it must be integer.

app.config['MYSQL_DATABASE_PORT'] = '3306'

This should be 3306, not '3306'

[SeppPenner]

@methane Thanks. I will change this and try again.

[SeppPenner]

Hi @methane: This issue is fixed, thank you 👍