python连接数据库步骤_Python连接mysql数据库

Python中连接MySQL的库主要有三个，Python-MySQL，PyMySQL和SQLAlchemy，其中Python-MySQL已经停止更新，且只支持Python2，目前使用最广泛的是PyMySQL,使用方法如下：

安装

pip install pymysql

连接

代码中只需要 import pymysql 即可导入使用，pymysql的一般使用步骤为：

配置数据库连接信息

连接数据库，获取连接对象

使用连接对象获取一个游标(cursor)对象

使用cursor对象提供的方法执行SQL语句

关闭cursor对象

关闭连接对象

操作

以下为增删改查的实例:

查询函数, 传入键值进行查询并返回结果列表

def find(key, word):

con = pymysql.connect(host='localhost', user='root', password='1111', port=3306, db='logistics')

cursor = con.cursor()

sql = "select * from main_info where {}='{}'".format(key, word)

list = []

try:

cursor.execute(sql)

row = cursor.fetchone()

while row:

print(row)

list.append(row)

row = cursor.fetchone()

except Exception as e:

print(e)

finally:

cursor.close()

con.close()

return list

插入函数, 插入新信息

def insert(id, name):

con = pymysql.connect(host='localhost', user='root', password='1111', port=3306, db='logistics')

cursor = con.cursor()

data = {

'id': id,

'name': name,

}

table = 'main_info'

keys = ', '.join(data.keys())

values = (', '.join('"' + item + '"' for item in data.values()))

sql = "insert into {0}({1}) values({2})".format(table, keys, values)

try:

if cursor.execute(sql):

con.commit()

result = True

except Exception as e:

con.rollback()

result = False

finally:

cursor.close()

con.close()

return result

删除函数， 以删除制定id为例

def delete(id):

con = pymysql.connect(host='localhost', user='root', password='1111', port=3306, db='logistics')

cursor = con.cursor()

sql = "delete from main——info where id = {}".format(id)

try:

if cursor.execute(sql):

con.commit()

result = True

except Exception as e:

con.rollback()

result = False

finally:

cursor.close()

con.close()

return result

修改函数， 修改指定id的name部分

def modify(id, name):

con = pymysql.connect(host='localhost', user='root', password='1111', port=3306, db='logistics')

cursor = con.cursor()

sql = "update from main——info set name = {} where id = {}".format(name, id)

try:

if cursor.execute(sql):

con.commit()

result = True

except Exception as e:

con.rollback()

result = False

finally:

cursor.close()

con.close()

return result