AttributeError: 'Series' object has no attribute 'translate'

Question

I'm developing a small CRM system by flask. Now there is an error when I test import_data.py by postman. Really have no idea how to fix this issue....I've tested to insert the data into MySQL DB manually and it worked. I am not sure which part cause the problem, but I think the issue should be at app.py, or import_data.py, or the way I test under Postman. Here is the structure of my project.

structure of project

here is coding for app.py

# -*- coding:utf-8 -*-
import pandas as pd
from flask import Flask, request
import logging; logging.basicConfig(level=logging.INFO)
import asyncio, os, json, time
from datetime import datetime
from util.template import ReponseTemplate
from aiohttp import web
from api import import_data, statistic, general_search
import sys
import traceback
from werkzeug.utils import secure_filename

# UPLOAD_FOLDER = '/path/to/the/uploads'
ALLOWED_EXTENSIONS = set(['xlsx', 'xls', 'csv'])
app = Flask(__name__)
# app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER
app.config['MAX_CONTENT_LENGTH'] = 60 * 1024 * 1024

app.register_blueprint(import_data.mold, url_prefix='/api/import')
app.register_blueprint(general_search.mold, url_prefix='/api/show')
app.register_blueprint(statistic.mold, url_prefix='/api/check')


def allowed_file(filename):
    return '.' in filename and \
        filename.rsplit('.', 1)[1] in ALLOWED_EXTENSIONS


@app.route('/upload', methods=['POST'])
def upload_excel():
    f = request.files['file']
    if f and allowed_file(f.filename):
        filename = secure_filename(f.filename)
        f.save(secure_filename(filename))
        # file.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
        df = pd.read_excel(f)
        return ReponseTemplate().jsonify_ok_df_response(df)


@app.errorhandler(Exception)
def default_exception_handler(error):
    exc_type, exc_value, exc_traceback = sys.exc_info()
    trace = traceback.format_exception(exc_type, exc_value,
                                       exc_traceback)
    code = 401 if 'auth' in str(type(error)).lower() else 400
    print('[Error]:{}'.format(trace))

    return ReponseTemplate().jsonify_bad_response(str(error), code)




if __name__ == '__main__':

    app.run(debug=True)

here is coding for template.py

import math

import simplejson as json
from flask import jsonify


class ResponseTemplate():
    response_template = {
        "success": "Success",
        "data": [],
        "meta": {
            "code": 200,
            "message": "OK",
            "errors": [],
            "pages": {}
        }
    }

    def jsonify_ok_row_response(self, rows, page_index=1, page_size=10, total=0):
        self.response_template['meta']['errors'] = []
        self.response_template['meta']['code'] = 200

        if rows is None:
            self.response_template['data'] = []
        else:
            json_result = [{key.lower(): value for (key, value) in row.items()} for row in rows]
            self.response_template['data'] = json_result

        pages = {"limit": 1, "page": 1, "total": 0, "count": 0}
        pages['limit'] = page_size
        pages['page'] = page_index
        pages['count'] = int(math.ceil(float(total) / page_size))
        pages['total'] = total if total > 0 else len(self.response_template['data'])
        self.response_template['meta']['pages'] = pages

        return jsonify(self.response_template)

    def jsonify_ok_list_response(self, data, page_index=1, page_size=10, total=0):
        self.response_template['meta']['errors'] = []
        self.response_template['meta']['code'] = 200

        if data is None:
            self.response_template['data'] = []
        else:
            json_result = [{key.lower(): value for (key, value) in d.items()} for d in data]
            self.response_template['data'] = json_result

        pages = {"limit": 1, "page": 1, "total": 0, "count": 0}
        pages['limit'] = page_size
        pages['page'] = page_index
        pages['count'] = int(math.ceil(float(total) / page_size))
        pages['total'] = total if total > 0 else len(self.response_template['data'])
        self.response_template['meta']['pages'] = pages

        return jsonify(self.response_template)

    def jsonify_ok_str_response(self, json_str, page_index=1, page_size=10, total=0):
        self.response_template['meta']['errors'] = []
        self.response_template['meta']['code'] = 200

        if json_str is None or json_str == '':
            self.response_template['data'] = []
        else:
            json_result = json.loads(json_str)
            self.response_template['data'] = json_result

        pages = {"limit": 1, "page": 1, "total": 0, "count": 0}
        pages['limit'] = page_size
        pages['page'] = page_index
        pages['count'] = int(math.ceil(float(total) / page_size))
        pages['total'] = total if total > 0 else len(self.response_template['data'])
        self.response_template['meta']['pages'] = pages

        return jsonify(self.response_template)

    def jsonify_ok_obj_response(self, obj, page_index=1, page_size=10, total=0):
        self.response_template['meta']['errors'] = []
        self.response_template['meta']['code'] = 200

        if obj is None:
            self.response_template['data'] = []
        else:
            self.response_template['data'] = obj

        pages = {"limit": 1, "page": 1, "total": 0, "count": 0}
        pages['limit'] = page_size
        pages['page'] = page_index
        pages['count'] = int(math.ceil(float(total) / page_size))
        pages['total'] = total if total > 0 else len(self.response_template['data'])
        self.response_template['meta']['pages'] = pages

        return jsonify(self.response_template)

    def jsonify_ok_df_response(self, df, page_index=1, page_size=10, total=0, orient='records'):
        self.response_template['meta']['errors'] = []
        self.response_template['meta']['code'] = 200

        if df is None:
            self.response_template['data'] = []
        else:
            json_str = df.to_json(orient=orient)
            json_result = json.loads(json_str)
            self.response_template['data'] = json_result

        pages = {"limit": 1, "page": 1, "total": 0, "count": 0}
        pages['limit'] = page_size
        pages['page'] = page_index
        pages['count'] = int(math.ceil(float(total) / page_size))
        pages['total'] = total if total > 0 else len(self.response_template['data'])
        self.response_template['meta']['pages'] = pages

        return jsonify(self.response_template)

    def jsonify_bad_response(self, errors, code):
        self.response_template['meta']['errors'] = errors
        self.response_template['meta']['code'] = code
        self.response_template['data'] = []
        self.response_template['meta']['pages'] = {}
        return jsonify(self.response_template)

here is import_data.py

# -*- coding:utf-8 -*-

import json
from flask import Blueprint, request
from util.template import ResponseTemplate
import logging
from model.dbmodel import Customers, Transaction
from sqlalchemy.orm import mapper, sessionmaker
from sqlalchemy import create_engine
import config
import pandas as pd
from sqlalchemy.ext.declarative import declarative_base


mold = Blueprint('import', __name__)

conn = config.conn_str
Base = declarative_base()


@mold.route('/customer', methods=['POST'])
def import_customer():
    engine = create_engine(conn)
    # df = pd.read_excel(request.data)
    cum = json.loads(request.data)
    if cum is None:
        logging.info("Not able to get the data from request.")
    # Customers.name = cum.get('name', '')
    # Customers.address = cum.get('address', '')
    # Customers.phone = cum.get('phone', '')
    # Customers.source_from = cum.get('source_from', '')
    name = cum.get('name', '')
    address = cum.get('address', '')
    phone = cum.get('phone', '')
    source_from = cum.get('source_from', '')

    Base.metadata.create_all(engine)

    Session_class = sessionmaker(bind=engine)  
    session = Session_class() 
    # generate the object for the data we would like to insert
    customer_obj = Customers(name=name, address=address,
                             phone=phone, source_from=source_from)
    # nothing yet, print to check
    print(customer_obj.id, customer_obj.name,
          customer_obj.phone, customer_obj.address,
          customer_obj.source_from)

    session.add(customer_obj)  # put the data obj into session, will insert together
    # check again. but still nothing yet....
    print(customer_obj.id, customer_obj.name,
          customer_obj.phone, customer_obj.address,
          customer_obj.source_from)
    session.commit()  # insert the data into database
    return ResponseTemplate.jsonify_ok_obj_response(customer_obj)


@mold.route('/transactions/<file_name>', methods=['POST'])
def import_transactions(file_name):
    engine = create_engine(conn)

    #df = pd.read_excel(request.data)
    #tran = json.loads(request.data)
    tran = pd.read_excel(file_name)
    if tran is None:
        logging.info("Not able to get the data from file.")
    name = tran.get('name', '')
    # print(name)
    date = tran.get('date', '')
    # print(date)
    product = tran.get('product', '')
    # print(product)
    quantity = tran.get('quantity', '')
    amount = tran.get('amount', '')

    Base.metadata.create_all(engine)

    Session_class = sessionmaker(bind=engine)  
    session = Session_class()  
    # generate the object for the data we would like to insert

    transaction_obj = Transaction(name=name, date=date, product=product,
                                  quantity=quantity, amount=amount)
    # print to check, there should be nothing yet
    print(transaction_obj.name, transaction_obj.product, transaction_obj.date,
          transaction_obj.quantity, transaction_obj.amount)
    session.add(transaction_obj)  # put the data into obj
    # check again, there should be nothing still
    print(transaction_obj.name, transaction_obj.product, transaction_obj.date,
          transaction_obj.quantity, transaction_obj.amount)
    session.commit()  # insert
    return ResponseTemplate.jsonify_ok_obj_response(transaction_obj)

here is coding for two tables in DB: dbmodel.py

# -*- coding:utf-8 -*-

from sqlalchemy import Table, MetaData, Column, Integer, String, DATE, DECIMAL, ForeignKey, DateTime
from sqlalchemy.orm import mapper
from sqlalchemy.ext.declarative import declarative_base

#metadata = MetaData()
Base = declarative_base()

# customers = Table('customers', metadata,
#                   Column('id', Integer, primary_key=True, autoincrement=True),
#                   Column('name', String(20)),
#                   Column('phone', String(20)),
#                   Column('address', String(45)),
#                   Column('source_from', String(45))
#                   )


class Customers(Base):
    # def __init__(self, id, name, phone, address, source_from):
    #     self.id = id
    #     self.name = name
    #     self.phone = phone
    #     self.address = address
    #     self.source_from = source_from
    #
    # def __repr__(self):
    #     return "<Customer(name='%s', phone='%s', address='%s', " \
    #            "source_from='%s')" % (self.name, self.phone, self.address,
    #                                   self.source_from)
    __tablename__ = 'customers'
    id = Column(Integer, primary_key=True, autoincrement=True, nullable=False)
    name = Column(String(20))
    phone = Column(String(20))
    address = Column(String(45))
    source_from = Column(String(45))

# mapper(Customers, customers)


# the table metadata is created separately with the Table construct,
# then associated with the User class via the mapper() function


# transaction = Table('transaction', metadata,
#                     Column('id', Integer, primary_key=True, autoincrement=True),
#                     Column('name', String(20)),
#                     Column('date', DateTime),
#                     Column('product', String(20)),
#                     Column('quantity', Integer),
#                     Column('amount', DECIMAL(2))
#                     )


class Transaction(Base):
    # def __init__(self, id, name, date, product, quantity, amount):
    #     self.id = id
    #     self.name = name
    #     self.date = date
    #     self.product = product
    #     self.quantity = quantity
    #     self.amount = amount
    #
    # def __repr__(self):
    #     return "<Transaction(name='%s', date='%s', product='%s'," \
    #            "quantity='%s', amount='%s')>" % (self.name, self.date,
    #                                              self.product, self.quantity,
    #                                              self.amount)
    __tablename__ = 'transaction'
    id = Column(Integer, primary_key=True, autoincrement=True, nullable=False)
    name = Column(String(20))
    date = Column(DATE)
    product = Column(String(20))
    quantity = Column(Integer)
    amount = Column(Integer)

# mapper(Transaction, transaction)

below link is the screenshot of postman result after I test the api:

postman test result

here are the error messages from my pycharm after I test the api with postman:

>          [Error]:['Traceback (most recent call last):\n', '  File 
>          "/Applications/anaconda3/envs/Fruit/lib/python3.6/site-packages/flask/app.py",
> line 1813, in full_dispatch_request\n    rv =
> self.dispatch_request()\n', 
>     '  File "/Applications/anaconda3/envs/Fruit/lib/python3.6/site-packages/flask/app.py",
> line 1799, in dispatch_request\n     return
> self.view_functions[rule.endpoint](**req.view_args)\n', '  File
> "/Users/chenneyhuang/PycharmProjects/Fruit/api/import_data.py", line
> 91, in import_transactions\n    session.commit()  # insert\n', '  File
> "/Applications/anaconda3/envs/Fruit/lib/python3.6/site-packages/sqlalchemy/orm/session.py",
> line 943, in commit\n    self.transaction.commit()\n', '  File
> "/Applications/anaconda3/envs/Fruit/lib/python3.6/site-packages/sqlalchemy/orm/session.py",
> line 467, in commit\n    self._prepare_impl()\n', '  File
> "/Applications/anaconda3/envs/Fruit/lib/python3.6/site-packages/sqlalchemy/orm/session.py",
> line 447, in _prepare_impl\n    self.session.flush()\n', '  File
> "/Applications/anaconda3/envs/Fruit/lib/python3.6/site-packages/sqlalchemy/orm/session.py",
> line 2254, in flush\n    self._flush(objects)\n', '  File
> "/Applications/anaconda3/envs/Fruit/lib/python3.6/site-packages/sqlalchemy/orm/session.py",
> line 2380, in _flush\n   
> transaction.rollback(_capture_exception=True)\n', '  File
> "/Applications/anaconda3/envs/Fruit/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py",
> line 66, in __exit__\n    compat.reraise(exc_type, exc_value,
> exc_tb)\n', '  File
> "/Applications/anaconda3/envs/Fruit/lib/python3.6/site-packages/sqlalchemy/util/compat.py",
> line 249, in reraise\n    raise value\n', '  File
> "/Applications/anaconda3/envs/Fruit/lib/python3.6/site-packages/sqlalchemy/orm/session.py",
> line 2344, in _flush\n    flush_context.execute()\n', '  File
> "/Applications/anaconda3/envs/Fruit/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py",
> line 391, in execute\n    rec.execute(self)\n', '  File
> "/Applications/anaconda3/envs/Fruit/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py",
> line 556, in execute\n    uow\n', '  File
> "/Applications/anaconda3/envs/Fruit/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py",
> line 181, in save_obj\n    mapper, table, insert)\n', '  File
> "/Applications/anaconda3/envs/Fruit/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py",
> line 866, in _emit_insert_statements\n    execute(statement,
> params)\n', '  File
> "/Applications/anaconda3/envs/Fruit/lib/python3.6/site-packages/sqlalchemy/engine/base.py",
> line 948, in execute\n    return meth(self, multiparams, params)\n', '
> File
> "/Applications/anaconda3/envs/Fruit/lib/python3.6/site-packages/sqlalchemy/sql/elements.py",
> line 269, in _execute_on_connection\n    return
> connection._execute_clauseelement(self, multiparams, params)\n', ' 
> File
> "/Applications/anaconda3/envs/Fruit/lib/python3.6/site-packages/sqlalchemy/engine/base.py",
> line 1060, in _execute_clauseelement\n    compiled_sql,
> distilled_params\n', '  File
> "/Applications/anaconda3/envs/Fruit/lib/python3.6/site-packages/sqlalchemy/engine/base.py",
> line 1200, in _execute_context\n    context)\n', '  File
> "/Applications/anaconda3/envs/Fruit/lib/python3.6/site-packages/sqlalchemy/engine/base.py",
> line 1416, in _handle_dbapi_exception\n    util.reraise(*exc_info)\n',
> '  File
> "/Applications/anaconda3/envs/Fruit/lib/python3.6/site-packages/sqlalchemy/util/compat.py",
> line 249, in reraise\n    raise value\n', '  File
> "/Applications/anaconda3/envs/Fruit/lib/python3.6/site-packages/sqlalchemy/engine/base.py",
> line 1193, in _execute_context\n    context)\n', '  File
> "/Applications/anaconda3/envs/Fruit/lib/python3.6/site-packages/sqlalchemy/engine/default.py",
> line 509, in do_execute\n    cursor.execute(statement, parameters)\n',
> '  File
> "/Applications/anaconda3/envs/Fruit/lib/python3.6/site-packages/pymysql/cursors.py",
> line 168, in execute\n    query = self.mogrify(query, args)\n', ' 
> File
> "/Applications/anaconda3/envs/Fruit/lib/python3.6/site-packages/pymysql/cursors.py",
> line 147, in mogrify\n    query = query % self._escape_args(args,
> conn)\n', '  File
> "/Applications/anaconda3/envs/Fruit/lib/python3.6/site-packages/pymysql/cursors.py",
> line 127, in _escape_args\n    return dict((key, conn.literal(val))
> for (key, val) in args.items())\n', '  File
> "/Applications/anaconda3/envs/Fruit/lib/python3.6/site-packages/pymysql/cursors.py",
> line 127, in <genexpr>\n    return dict((key, conn.literal(val)) for
> (key, val) in args.items())\n', '  File
> "/Applications/anaconda3/envs/Fruit/lib/python3.6/site-packages/pymysql/connections.py",
> line 469, in literal\n    return self.escape(obj, self.encoders)\n', '
> File
> "/Applications/anaconda3/envs/Fruit/lib/python3.6/site-packages/pymysql/connections.py",
> line 462, in escape\n    return converters.escape_item(obj,
> self.charset, mapping=mapping)\n', '  File
> "/Applications/anaconda3/envs/Fruit/lib/python3.6/site-packages/pymysql/converters.py",
> line 27, in escape_item\n    val = encoder(val, mapping)\n', '  File
> "/Applications/anaconda3/envs/Fruit/lib/python3.6/site-packages/pymysql/converters.py",
> line 118, in escape_unicode\n    return u"\'%s\'" %
> _escape_unicode(value)\n', '  File "/Applications/anaconda3/envs/Fruit/lib/python3.6/site-packages/pymysql/converters.py",
> line 73, in _escape_unicode\n    return
> value.translate(_escape_table)\n', '  File
> "/Applications/anaconda3/envs/Fruit/lib/python3.6/site-packages/pandas/core/generic.py",
> line 4376, in __getattr__\n    return object.__getattribute__(self,
> name)\n', "AttributeError: 'Series' object has no attribute
> 'translate'\n"]

I'm using python3.6. Here are the package details:

aiohttp==3.3.2
aiomysql==0.0.19
asn1crypto==0.24.0
async-timeout==3.0.0
attrs==18.1.0
cffi==1.11.5
chardet==3.0.4
click==6.7
cryptography==2.2.2
Flask==1.0.2
idna==2.7
idna-ssl==1.1.0
itsdangerous==0.24
Jinja2==2.10
MarkupSafe==1.0
multidict==4.3.1
numpy==1.15.0
pandas==0.23.4
pycparser==2.18
PyMySQL==0.9.2
python-dateutil==2.7.3
pytz==2018.5
simplejson==3.16.0
six==1.11.0
SQLAlchemy==1.2.10
Werkzeug==0.14.1
xlrd==1.1.0
yarl==1.2.6

Answer 1

This happens because you passed an object from pandas to an operation that expects plain strings (it happens to be a Series object, as the stack trace suggests).

Look at this sequence:

tran = pd.read_excel(file_name)
name = tran.get('name', '')

tran here is no ordinary Python dict or list, applying indexing to it (with tran[x]) or .get(), etc. returns complex data structures, not strings - even if the original input data (in the excel file) is strings.

If your .get('key') operation selects a single value from the dataframe that is of type str, you need to convert it to a Python string explicitly, you cannot just give it to a database function that wants strings and expect it magically to become a string:

name = str ( tran.get('name', '' ) )

I suspect that it would actually give you a column of values, though - you'd need more than a few changes in the code to handle that.

(BTW, if you just need a simple conversion from XLS or CSV to Python data, pandas seems like one heavy monster to use for this. For CSV, there's a native Python library; though off the top of my head, I don't have a converter for XLS that I can recommend now).