4

I have a code which looks like the following:

from sqlalchemy import create_engine
import pandas as pd

# load the CSV
df = pd.Series()
df['raw'] = pd.read_csv('./data/Iris.csv',index_col='Id')

# Connect to the mysql, and use database "datasets"
engine = create_engine('mysql://root:[email protected]')
engine.execute("USE Datasets") # select new db


# Write data
table_name = 'IRIS'
df['raw'].to_sql(table_name, engine, if_exists='append', index=False)

The data is correctly inserted into the database, and can be loaded afterwards, but the command produces an error:

> --------------------------------------------------------------------------- AttributeError                            Traceback (most recent call
> last) <ipython-input-6-cfb9b0f5c930> in <module>()
>       1 table_name = 'IRIS'
>       2 
> ----> 3 df['raw'].to_sql(table_name, engine, if_exists='append', index=False)
> 
> ~/anaconda3/lib/python3.6/site-packages/pandas/core/generic.py in
> to_sql(self, name, con, flavor, schema, if_exists, index, index_label,
> chunksize, dtype)    1360         sql.to_sql(self, name, con,
> flavor=flavor, schema=schema,    1361                   
> if_exists=if_exists, index=index, index_label=index_label,
> -> 1362                    chunksize=chunksize, dtype=dtype)    1363     1364     def to_pickle(self, path, compression='infer'):
> 
> ~/anaconda3/lib/python3.6/site-packages/pandas/io/sql.py in
> to_sql(frame, name, con, flavor, schema, if_exists, index,
> index_label, chunksize, dtype)
>     469     pandas_sql.to_sql(frame, name, if_exists=if_exists, index=index,
>     470                       index_label=index_label, schema=schema,
> --> 471                       chunksize=chunksize, dtype=dtype)
>     472 
>     473 
> 
> ~/anaconda3/lib/python3.6/site-packages/pandas/io/sql.py in
> to_sql(self, frame, name, if_exists, index, index_label, schema,
> chunksize, dtype)    1157                 table_names =
> engine.table_names(    1158                     schema=schema or
> self.meta.schema,
> -> 1159                     connection=conn,    1160                 )    1161             if name not in table_names:
> 
> ~/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/base.py in
> table_names(self, schema, connection)    2137             if not
> schema:    2138                 schema =
> self.dialect.default_schema_name
> -> 2139             return self.dialect.get_table_names(conn, schema)    2140     2141     def has_table(self, table_name, schema=None):
> 
> <string> in get_table_names(self, connection, schema, **kw)
> 
> ~/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/reflection.py
> in cache(fn, self, con, *args, **kw)
>      40     info_cache = kw.get('info_cache', None)
>      41     if info_cache is None:
> ---> 42         return fn(self, con, *args, **kw)
>      43     key = (
>      44         fn.__name__,
> 
> ~/anaconda3/lib/python3.6/site-packages/sqlalchemy/dialects/mysql/base.py
> in get_table_names(self, connection, schema, **kw)    1954            
> rp = connection.execute(    1955                 "SHOW FULL TABLES
> FROM %s" %
> -> 1956                 self.identifier_preparer.quote_identifier(current_schema))    1957    
> 1958             return [row[0]
> 
> ~/anaconda3/lib/python3.6/site-packages/sqlalchemy/sql/compiler.py in
> quote_identifier(self, value)    3021     3022         return
> self.initial_quote + \
> -> 3023             self._escape_identifier(value) + \    3024             self.final_quote    3025 
> 
> ~/anaconda3/lib/python3.6/site-packages/sqlalchemy/sql/compiler.py in
> _escape_identifier(self, value)    2999         """    3000 
> -> 3001         value = value.replace(self.escape_quote, self.escape_to_quote)    3002         if self._double_percents:   
> 3003             value = value.replace('%', '%%')
> 
> AttributeError: 'NoneType' object has no attribute 'replace'

I've enabled logging on the SQL, and the log states the following:

Time                 Id Command    Argument
2018-01-12T18:13:43.116036Z    20 Query SET global log_output = 'file'
2018-01-12T18:13:44.291677Z    20 Query SET global general_log = on
2018-01-12T18:14:15.861927Z    19 Query DESCRIBE `IRIS`
2018-01-12T18:14:15.864129Z    19 Query rollback
2018-01-12T18:14:15.869620Z    19 Query INSERT INTO `IRIS` (`SepalLengthCm`, `SepalWidthCm`, `PetalLengthCm`, `PetalWidthCm`, `Species`) VALUES (5.1, 3.5, 1.4, 0.2, 'Iris-setosa'),(4.9, 3, 1.4, 0.2, 'Iris-setosa'),(4.7, 3.2, 1.3, 0.2, 'Iris-setosa'),(4.6, 3.1, 1.5, 0.2, 'Iris-setosa'),(5, 3.6, 1.4, 0.2, 'Iris-setosa'),(5.4, 3.9, 1.7, 0.4, 'Iris-setosa'),(4.6, 3.4, 1.4, 0.3, 'Iris-setosa'),(5, 3.4, 1.5, 0.2, 'Iris-setosa'),(4.4, 2.9, 1.4, 0.2, 'Iris-setosa'),(4.9, 3.1, 1.5, 0.1, 'Iris-setosa'),(5.4, 3.7, 1.5, 0.2, 'Iris-setosa'),(4.8, 3.4, 1.6, 0.2, 'Iris-setosa'),(4.8, 3, 1.4, 0.1, 'Iris-setosa'),(4.3, 3, 1.1, 0.1, 'Iris-setosa'),(5.8, 4, 1.2, 0.2, 'Iris-setosa'),(5.7, 4.4, 1.5, 0.4, 'Iris-setosa'),(5.4, 3.9, 1.3, 0.4, 'Iris-setosa'),(5.1, 3.5, 1.4, 0.3, 'Iris-setosa'),(5.7, 3.8, 1.7, 0.3, 'Iris-setosa'),(5.1, 3.8, 1.5, 0.3, 'Iris-setosa'),(5.4, 3.4, 1.7, 0.2, 'Iris-setosa'),(5.1, 3.7, 1.5, 0.4, 'Iris-setosa'),(4.6, 3.6, 1, 0.2, 'Iris-setosa'),(5.1, 3.3, 1.7, 0.5, 'Iris-setosa'),(4.8, 3.4, 1.9, 0.2, 'Iris-setosa'),(5, 3, 1.6, 0.2, 'Iris-setosa'),(5, 3.4, 1.6, 0.4, 'Iris-setosa'),(5.2, 3.5, 1.5, 0.2, 'Iris-setosa'),(5.2, 3.4, 1.4, 0.2, 'Iris-setosa'),(4.7, 3.2, 1.6, 0.2, 'Iris-setosa'),(4.8, 3.1, 1.6, 0.2, 'Iris-setosa'),(5.4, 3.4, 1.5, 0.4, 'Iris-setosa'),(5.2, 4.1, 1.5, 0.1, 'Iris-setosa'),(5.5, 4.2, 1.4, 0.2, 'Iris-setosa'),(4.9, 3.1, 1.5, 0.1, 'Iris-setosa'),(5, 3.2, 1.2, 0.2, 'Iris-setosa'),(5.5, 3.5, 1.3, 0.2, 'Iris-setosa'),(4.9, 3.1, 1.5, 0.1, 'Iris-setosa'),(4.4, 3, 1.3, 0.2, 'Iris-setosa'),(5.1, 3.4, 1.5, 0.2, 'Iris-setosa'),(5, 3.5, 1.3, 0.3, 'Iris-setosa'),(4.5, 2.3, 1.3, 0.3, 'Iris-setosa'),(4.4, 3.2, 1.3, 0.2, 'Iris-setosa'),(5, 3.5, 1.6, 0.6, 'Iris-setosa'),(5.1, 3.8, 1.9, 0.4, 'Iris-setosa'),(4.8, 3, 1.4, 0.3, 'Iris-setosa'),(5.1, 3.8, 1.6, 0.2, 'Iris-setosa'),(4.6, 3.2, 1.4, 0.2, 'Iris-setosa'),(5.3, 3.7, 1.5, 0.2, 'Iris-setosa'),(5, 3.3, 1.4, 0.2, 'Iris-setosa'),(7, 3.2, 4.7, 1.4, 'Iris-versicolor'),(6.4, 3.2, 4.5, 1.5, 'Iris-versicolor'),(6.9, 3.1, 4.9, 1.5, 'Iris-versicolor'),(5.5, 2.3, 4, 1.3, 'Iris-versicolor'),(6.5, 2.8, 4.6, 1.5, 'Iris-versicolor'),(5.7, 2.8, 4.5, 1.3, 'Iris-versicolor'),(6.3, 3.3, 4.7, 1.6, 'Iris-versicolor'),(4.9, 2.4, 3.3, 1, 'Iris-versicolor'),(6.6, 2.9, 4.6, 1.3, 'Iris-versicolor'),(5.2, 2.7, 3.9, 1.4, 'Iris-versicolor'),(5, 2, 3.5, 1, 'Iris-versicolor'),(5.9, 3, 4.2, 1.5, 'Iris-versicolor'),(6, 2.2, 4, 1, 'Iris-versicolor'),(6.1, 2.9, 4.7, 1.4, 'Iris-versicolor'),(5.6, 2.9, 3.6, 1.3, 'Iris-versicolor'),(6.7, 3.1, 4.4, 1.4, 'Iris-versicolor'),(5.6, 3, 4.5, 1.5, 'Iris-versicolor'),(5.8, 2.7, 4.1, 1, 'Iris-versicolor'),(6.2, 2.2, 4.5, 1.5, 'Iris-versicolor'),(5.6, 2.5, 3.9, 1.1, 'Iris-versicolor'),(5.9, 3.2, 4.8, 1.8, 'Iris-versicolor'),(6.1, 2.8, 4, 1.3, 'Iris-versicolor'),(6.3, 2.5, 4.9, 1.5, 'Iris-versicolor'),(6.1, 2.8, 4.7, 1.2, 'Iris-versicolor'),(6.4, 2.9, 4.3, 1.3, 'Iris-versicolor'),(6.6, 3, 4.4, 1.4, 'Iris-versicolor'),(6.8, 2.8, 4.8, 1.4, 'Iris-versicolor'),(6.7, 3, 5, 1.7, 'Iris-versicolor'),(6, 2.9, 4.5, 1.5, 'Iris-versicolor'),(5.7, 2.6, 3.5, 1, 'Iris-versicolor'),(5.5, 2.4, 3.8, 1.1, 'Iris-versicolor'),(5.5, 2.4, 3.7, 1, 'Iris-versicolor'),(5.8, 2.7, 3.9, 1.2, 'Iris-versicolor'),(6, 2.7, 5.1, 1.6, 'Iris-versicolor'),(5.4, 3, 4.5, 1.5, 'Iris-versicolor'),(6, 3.4, 4.5, 1.6, 'Iris-versicolor'),(6.7, 3.1, 4.7, 1.5, 'Iris-versicolor'),(6.3, 2.3, 4.4, 1.3, 'Iris-versicolor'),(5.6, 3, 4.1, 1.3, 'Iris-versicolor'),(5.5, 2.5, 4, 1.3, 'Iris-versicolor'),(5.5, 2.6, 4.4, 1.2, 'Iris-versicolor'),(6.1, 3, 4.6, 1.4, 'Iris-versicolor'),(5.8, 2.6, 4, 1.2, 'Iris-versicolor'),(5, 2.3, 3.3, 1, 'Iris-versicolor'),(5.6, 2.7, 4.2, 1.3, 'Iris-versicolor'),(5.7, 3, 4.2, 1.2, 'Iris-versicolor'),(5.7, 2.9, 4.2, 1.3, 'Iris-versicolor'),(6.2, 2.9, 4.3, 1.3, 'Iris-versicolor'),(5.1, 2.5, 3, 1.1, 'Iris-versicolor'),(5.7, 2.8, 4.1, 1.3, 'Iris-versicolor'),(6.3, 3.3, 6, 2.5, 'Iris-virginica'),(5.8, 2.7, 5.1, 1.9, 'Iris-virginica'),(7.1, 3, 5.9, 2.1, 'Iris-virginica'),(6.3, 2.9, 5.6, 1.8, 'Iris-virginica'),(6.5, 3, 5.8, 2.2, 'Iris-virginica'),(7.6, 3, 6.6, 2.1, 'Iris-virginica'),(4.9, 2.5, 4.5, 1.7, 'Iris-virginica'),(7.3, 2.9, 6.3, 1.8, 'Iris-virginica'),(6.7, 2.5, 5.8, 1.8, 'Iris-virginica'),(7.2, 3.6, 6.1, 2.5, 'Iris-virginica'),(6.5, 3.2, 5.1, 2, 'Iris-virginica'),(6.4, 2.7, 5.3, 1.9, 'Iris-virginica'),(6.8, 3, 5.5, 2.1, 'Iris-virginica'),(5.7, 2.5, 5, 2, 'Iris-virginica'),(5.8, 2.8, 5.1, 2.4, 'Iris-virginica'),(6.4, 3.2, 5.3, 2.3, 'Iris-virginica'),(6.5, 3, 5.5, 1.8, 'Iris-virginica'),(7.7, 3.8, 6.7, 2.2, 'Iris-virginica'),(7.7, 2.6, 6.9, 2.3, 'Iris-virginica'),(6, 2.2, 5, 1.5, 'Iris-virginica'),(6.9, 3.2, 5.7, 2.3, 'Iris-virginica'),(5.6, 2.8, 4.9, 2, 'Iris-virginica'),(7.7, 2.8, 6.7, 2, 'Iris-virginica'),(6.3, 2.7, 4.9, 1.8, 'Iris-virginica'),(6.7, 3.3, 5.7, 2.1, 'Iris-virginica'),(7.2, 3.2, 6, 1.8, 'Iris-virginica'),(6.2, 2.8, 4.8, 1.8, 'Iris-virginica'),(6.1, 3, 4.9, 1.8, 'Iris-virginica'),(6.4, 2.8, 5.6, 2.1, 'Iris-virginica'),(7.2, 3, 5.8, 1.6, 'Iris-virginica'),(7.4, 2.8, 6.1, 1.9, 'Iris-virginica'),(7.9, 3.8, 6.4, 2, 'Iris-virginica'),(6.4, 2.8, 5.6, 2.2, 'Iris-virginica'),(6.3, 2.8, 5.1, 1.5, 'Iris-virginica'),(6.1, 2.6, 5.6, 1.4, 'Iris-virginica'),(7.7, 3, 6.1, 2.3, 'Iris-virginica'),(6.3, 3.4, 5.6, 2.4, 'Iris-virginica'),(6.4, 3.1, 5.5, 1.8, 'Iris-virginica'),(6, 3, 4.8, 1.8, 'Iris-virginica'),(6.9, 3.1, 5.4, 2.1, 'Iris-virginica'),(6.7, 3.1, 5.6, 2.4, 'Iris-virginica'),(6.9, 3.1, 5.1, 2.3, 'Iris-virginica'),(5.8, 2.7, 5.1, 1.9, 'Iris-virginica'),(6.8, 3.2, 5.9, 2.3, 'Iris-virginica'),(6.7, 3.3, 5.7, 2.5, 'Iris-virginica'),(6.7, 3, 5.2, 2.3, 'Iris-virginica'),(6.3, 2.5, 5, 1.9, 'Iris-virginica'),(6.5, 3, 5.2, 2, 'Iris-virginica'),(6.2, 3.4, 5.4, 2.3, 'Iris-virginica'),(5.9, 3, 5.1, 1.8, 'Iris-virginica')
2018-01-12T18:14:15.879509Z    19 Query commit
2018-01-12T18:14:15.881477Z    19 Query rollback
2018-01-12T18:14:15.881695Z    19 Query rollback

I see that the problem is when SQLalchemy is trying to get the name of the current database. Is it possible to either fix the error, or to stop the API from making the call? my only problem is the program crashing due to an error, as the data is actually in the table.

Improve this question

1 Answer 1

Reset to default
5

I found the root cause of the problem to be the table_names method to return an empty list.

len(engine.table_names())
0

I changed this:

# Connect to the mysql, and use database "datasets"
######### wrong part ##########
#engine = create_engine('mysql://root:[email protected]')
#engine.execute("USE Datasets") # select new db
######### Correct code ########
engine = create_engine('mysql+mysqlconnector://root:[email protected]/Datasets')

this way the engine knows which database to return the tables from.

the length of table_names is now correct:

len(engine.table_names())
1
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

saved me a bunch of time! thanks for posting your answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.