环境:mac os
python DB API
Python访问数据库的统一接口规范
1.创建数据库连接对象conection
2.获取数据库交互对象cursor
3.查询,插入数据等操作
4关闭cursor,connection
下载mysql connector
pip3 install mysql-connector-python
创建连接对象connection
建立python与mysql之间的连接
创建方法:MySQLdb.Connec(参数)
connection对象支持的方法:
- cursor()使用该连接创建返回游标
- commit()提交
- rollback()回滚
- close()关闭连接
代码
import mysql.connector
from mysql.connector import errorcode
config = {
'host': '127.0.0.1',
'user': 'root',
'password': '*********',
'port': 3306,
'database': 'all_test',
'charset': 'utf8'
}
try:
print('开始连接mysql')
conn = mysql.connector.connect(**config)
print(conn)
except mysql.connector.Error as err:
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
print("用户名或密码错误")
elif err.errno == errorcode.ER_BAD_DB_ERROR:
print("数据库不存在")
else:
print(err)
cursor = conn.cursor()
try:
sql_query = 'select * from stu ;'
cursor.execute(sql_query)
for name, age in cursor:
print('[', name, age, ']')
except mysql.connector.Error as e:
print('query error!{}'.format(e))
finally:
print("关闭数据库连接")
cursor.close()
conn.close()
output:
开始连接mysql
<mysql.connector.connection.MySQLConnection object at 0x108df60b8>
[ 你好 78 ]
[ 学生啊 18 ]
关闭数据库连接
cursor游标对象(用于查询和获取结果)
查询语句注意:游标位置在移动,rowcount变
sql_query = 'select * from stu ;'
cursor = conn.cursor()
cursor.execute(sql_query)
print(cursor)
print(cursor.rowcount)
# for name, age in cursor:
# print('[', name, age, ']')
print(cursor.fetchone())
print(cursor.fetchmany(2))
print(cursor.rowcount)
print(cursor.fetchall())
插入语句
import mysql.connector
from mysql.connector import errorcode
config = {
'host': '127.0.0.1',
'user': 'root',
'password': '1045152332zjy',
'port': 3306,
'database': 'all_test',
'charset': 'utf8'
}
def select2(sql_cmd, param):
"""
:param sql_cmd sql 命令
:param param 参数
"""
try:
conn = mysql.connector.connect(**config)
except mysql.connector.Error as e:
print('connect fails!{}'.format(e))
cursor = conn.cursor()
try:
cursor.execute(sql_cmd, param)
conn.commit()
except mysql.connector.Error as e:
print('connect fails!{}'.format(e))
finally:
cursor.close()
conn.close()
if __name__=='__main__':
sql_cmd = "insert into stu (name, age) value (%s, %s)"
param = ('name999', 999)
select2(sql_cmd=sql_cmd, param=param)