python连接数据库

python连接数据库

连接MySQL

第一步：连接mysql数据库要导入的包：

import pymysql

第二步：连接数据库

db=pymysql.connect(host,port,user,password,database)

第三步：开启游标功能，创建游标对象（开启游标的功能是，起到一个存储的作用，用查看数据时，用fetchall()或者fetchone()接口）

cursor = db.sursor()

第四步：执行sql语句（注意：如果sql语句为增加、删除、修改，要用commit（）进行提交

select_sql = 'SELECT * FROM name'
cursor.execute(select_sql)

delete_sql = 'delete from name where id=1'
cursor.execute(delete_sql)
db.commit()

第五步：提取查询结果（fetchall()或者fetchone()接口）,以元组的形式进行取出

data=cursor.fetchall()

第六步：关闭数据，并打印出结果

db.close()
print(data)

eg：

import pymysql


class Connection:

    def con_mysql(self, localhost, port, user, password, database):
        # 连接mysql
        db = pymysql.connect(host=localhost, port=port, user=user, password=password, database=database)
        # 开启游标功能，创建游标对象，创建cursor()游标对象
        cursor = db.cursor()
        # # 数据查询
        # select_sql = 'SELECT * FROM name'
        # # 使用excute()执行SQL语句
        # cursor.execute(select_sql)
        # # 使用fetchone()或者fetchall()返回查询结果
        # data = cursor.fetchall()

        # # 数据删除
        # delete_sql = 'delete from name where id=1'
        # cursor.execute(delete_sql)
        # db.commit()
        # data =cursor.fetchall()

        # #增加数据
        # insert_sql='insert into name values (4,"九点九九九"),(5,"分级基金")'
        # cursor.execute(insert_sql)
        # db.commit()
        # select_sql = 'SELECT * FROM name'
        # cursor.execute(select_sql)
        # data=cursor.fetchall()

        # 修改数据
        update_sql = ' update name set name="wangguidddddz" where id = 2'
        cursor.execute(update_sql)
        # db.commit()
        select_sql = 'SELECT * FROM name'
        cursor.execute(select_sql)
        data = cursor.fetchall()

        # 关闭数据库
        db.close()
        print(data)


if __name__ == '__main__':
    host = '127.0.0.1'
    port = 3306
    user = 'root'
    password = 'root'
    database = 'wgz'
    Connection().con_mysql(host, port, user, password, database)

连接Oracle

导入cx_Oracle包

import cx_Oracle

步骤跟连接mysql一致

eg：

import cx_Oracle
conn = cx_Oracle.connect('用户名','密码','IP地址:端口号/ORCL')
cursor = conn.cursor()
print('连接数据库成功！')
insert_sql='insert into name values (4,"九点九九九"),(5,"分级基金")'
all = cursor.execute(insert_sql)
conn.commit()

连接SqlServer

导入pyodbc包

import pyodbc

步骤跟连接mysql一致

eg：

conn = pyodbc.connect('Driver={SQL Server};'

                      'Server=server_name;'

                      'Database=database_name;'

                      'Trusted_Connection=yes;')
cursor = conn.cursor()
# 执行 SQL 查询
insert_sql='insert into name values (4,"九点九九九"),(5,"分级基金")'
all = cursor.execute(insert_sql)
conn.commit()