python之SQLite连接数据库

文章目录

SQLite连接数据库创建数据表插入数据查询数据

SQLite

连接数据库

# 1.连接数据库

conn = sqlite3.connect("test.db") # 打开或创建数据库文件

print("成功打开数据库")

创建数据表

# 1.连接数据库

conn = sqlite3.connect("test.db") # 打开或创建数据库文件

print("成功打开数据库")

# 2.创建数据表

c = conn.cursor() # 获取游标

sql = '''

create table company

(id int primary key not null,

name text not null,

age int not null,

address char(50),

salary real);

'''

c.execute(sql) # 执行sql语句

conn.commit() # 提交数据库操作

conn.close() # 关闭数据库链接

print("成功建表")

插入数据

import sqlite3

# 1.连接数据库

conn = sqlite3.connect("test.db") # 打开或创建数据库文件

print("成功打开数据库")

# 2.创建数据表

c = conn.cursor() # 获取游标

sql1 = '''

insert into company (id,name,age,address,salary)

values(1,"张三" ,"19","西安","10000")

'''

sql2 = '''

insert into company (id,name,age,address,salary)

values(2,"bkys" ,"19","西安","1000000")

'''

c.execute(sql1) # 执行sql语句

c.execute(sql2)

conn.commit() # 提交数据库操作

conn.close() # 关闭数据库链接

print("插入数据完毕")

查询数据

# 1.连接数据库

conn = sqlite3.connect("test.db") # 打开或创建数据库文件

print("成功打开数据库")

# 2.创建数据表

c = conn.cursor() # 获取游标

sql = "select id,name,address,salary from company"

cursor = c.execute(sql)

for row in cursor:

print("id = ", row[0])

print("name = ", row[1])

print("address = ", row[2])

print("salary = ", row[3], "\n")

conn.close() # 关闭数据库链接

print("查询完毕")

def saveData2DB(datalist,dbpath):

init_db(dbpath)

conn =sqlite3.connect(dbpath)

cur = conn.cursor()

for data in datalist:

for index in range(len(data)):

data[index] = '"' + data[index] + '"'

sql = '''

insert into movie250 (

info_link, pic_link, cname, ename, score, rated, introduction, info)

values(%s)''' % ",".join(data)

cur.execute(sql)

conn.commit()

cur.close()

conn.close()

def init_db(dbpath):

sql = '''

create table movie250

(

id integer primary key autoincrement,

info_link text,

pic_link text,

cname varchar ,

ename varchar ,

score numeric ,

rated numeric ,

instroduction text,

info text

)

'''

conn = sqlite3.connect(dbpath)

cursor = conn.cursor()

cursor.execute(sql)

conn.commit()

conn.close()

相关文章

 您阅读本篇文章共花了：