Error when connecting MySQL : Forums : PythonAnywhere

Error when connecting MySQL

My code to connect to MySQL sever is as below:

engine = create_engine('mysql+mysqldb://bkcollection:pw@bkcollecton.mysql.pythonanywhere-services.com')
conn = engine.raw_connection()
df_new.to_sql(name='KLSE', con=conn, if_exists='replace')
conn.close()

however, I have this error

File "/home/bkcollecton/test_f.py", line 236, in <module>
    conn = engine.raw_connection()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1699, in raw_connection
    return self.pool.unique_connection()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 220, in unique_connection
    return _ConnectionFairy(self).checkout()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 431, in __init__
    rec = self._connection_record = pool._do_get()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 788, in _do_get
    con = self._create_connection()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 225, in _create_connection
    return _ConnectionRecord(self)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 318, in __init__
    self.connection = self.__connect()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 379, in __connect
    connection = self.__pool._creator()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/strategies.py", line 80, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 283, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/usr/local/lib/python2.7/dist-packages/MySQLdb/__init__.py", line 81, in Connect
    return Connection(*args, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/MySQLdb/connections.py", line 193, in __init__
    super(Connection, self).__init__(*args, **kwargs2)
sqlalchemy.exc.OperationalError: (OperationalError) (1045, "Access denied for user 'bkcollection'@'ip-10-61-196-73.ec2.internal' (usi
ng password: YES)") None None

deleted-user-1226766 | 51 posts | April 14, 2016, 9:54 a.m. | permalink

Your username is incorrect in the connection string:

bkcollection != bkcollecton

glenn | 10043 posts | PythonAnywhere staff | April 14, 2016, 9:59 a.m. | permalink

i have put bkcollecton but still the same error.

deleted-user-1226766 | 51 posts | April 14, 2016, 1:03 p.m. | permalink

I also notice that you're not specifying the database to connect to in your connection string. It will be something like "bkcollecton$default"

glenn | 10043 posts | PythonAnywhere staff | April 14, 2016, 2:04 p.m. | permalink

do u mean add "bkcollecton$default" at this comand

conn = engine.raw_connection(bkcollecton$default)

deleted-user-1226766 | 51 posts | April 14, 2016, 3:27 p.m. | permalink

No, like this:

engine = create_engine('mysql+mysqldb://bkcollection:pw@bkcollecton.mysql.pythonanywhere-services.com/bkcollecton$default')

otherwise sqlalchemy has no idea which database to connect to

glenn | 10043 posts | PythonAnywhere staff | April 14, 2016, 3:55 p.m. | permalink

Hi glenn, I changed the engine name as you shown (username is bkcollecton), changed the password to make sure it is correct. besides, I try to change to bkcollecton$default and bkcollecton$vindex but both has the below error this round.

    Traceback (most recent call last):
  File "/home/bkcollecton/test_f.py", line 237, in <module>
    df_new.to_sql(name='KLSE', con=conn, if_exists='replace')
  File "/usr/local/lib/python2.7/dist-packages/pandas/core/generic.py", line 950, in to_sql
    index_label=index_label)
  File "/usr/local/lib/python2.7/dist-packages/pandas/io/sql.py", line 475, in to_sql
    index_label=index_label)
  File "/usr/local/lib/python2.7/dist-packages/pandas/io/sql.py", line 1084, in to_sql
    index_label=index_label)
  File "/usr/local/lib/python2.7/dist-packages/pandas/io/sql.py", line 543, in __init__
    if self.pd_sql.has_table(self.name):
  File "/usr/local/lib/python2.7/dist-packages/pandas/io/sql.py", line 1094, in has_table
    return len(self.execute(query).fetchall()) > 0
  File "/usr/local/lib/python2.7/dist-packages/pandas/io/sql.py", line 1041, in execute
    raise_with_traceback(ex)
  File "/usr/local/lib/python2.7/dist-packages/pandas/io/sql.py", line 1030, in execute
    cur.execute(*args)
  File "/usr/local/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 205, in execute
    self.errorhandler(self, exc, value)
  File "/usr/local/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
pandas.io.sql.DatabaseError: Execution failed on sql: SELECT name FROM sqlite_master WHERE type='table' AND name='KLSE';

deleted-user-1226766 | 51 posts | April 15, 2016, 2:52 a.m. | permalink

well. it certainly seems like you are making progress. what do you think this error means?

conrad | 4232 posts | PythonAnywhere staff | April 15, 2016, 7:58 a.m. | permalink

I check on stackexchange and seems like raw_connection is no longer used.

engine = create_engine('mysql+mysqldb://bkcollecton:pw@bkcollecton.mysql.pythonanywhere-services.com/bkcollecton$vindex')
#conn = engine.raw_connection()
df_new.to_sql(name='KLSE', con=engine, if_exists='replace')
conn.close()

Thus, I change the connection to engine but error still exists.

Traceback (most recent call last):
  File "/home/bkcollecton/test_f.py", line 237, in <module>
    df_new.to_sql(name='KLSE', con=engine, if_exists='replace')
  File "/usr/local/lib/python2.7/dist-packages/pandas/core/generic.py", line 950, in to_sql
    index_label=index_label)
  File "/usr/local/lib/python2.7/dist-packages/pandas/io/sql.py", line 475, in to_sql
    index_label=index_label)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 324, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 205, in execute
    self.errorhandler(self, exc, value)
  File "/usr/local/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
sqlalchemy.exc.OperationalError: (OperationalError) (1166, "Incorrect column name 'STOCH uptrend '") '\nCREATE TABLE `KLSE` (\n\t`ind
ex` BIGINT, \n\t`Stock` TEXT, \n\t`Momentum Active` TEXT, \n\t`STOCH uptrend ` TEXT, \n\t`Golden Cross above 0` TEXT, \n\t`Golden Cro
ss below 0` TEXT, \n\t`Volume above MA50  ` TEXT, \n\t`Death Cross above 0` TEXT, \n\t`Death Cross below 0` TEXT, \n\t`No MACD ` TEXT
, \n\t`Momentum Not Active` TEXT, \n\t`STOCH no trend ` TEXT, \n\t`STOCH downtrend ` TEXT, \n\t`No momentum ` TEXT, \n\t`Volume below
 MA50  ` TEXT, \n\t`Volume changes pc  ` FLOAT, \n\t`Previous close ` FLOAT, \n\t`resistance 1 ` FLOAT, \n\t`resistance 2 ` FLOAT, \n
\t`support 1 ` FLOAT, \n\t`support 2 ` FLOAT, \n\t`52w_High` TEXT, \n\t`52w_Low` TEXT, \n\t`total pts` FLOAT, \n\ttrade TEXT, \n\t`Vi
ndex` FLOAT\n)\n\n' ()

I think still the connection issue here.

deleted-user-1226766 | 51 posts | April 15, 2016, 9:11 a.m. | permalink

still same issue after I eliminate the spacing for columns. Now I got this error. Seems like it database can not accept nan as data?I thought pandas after 0.15 should able to handle nan?

sqlalchemy.exc.OperationalError: (OperationalError) (1054, "Unknown column 'nan' in 'field list'")

deleted-user-1226766 | 51 posts | April 15, 2016, 9:29 a.m. | permalink

Double check which version of pandas you are using. If you are on python2.7, you may be on an older version because we have pinned it to avoid breaking our existing users' code.

Investigate using a virtualenv to have more control over your python package versions.

conrad | 4232 posts | PythonAnywhere staff | April 15, 2016, 4:41 p.m. | permalink