python数据库连接方式_python连接数据库

一、连接mysql

1、安装库PyMySQL

2、2种连接方式。

# 第一种

import pymysql

# 直接生成db对象

db = pymysql.connect(host = 'localhost',

port = 3306,

user = 'root',

password = '123',

db = 'mrsoft',

charset = 'utf8',

cursorclass = pymysql.cursors.DictCursor

)

cursor = db.cursor() # 创建游标

# 第二种

import pymysql

# 通过字典的方式创建db对象

dictoj = {

'host': 'localhost',

'port': 3306,

'user': 'root',

'password': '123',

'db': 'mrsoft',

'charset': 'utf8',

'cursorclass': pymysql.cursors.DictCursor

}

db = pymysql.connect(**dictoj)

3、添加数据采用executemany()方法

# 添加数据 executemany(operation ,seq_of_params)

data = [

('三国演义','四大名著','100.23','2007-1-3'),

('水浒传','四大名著','120.12','2004-2-24'),

('红楼梦','四大名著','130.29','2003-4-21'),

('西游记','四大名著','140.37','2002-7-1'),

('java开发','专业书','50.67','2019-1-21'),

('php开发实战','专业书','80.19','2017-2-22')

]

try:

cursor.executemany('insert into books(name,category,price,publish_time) values(%s,%s,%s,%s)', data)

db.commit()

except:

db.rollback() # 错误时执行回滚操作

4、代码

import pymysql

"""

# 直接生成db对象

db = pymysql.connect(host = 'localhost',

port = 3306,

user = 'root',

password = '123',

db = 'mrsoft',

charset = 'utf8',

cursorclass = pymysql.cursors.DictCursor

)

"""

# 通过字典的方式创建db对象

dictoj = {

'host': 'localhost',

'port': 3306,

'user': 'root',

'password': '123',

'db': 'mrsoft',

'charset': 'utf8',

'cursorclass': pymysql.cursors.DictCursor

}

db = pymysql.connect(**dictoj)

cursor = db.cursor() # 创建游标

cursor.execute('drop table if exists books') # 游标的execute()方法

sql = """

create table books (

id int(8) not null auto_increment,

name varchar(50) not null,

category varchar(50) not null,

price decimal(10,2) default null,

publish_time date default null,

primary key (id)

) engine=myisam auto_increment=1 default charset= utf8;

"""

cursor.execute(sql) # 执行sql

# 添加数据 executemany(operation ,seq_of_params)

data = [

('三国演义','四大名著','100.23','2007-1-3'),

('水浒传','四大名著','120.12','2004-2-24'),

('红楼梦','四大名著','130.29','2003-4-21'),

('西游记','四大名著','140.37','2002-7-1'),

('java开发','专业书','50.67','2019-1-21'),

('php开发实战','专业书','80.19','2017-2-22')

]

try:

cursor.executemany('insert into books(name,category,price,publish_time) values(%s,%s,%s,%s)', data)

db.commit()

except:

db.rollback() # 错误时执行回滚操作

db.close()

二、连接SQL

1、引入库pymssql

2、连接

def conn():

connect = pymssql.connect('.','sa','123','dbtest')

if connect :

print('connect success!')

return connect

else:

print('connect failed!')

3、数据库操作(新建表sql_test，插入数据，查询数据)

注意插入数据后得commit提交下否则表中没有数据。

import pymssql

def conn():

connect = pymssql.connect('.','sa','123','dbtest')

if connect :

print('connect success!')

return connect

else:

print('connect failed!')

def createsql():

cursor.execute('create table sql_test(id varchar(20),name varchar(50) default null,sex bit not null)')

db.commit()

print('create success！')

def inssql():

sql = "insert into sql_test(id,name,sex) values('1001','张三','0'),('1001','张三','0')"

print('insert success!')

return sql

def selectsql():

sl = 'select id, name, sex from sql_test'

return sl

if __name__ == '__main__':

db = conn() # 创建数据库对象

cursor = db.cursor() # 创建游标

createsql() # 创建

# 插入

s0 = inssql()

cursor.execute(s0)

db.commit() # 插入以后必须得提交，否则没有数据

# 查询

s1 = selectsql()

cursor.execute(s1)

# 读取查询结果

row = cursor.fetchone()

while row :

print("Id =%s,Name=%s,Sex =%s" % (row[0],row[1],row[2]))

row = cursor.fetchone()

cursor.close()

db.close()