Access denied for user error

[shinriyo]

Hi there

I use sqlalchemy and Python3.2 and pymysql on Windows7.

I tried below

from sqlalchemy import *

engine = create_engine('mysql+pymysql://teaspoon:teaspoon@localhost/teaspoon')
metadata = MetaData()
t = Table('mytable', metadata,
    Column('mytable_id', Integer, primary_key=True)
)
metadata.create_all(engine)

But, error occurd

sqlalchemy.exc.InternalError: (InternalError) (1045, "#28000Access denied for user 'teaspoon'@'localhost' (using password: YES)") None None

So, I tried via console. (DOSprompt)

It it connect? => It is OK!

c:\xampp\mysql\bin\mysql.exe -u teaspoon -p

Can user use database? => It is OK!

mysql> use teaspoon
Database changed

Can user create? => It is OK! 

mysql> CREATE TABLE teaspoon(id INT(11)
Query OK, 0 rows affected (0.07 sec)

I have no idea.

Would you tell me why mysql deny?

[shinriyo]

I tried sqlit. it is fine.

engine = create_engine('sqlite+pysqlite:///file.db')

but, MySQL can't connect.

2012年3月21日水曜日12時46分23秒 UTC+9 shinriyo:

2012年3月21日水曜日12時46分23秒 UTC+9 shinriyo:

[Michael Bayer]

it seems like you connect without a host using mysql.exe, so try:

create_engine('mysql+pymysql://teaspoon:teaspoon@/teaspoon')

otherwise you need to add an entry for 'teaspoon'@'localhost' to your user table:

http://dev.mysql.com/doc/refman/5.0/en/connection-access.html

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/F9BC0dTCw8oJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[shinriyo]

Hi Michael Bayer 

Thank you for your help.

I tried to change create_engine args you suggested below.

create_engine('mysql+pymysql://teaspoon:teaspoon@/teaspoon') 

But, same problem was happened.

So, I create "user table" like the URL you tought me.

---

CREATE TABLE user

(

user VARCHAR(64),

host VARCHAR(64)

);

INSERT INTO user VALUE ('teaspoon', 'localhost');

---

But, I couldn't resolve.

[shinriyo]

Mac version is also strange

(fb_env)shinriyo:teaspoon shinriyo$ python . create

=> 20:46:34 : creating the database.

2012-03-22 20:46:34,905 INFO sqlalchemy.engine.base.Engine b'SELECT DATABASE()'

2012-03-22 20:46:34,905 INFO sqlalchemy.engine.base.Engine ()

2012-03-22 20:46:34,910 INFO sqlalchemy.engine.base.Engine b"SHOW VARIABLES LIKE 'character_set%%'"

2012-03-22 20:46:34,910 INFO sqlalchemy.engine.base.Engine ()

2012-03-22 20:46:34,913 INFO sqlalchemy.engine.base.Engine b"SHOW VARIABLES LIKE 'lower_case_table_names'"

2012-03-22 20:46:34,913 INFO sqlalchemy.engine.base.Engine ()

2012-03-22 20:46:34,915 INFO sqlalchemy.engine.base.Engine b'SHOW COLLATION'

2012-03-22 20:46:34,915 INFO sqlalchemy.engine.base.Engine ()

2012-03-22 20:46:34,949 INFO sqlalchemy.engine.base.Engine b"SHOW VARIABLES LIKE 'sql_mode'"

2012-03-22 20:46:34,949 INFO sqlalchemy.engine.base.Engine ()

2012-03-22 20:46:34,952 INFO sqlalchemy.engine.base.Engine b'DESCRIBE `permissions`'

2012-03-22 20:46:34,953 INFO sqlalchemy.engine.base.Engine ()

2012-03-22 20:46:34,960 INFO sqlalchemy.engine.base.Engine b'DESCRIBE `users`'

2012-03-22 20:46:34,960 INFO sqlalchemy.engine.base.Engine ()

2012-03-22 20:46:34,964 INFO sqlalchemy.engine.base.Engine ROLLBACK

Traceback (most recent call last):

  File "/Users/shinriyo/ve/fb_env/lib/python3.2/site-packages/pymysql/cursors.py", line 103, in execute

    result = self._query(query)

  File "/Users/shinriyo/ve/fb_env/lib/python3.2/site-packages/pymysql/cursors.py", line 221, in _query

    conn.query(q)

  File "/Users/shinriyo/ve/fb_env/lib/python3.2/site-packages/pymysql/connections.py", line 599, in query

    self._affected_rows = self._read_query_result()

  File "/Users/shinriyo/ve/fb_env/lib/python3.2/site-packages/pymysql/connections.py", line 685, in _read_query_result

    result.read()

  File "/Users/shinriyo/ve/fb_env/lib/python3.2/site-packages/pymysql/connections.py", line 867, in read

    self.first_packet = self.connection.read_packet()

  File "/Users/shinriyo/ve/fb_env/lib/python3.2/site-packages/pymysql/connections.py", line 680, in read_packet

    packet.check_error()

  File "/Users/shinriyo/ve/fb_env/lib/python3.2/site-packages/pymysql/connections.py", line 331, in check_error

    raise_mysql_exception(self.__data)

  File "/Users/shinriyo/ve/fb_env/lib/python3.2/site-packages/pymysql/err.py", line 135, in raise_mysql_exception

    _check_mysql_exception(errinfo)

  File "/Users/shinriyo/ve/fb_env/lib/python3.2/site-packages/pymysql/err.py", line 128, in _check_mysql_exception

    raise errorclass(errno,errorvalue)

pymysql.err.ProgrammingError: (1146, "#42S02Table 'teaspoon.users' doesn't exist")

During handling of the above exception, another exception occurred:

Traceback (most recent call last):

  File "/Users/shinriyo/ve/fb_env/lib/python3.2/site-packages/sqlalchemy/engine/base.py", line 1639, in _execute_context

    context)

  File "/Users/shinriyo/ve/fb_env/lib/python3.2/site-packages/sqlalchemy/engine/default.py", line 332, in do_execute

    cursor.execute(statement, parameters)

  File "/Users/shinriyo/ve/fb_env/lib/python3.2/site-packages/pymysql/cursors.py", line 108, in execute

    self.errorhandler(self, exc, value)

  File "/Users/shinriyo/ve/fb_env/lib/python3.2/site-packages/pymysql/connections.py", line 184, in defaulterrorhandler

    raise errorclass(errorvalue)

pymysql.err.ProgrammingError: (1146, "#42S02Table 'teaspoon.users' doesn't exist")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):

  File "/opt/python-3.2.2/lib/python3.2/runpy.py", line 160, in _run_module_as_main

    "__main__", fname, loader, pkg_name)

  File "/opt/python-3.2.2/lib/python3.2/runpy.py", line 73, in _run_code

    exec(code, run_globals)

  File "/Users/shinriyo/ve/fb_env/tmp/teaspoon/__main__.py", line 78, in <module>

    eval(argv[1])()

  File "/Users/shinriyo/ve/fb_env/tmp/teaspoon/__main__.py", line 46, in create

    __create__()

  File "./models/__init__.py", line 29, in <lambda>

    __create__ = lambda: (setattr(engine, 'echo', True), metadata.create_all(engine))

  File "/Users/shinriyo/ve/fb_env/lib/python3.2/site-packages/sqlalchemy/schema.py", line 2538, in create_all

    tables=tables)

  File "/Users/shinriyo/ve/fb_env/lib/python3.2/site-packages/sqlalchemy/engine/base.py", line 2236, in _run_visitor

    conn._run_visitor(visitorcallable, element, **kwargs)

  File "/Users/shinriyo/ve/fb_env/lib/python3.2/site-packages/sqlalchemy/engine/base.py", line 1906, in _run_visitor

    **kwargs).traverse_single(element)

  File "/Users/shinriyo/ve/fb_env/lib/python3.2/site-packages/sqlalchemy/sql/visitors.py", line 84, in traverse_single

    return meth(obj, **kw)

  File "/Users/shinriyo/ve/fb_env/lib/python3.2/site-packages/sqlalchemy/engine/ddl.py", line 53, in visit_metadata

    collection = [t for t in sql_util.sort_tables(tables) 

  File "/Users/shinriyo/ve/fb_env/lib/python3.2/site-packages/sqlalchemy/engine/ddl.py", line 54, in <listcomp>

    if self._can_create_table(t)]

  File "/Users/shinriyo/ve/fb_env/lib/python3.2/site-packages/sqlalchemy/engine/ddl.py", line 32, in _can_create_table

    table.name, schema=table.schema)

  File "/Users/shinriyo/ve/fb_env/lib/python3.2/site-packages/sqlalchemy/dialects/mysql/base.py", line 1878, in has_table

    rs = connection.execute(st)

  File "/Users/shinriyo/ve/fb_env/lib/python3.2/site-packages/sqlalchemy/engine/base.py", line 1405, in execute

    params)

  File "/Users/shinriyo/ve/fb_env/lib/python3.2/site-packages/sqlalchemy/engine/base.py", line 1582, in _execute_text

    statement, parameters

  File "/Users/shinriyo/ve/fb_env/lib/python3.2/site-packages/sqlalchemy/engine/base.py", line 1646, in _execute_context

    context)

  File "/Users/shinriyo/ve/fb_env/lib/python3.2/site-packages/sqlalchemy/engine/base.py", line 1783, in _handle_dbapi_exception

    from e

sqlalchemy.exc.ProgrammingError: (ProgrammingError) (1146, "#42S02Table 'teaspoon.users' doesn't exist") b'DESCRIBE `users`' ()

[shinriyo]

Hi All

Mac edition is resolved. because, I use PostgreSQL+Python3.2.2 !

But, Windows can't.....In spite of Python2.7!!!