Python 使用 pyodbc 连接 MySQL进行 CRUD 操作

最新推荐文章于 2024-08-10 08:35:25 发布

allway2

最新推荐文章于 2024-08-10 08:35:25 发布

阅读量876

点赞数

文章标签： python

本文链接：https://blog.csdn.net/allway2/article/details/124360514

版权

import pyodbc

sqlDbConn = pyodbc.connect("""DRIVER={MySQL ODBC 3.51 Driver};
    SERVER=localhost;DATABASE=db;USER=root;PASSWORD=root;"""
)


def getData(sqlDbConn):
    print("Read")
    cursor = sqlDbConn.cursor();
    cursor.execute("select * from MyTable")
    for row in cursor:
        print(row)


def insertData(sqlDbConn):
    print("Insert")
    cursor = sqlDbConn.cursor();
    cursor.execute(
        'insert into MyTable (name,city) values(?,?)',
        ('Ram', 'Delhi'))

    sqlDbConn.commit()
    # Without calling commit data will not saved in database.


def updateData(sqlDbConn):
    print("Update")
    cursor = sqlDbConn.cursor();
    cursor.execute(
        'update MyTable set city = ? where id = ?',
        ('Motihari', 17))
    sqlDbConn.commit()


def deleteData(sqlDbConn):
    print("Delete")
    cursor = sqlDbConn.cursor();
    cursor.execute(
        'delete from MyTable where id = ?',
        (2))
    sqlDbConn.commit()


# Call the functions one by one
insertData(sqlDbConn)
updateData(sqlDbConn)
deleteData(sqlDbConn)
getData(sqlDbConn)

CREATE TABLE `mytable` (
	`id` INT(10) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`city` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=2
;