python数据库中表的操作

最新推荐文章于 2024-08-13 11:34:18 发布

zzh_1032399080

最新推荐文章于 2024-08-13 11:34:18 发布

阅读量1.2k

点赞数

分类专栏： python

本文链接：https://blog.csdn.net/zzh_1032399080/article/details/78610152

版权

python 专栏收录该内容

46 篇文章 0 订阅

订阅专栏

创建表

[root@localhost ~]# cat 1.py
#!/usr/bin/env python
import MySQLdb

def connect_mysql():
    db_config = {
        'host': '127.0.0.1',
        'port': 3306,
        'user': 'root',
        'passwd': 'pzk123',
        'db': 'test'
    }
    c = MySQLdb.connect(**db_config)
    return c

if __name__ == '__main__':
    c = connect_mysql()                             # 先连接数据库
    cus = c.cursor()
    sql = '''                                       # 定义建表语句
        create table t1(
            id int primary key not null,
            name varchar(10) not null,
            age int not null
        );
    '''
    try:
        cus.execute(sql)                            # 创建数据表
        c.commit()
    except Exception as e:
        c.rollback()
        raise e
    finally:
        c.close()

增加数据

[root@localhost ~]# mysql -uroot -ppzk123 -e "use test; desc t1;"
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(10) | NO   |     | NULL    |       |
| age   | int(11)     | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
#!/usr/bin/env python
import MySQLdb

def connect_mysql():
    db_config = {
        'host': '127.0.0.1',
        'port': 3306,
        'user': 'root',
        'passwd': 'pzk123',
        'db': 'test'
    }
    c = MySQLdb.connect(**db_config)
    return c

if __name__ == '__main__':
    c = connect_mysql()
    cus = c.cursor()
    sql = '''insert into t1 values
            (1, 'Tom', 17),
            (2, 'John', 25),
            (3, 'Jeny', 25);
    '''
    try:
        cus.execute(sql)
        c.commit()
    except Exception as e:
        c.rollback()
        raise e
    finally:
        c.close()

查询数据

#!/usr/bin/env python
import MySQLdb

def connect_mysql():
    db_config = {
        'host': '127.0.0.1',
        'port': 3306,
        'user': 'root',
        'passwd': 'pzk123',
        'db': 'test'
    }
    c = MySQLdb.connect(**db_config)
    return c

if __name__ == '__main__':
    c = connect_mysql()
    cus = c.cursor()
    sql = 'select * from t1;'
    try:
        cus.execute(sql)
        result = cus.fetchall()
        with open('1.txt', 'w') as fd:    # 这里我们把查询到的数据放到一个文件下
            for i in result:
                fd.write(str(i))
                fd.write('\n')
        c.commit()
    except Exception as e:
        c.rollback()
        raise e
    finally:
        c.close()