python连接mysql数据库遇到的问题

1.源代码：

 

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Column
from sqlalchemy.types import CHAR, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from random import randint
from sqlalchemy import ForeignKey
engine = create_engine('mssql+pymssql://root:root@localhost:3307/python',encoding="utf-8", echo=True)#创造一个连接
Base=declarative_base()#生成ORM基类
#定义一个类Host,一个表对应一个类，且这个类和表做了映射关系
class Host(Base):
    tablename="hostinfo"#表名
    id=Column(Integer,primary_key=True)#字段
    hostname=Column(String(32))#字段
    ip=Column(String(64))#字段
Base.metadata.create_all(engine)#创建表结构
Session_class=sessionmaker(bind=engine)#创建与数据库的会话session class
Session=Session_class()#生成session实例
user_obj=Host(hostname="pc1",ip="192.168.1.3")#生成你要创建的数据对象
print(user_obj.hostname,user_obj.ip,user_obj.id)#打印数据
Session.add(user_obj)#把要创建的数据对象添加到这个session里，一会统一创建

Session.commit()

报错:

Traceback (most recent call last):
  File "F:/Python_Document/sql/3.py", line 11, in <module>
    class Host(Base):
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python36\lib\site-packages\sqlalchemy-1.2.10-py3.6-win-amd64.egg\sqlalchemy\ext\declarative\api.py", line 65, in __init__
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python36\lib\site-packages\sqlalchemy-1.2.10-py3.6-win-amd64.egg\sqlalchemy\ext\declarative\base.py", line 88, in _as_declarative
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python36\lib\site-packages\sqlalchemy-1.2.10-py3.6-win-amd64.egg\sqlalchemy\ext\declarative\base.py", line 116, in setup_mapping
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python36\lib\site-packages\sqlalchemy-1.2.10-py3.6-win-amd64.egg\sqlalchemy\ext\declarative\base.py", line 146, in __init__
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python36\lib\site-packages\sqlalchemy-1.2.10-py3.6-win-amd64.egg\sqlalchemy\ext\declarative\base.py", line 472, in _setup_inheritance
sqlalchemy.exc.InvalidRequestError: Class <class '__main__.Host'> does not have a __table__ or __tablename__ specified and does not inherit from an existing table-mapped class.

 解决办法：源代码中的_tablename_格式写错，把报错信息中的__tablename__    复制过去就对了。

2.

源代码：

 

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Column
from sqlalchemy.types import CHAR, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from random import randint
from sqlalchemy import ForeignKey
engine=create_engine('mssql+pymssql://root:root@localhost:3307/student')#创造一个连接
Base=declarative_base()#生成ORM基类
#定义一个类Host,一个表对应一个类，且这个类和表做了映射关系
class Host(Base):
    __tablename__='hostinfo'#表名
    id=Column(Integer,primary_key=True)#字段
    hostname=Column(String(32))#字段
    ip=Column(String(64))#字段
Base.metadata.create_all(engine)#创建表结构
Session_class=sessionmaker(bind=engine)#创建与数据库的会话session class
Session=Session_class()#生成session实例
user_obj=Host(hostname="pc1",ip="192.168.1.3")#生成你要创建的数据对象
print(user_obj.hostname,user_obj.ip,user_obj.id)#打印数据
Session.add(user_obj)#把要创建的数据对象添加到这个session里，一会统一创建
Session.commit()

报错：

Traceback (most recent call last):
  File "F:/Python_Document/sql/2.py", line 19, in <module>
    Base.metadata.create_all(engine)#创建表结构
  File "D:\python3.6\lib\site-packages\sqlalchemy-1.2.10-py3.6-win-amd64.egg\sqlalchemy\sql\schema.py", line 4005, in create_all
  File "D:\python3.6\lib\site-packages\sqlalchemy-1.2.10-py3.6-win-amd64.egg\sqlalchemy\engine\base.py", line 1939, in _run_visitor
  File "D:\python3.6\lib\contextlib.py", line 82, in __enter__
    return next(self.gen)
  File "D:\python3.6\lib\site-packages\sqlalchemy-1.2.10-py3.6-win-amd64.egg\sqlalchemy\engine\base.py", line 1932, in _optional_conn_ctx_manager
  File "D:\python3.6\lib\site-packages\sqlalchemy-1.2.10-py3.6-win-amd64.egg\sqlalchemy\engine\base.py", line 2123, in contextual_connect
  File "D:\python3.6\lib\site-packages\sqlalchemy-1.2.10-py3.6-win-amd64.egg\sqlalchemy\engine\base.py", line 2162, in _wrap_pool_connect
  File "D:\python3.6\lib\site-packages\sqlalchemy-1.2.10-py3.6-win-amd64.egg\sqlalchemy\engine\base.py", line 1476, in _handle_dbapi_exception_noconnection
  File "D:\python3.6\lib\site-packages\sqlalchemy-1.2.10-py3.6-win-amd64.egg\sqlalchemy\util\compat.py", line 265, in raise_from_cause
  File "D:\python3.6\lib\site-packages\sqlalchemy-1.2.10-py3.6-win-amd64.egg\sqlalchemy\util\compat.py", line 248, in reraise
  File "D:\python3.6\lib\site-packages\sqlalchemy-1.2.10-py3.6-win-amd64.egg\sqlalchemy\engine\base.py", line 2158, in _wrap_pool_connect
  File "D:\python3.6\lib\site-packages\sqlalchemy-1.2.10-py3.6-win-amd64.egg\sqlalchemy\pool.py", line 403, in connect
  File "D:\python3.6\lib\site-packages\sqlalchemy-1.2.10-py3.6-win-amd64.egg\sqlalchemy\pool.py", line 791, in _checkout
  File "D:\python3.6\lib\site-packages\sqlalchemy-1.2.10-py3.6-win-amd64.egg\sqlalchemy\pool.py", line 532, in checkout
  File "D:\python3.6\lib\site-packages\sqlalchemy-1.2.10-py3.6-win-amd64.egg\sqlalchemy\pool.py", line 1196, in _do_get
  File "D:\python3.6\lib\site-packages\sqlalchemy-1.2.10-py3.6-win-amd64.egg\sqlalchemy\util\langhelpers.py", line 66, in __exit__
  File "D:\python3.6\lib\site-packages\sqlalchemy-1.2.10-py3.6-win-amd64.egg\sqlalchemy\util\compat.py", line 249, in reraise
  File "D:\python3.6\lib\site-packages\sqlalchemy-1.2.10-py3.6-win-amd64.egg\sqlalchemy\pool.py", line 1193, in _do_get
  File "D:\python3.6\lib\site-packages\sqlalchemy-1.2.10-py3.6-win-amd64.egg\sqlalchemy\pool.py", line 350, in _create_connection
  File "D:\python3.6\lib\site-packages\sqlalchemy-1.2.10-py3.6-win-amd64.egg\sqlalchemy\pool.py", line 477, in __init__
  File "D:\python3.6\lib\site-packages\sqlalchemy-1.2.10-py3.6-win-amd64.egg\sqlalchemy\pool.py", line 674, in __connect
  File "D:\python3.6\lib\site-packages\sqlalchemy-1.2.10-py3.6-win-amd64.egg\sqlalchemy\engine\strategies.py", line 106, in connect
  File "D:\python3.6\lib\site-packages\sqlalchemy-1.2.10-py3.6-win-amd64.egg\sqlalchemy\engine\default.py", line 412, in connect
  File "pymssql.pyx", line 641, in pymssql.connect (pymssql.c:10824)
sqlalchemy.exc.OperationalError: (pymssql.OperationalError) (20002, b'DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (localhost:3307)\n') (Background on this error at: http://sqlalche.me/e/e3q8)

Process finished with exit code 1

解决办法：

后来发现是在mysql中创建不了表格。所以只能手动创建表格。但是任然与数据库不能连接。

然后我配置了sql server数据库。发现连接成功。

源代码：

import pymssql
#连接sql server数据库

conn=pymssql.connect(host="localhost",port=1433,user="sa", password="root",database="world",charset="utf8")
cursor = conn.cursor()
sql = "select * from Product"
cursor.execute(sql)

# 获取总记录数
print(cursor.rowcount)

# 获取一条数据
rs = cursor.fetchone()
print(rs)

# 获取所有数据，返回所有的数据
rs = cursor.fetchall()
print(rs)

cursor.close()
conn.close()

连接不上mysql数据库，应该是数据库的问题。应该再重装一遍数据库就行了。