python中常见的数据库有哪些呢?数据库大致分为两大类,第一类是包括关系数据库,第二类是非关系数据库,下面介绍一下这两类数据库的相关知识。
包括关系数据库:sqlite,mysql,mssql
非关系数据库:MongoDB,Redis
1. 连接Sqlite
import sqlite3 import traceback try: # 如果表不存在,就创建 with sqlite3.connect('test.db') as conn: print("Opened database successfully") # 删除表 conn.execute("DROP TABLE IF EXISTS COMPANY") # 创建表 sql = """ CREATE TABLE IF NOT EXISTS COMPANY (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL); """ conn.execute(sql) print("create table successfully") # 添加数据 conn.executemany("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES (?, ?, ?, ? )", [('Paul', 32, 'California', 20000.00), ('Allen', 25, 'Texas', 15000.00), ('Teddy', 23, 'Norway', 20000.00), ('Mark', 25, 'Rich-Mond ', 65000.00), ('David', 27, 'Texas', 85000.00), ('Kim', 22, 'South-Hall', 45000.00), ('James', 24, 'Houston', 10000.00)]) # conn.execute("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)\ # VALUES ( 'Paul', 32, 'California', 20000.00 )") # # conn.execute("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)\ # VALUES ('Allen', 25, 'Texas', 15000.00 )") # # conn.execute("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)\ # VALUES ('Teddy', 23, 'Norway', 20000.00 )") # # conn.execute("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)\ # VALUES ( 'Mark', 25, 'Rich-Mond ', 65000.00 )") # # conn.execute("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)\ # VALUES ( 'David', 27, 'Texas', 85000.00 )"); # # conn.execute("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)\ # VALUES ( 'Kim', 22, 'South-Hall', 45000.00 )") # # conn.execute("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)\ # VALUES ( 'James', 24, 'Houston', 10000.00 )") # 提交,否则重新运行程序时,表中无数据 conn.commit() print("insert successfully") # 查询表 sql = """ select id,NAME,AGE,ADDRESS,SALARY FROM COMPANY """ result = conn.execute(sql) for row in result: print("-" * 50) # 输出50个-,作为分界线 print("%-10s %s" % ("id", row[0])) # 字段名固定10位宽度,并且左对齐 print("%-10s %s" % ("name", row[1])) print("%-10s %s" % ("age", row[2])) print("%-10s %s" % ("address", row[3])) print("%-10s %.2f" % ("salary", row[4])) # or # print('{:10s} {:.2f}'.format("salary", row[4])) except sqlite3.Error as e: print("sqlite3 Error:", e) traceback.print_exc()
2.连接mysql
相关推荐:《python视频教程》
2.2 使用MySQLdb
2.1使用mysqldb库中的_mysql
import MySQLdb from contextlib import closing import traceback try: # 获取一个数据库连接 with closing(MySQLdb.connect(host='localhost', user='root', passwd='root', db='test', port=3306,charset='utf8')) as conn: print("connect database successfully") with closing(conn.cursor()) as cur: # 删除表 cur.execute("DROP TABLE IF EXISTS COMPANY") # 创建表 sql = """ CREATE TABLE IF NOT EXISTS COMPANY (ID INTEGER PRIMARY KEY NOT NULL auto_increment, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL); """ cur.execute(sql) print("create table successfully") # 添加数据 # 在一个conn.execute里面里面执行多个sql语句是非法的 cur.executemany("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( %s, %s, %s, %s )", [('Paul', 32, 'California', 20000.00), ('Allen', 25, 'Texas', 15000.00), ('Teddy', 23, 'Norway', 20000.00), ('Mark', 25, 'Rich-Mond ', 65000.00), ('David', 27, 'Texas', 85000.00), ('Kim', 22, 'South-Hall', 45000.00), ('James', 24, 'Houston', 10000.00)]) # 提交,否则重新运行程序时,表中无数据 conn.commit() print("insert successfully") # 查询表 sql = """ select id,NAME,AGE,ADDRESS,SALARY FROM COMPANY """ cur.execute(sql) for row in cur.fetchall(): print("-" * 50) # 输出50个-,作为分界线 print("%-10s %s" % ("id", row[0])) # 字段名固定10位宽度,并且左对齐 print("%-10s %s" % ("name", row[1])) print("%-10s %s" % ("age", row[2])) print("%-10s %s" % ("address", row[3])) print("%-10s %s" % ("salary", row[4])) except MySQLdb.Error as e: print("Mysql Error:", e) traceback.print_exc() # 打印错误栈信息
2.2 使用MySQLdb
import MySQLdb from contextlib import closing import traceback try: # 获取一个数据库连接 with closing(MySQLdb.connect(host='localhost', user='root', passwd='root', db='test', port=3306,charset='utf8')) as conn: print("connect database successfully") with closing(conn.cursor()) as cur: # 删除表 cur.execute("DROP TABLE IF EXISTS COMPANY") # 创建表 sql = """ CREATE TABLE IF NOT EXISTS COMPANY (ID INTEGER PRIMARY KEY NOT NULL auto_increment, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL); """ cur.execute(sql) print("create table successfully") # 添加数据 # 在一个conn.execute里面里面执行多个sql语句是非法的 cur.executemany("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( %s, %s, %s, %s )", [('Paul', 32, 'California', 20000.00), ('Allen', 25, 'Texas', 15000.00), ('Teddy', 23, 'Norway', 20000.00), ('Mark', 25, 'Rich-Mond ', 65000.00), ('David', 27, 'Texas', 85000.00), ('Kim', 22, 'South-Hall', 45000.00), ('James', 24, 'Houston', 10000.00)]) # 提交,否则重新运行程序时,表中无数据 conn.commit() print("insert successfully") # 查询表 sql = """ select id,NAME,AGE,ADDRESS,SALARY FROM COMPANY """ cur.execute(sql) for row in cur.fetchall(): print("-" * 50) # 输出50个-,作为分界线 print("%-10s %s" % ("id", row[0])) # 字段名固定10位宽度,并且左对齐 print("%-10s %s" % ("name", row[1])) print("%-10s %s" % ("age", row[2])) print("%-10s %s" % ("address", row[3])) print("%-10s %s" % ("salary", row[4])) except MySQLdb.Error as e: print("Mysql Error:", e) traceback.print_exc() # 打印错误栈信息
2.3使用pymysql
2.1和2.2节使用MySQLdb,不支持Python3.x
pymysql对Python2.x和Python3.x的支持都比较好
import pymysql from contextlib import closing import traceback try: # 获取一个数据库连接,with关键字 表示退出时,conn自动关闭 # with 嵌套上一层的with 要使用closing() with closing(pymysql.connect(host='localhost', user='root', passwd='root', db='test', port=3306, charset='utf8')) as conn: print("connect database successfully") # 获取游标,with关键字 表示退出时,cur自动关闭 with conn.cursor() as cur: # 删除表 cur.execute("DROP TABLE IF EXISTS COMPANY") # 创建表 sql = """ CREATE TABLE IF NOT EXISTS COMPANY (ID INTEGER PRIMARY KEY NOT NULL auto_increment, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL); """ cur.execute(sql) print("create table successfully") # 添加数据 # 在一个conn.execute里面里面执行多个sql语句是非法的 cur.executemany("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( %s, %s, %s, %s )", [('Paul', 32, 'California', 20000.00), ('Allen', 25, 'Texas', 15000.00), ('Teddy', 23, 'Norway', 20000.00), ('Mark', 25, 'Rich-Mond ', 65000.00), ('David', 27, 'Texas', 85000.00), ('Kim', 22, 'South-Hall', 45000.00), ('James', 24, 'Houston', 10000.00)]) # 提交,否则重新运行程序时,表中无数据 conn.commit() print("insert successfully") # 查询表 sql = """ select id,NAME,AGE,ADDRESS,SALARY FROM COMPANY """ cur.execute(sql) for row in cur.fetchall(): print("-" * 50) # 输出50个-,作为分界线 print("%-10s %s" % ("id", row[0])) # 字段名固定10位宽度,并且左对齐 print("%-10s %s" % ("name", row[1])) print("%-10s %s" % ("age", row[2])) print("%-10s %s" % ("address", row[3])) print("%-10s %s" % ("salary", row[4])) except pymysql.Error as e: print("Mysql Error:", e) traceback.print_exc()
3.连接mssql
import pymssql from contextlib import closing try: # 先要保证数据库中有test数据库 # 获取一个数据库连接,with关键字 表示退出时,conn自动关闭 # with 嵌套上一层的with 要使用closing() with closing(pymssql.connect(host='192.168.100.114', user='sa', password='sa12345', database='test', port=1433, charset='utf8')) as conn: print("connect database successfully") # 获取游标,with关键字 表示退出时,cur自动关闭 with conn.cursor() as cur: # 删除表 cur.execute( '''if exists (select 1 from sys.objects where name='COMPANY' and type='U') drop table COMPANY''') # 创建表 sql = """ CREATE TABLE COMPANY (ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL , NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL); """ cur.execute(sql) print("create table successfully") # 添加数据 # 在一个conn.execute里面里面执行多个sql语句是非法的 cur.executemany("INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( %s, %s, %s, %s )", [('Paul', 32, 'California', 20000.00), ('Allen', 25, 'Texas', 15000.00), ('Teddy', 23, 'Norway', 20000.00), ('Mark', 25, 'Rich-Mond', 65000.00), ('David', 27, 'Texas', 85000.00), ('Kim', 22, 'South-Hall', 45000.00), ('James', 24, 'Houston', 10000.00)]) # 提交,否则重新运行程序时,表中无数据 conn.commit() print("insert successfully") # 查询表 sql = """ select id,NAME,AGE,ADDRESS,SALARY FROM COMPANY """ cur.execute(sql) for row in cur.fetchall(): print("-" * 50) # 输出50个-,作为分界线 print("%-10s %s" % ("id", row[0])) # 字段名固定10位宽度,并且左对齐 print("%-10s %s" % ("name", row[1])) print("%-10s %s" % ("age", row[2])) print("%-10s %s" % ("address", row[3])) print("%-10s %s" % ("salary", row[4])) except pymssql.Error as e: print("mssql Error:", e) # traceback.print_exc()
4.连接MongoDB
import pymongo from pymongo.mongo_client import MongoClient import pymongo.errors import traceback try: # 连接到 mongodb 服务 mongoClient = MongoClient('localhost', 27017) # 连接到数据库 mongoDatabase = mongoClient.test print("connect database successfully") # 获取集合 mongoCollection = mongoDatabase.COMPANY # 移除所有数据 mongoCollection.remove() # 添加数据 mongoCollection.insert_many([{"Name": "Paul", "Age": "32", "Address": "California", "Salary": "20000.00"}, {"Name": "Allen", "Age": "25", "Address": "Texas", "Salary": "15000.00"}, {"Name": "Teddy", "Age": "23", "Address": "Norway", "Salary": "20000.00"}, {"Name": "Mark", "Age": "25", "Address": "Rich-Mond", "Salary": "65000.00"}, {"Name": "David", "Age": "27", "Address": "Texas", "Salary": "85000.00"}, {"Name": "Kim", "Age": "22", "Address": "South-Hall", "Salary": "45000.00"}, {"Name": "James", "Age": "24", "Address": "Houston", "Salary": "10000.00"}, ]) #获取集合中的值 for row in mongoCollection.find(): print("-" * 50) # 输出50个-,作为分界线 print("%-10s %s" % ("_id", row['_id'])) # 字段名固定10位宽度,并且左对齐 print("%-10s %s" % ("name", row['Name'])) print("%-10s %s" % ("age", row['Age'])) print("%-10s %s" % ("address", row['Address'])) print("%-10s %s" % ("salary", row['Salary'])) print('\n\n\n') # 使id自增 mongoCollection.remove() # 创建计数表 mongoDatabase.counters.save({"_id": "people_id", "sequence_value": 0}) # 创建存储过程 mongoDatabase.system_js.getSequenceValue = '''function getSequenceValue(sequenceName){ var sequenceDocument = db.counters.findAndModify({ query: {_id: sequenceName}, update: {$inc:{sequence_value: 1}}, new:true }); return sequenceDocument.sequence_value; }''' mongoCollection.insert_many( [{"_id": mongoDatabase.eval("getSequenceValue('people_id')"), "Name": "Paul", "Age": "32", "Address": "California", "Salary": "20000.00"}, {"_id": mongoDatabase.eval("getSequenceValue('people_id')"), "Name": "Allen", "Age": "25", "Address": "Texas", "Salary": "15000.00"}, {"_id": mongoDatabase.eval("getSequenceValue('people_id')"), "Name": "Teddy", "Age": "23", "Address": "Norway", "Salary": "20000.00"}, {"_id": mongoDatabase.eval("getSequenceValue('people_id')"), "Name": "Mark", "Age": "25", "Address": "Rich-Mond", "Salary": "65000.00"}, {"_id": mongoDatabase.eval("getSequenceValue('people_id')"), "Name": "David", "Age": "27", "Address": "Texas", "Salary": "85000.00"}, {"_id": mongoDatabase.eval("getSequenceValue('people_id')"), "Name": "Kim", "Age": "22", "Address": "South-Hall", "Salary": "45000.00"}, {"_id": mongoDatabase.eval("getSequenceValue('people_id')"), "Name": "James", "Age": "24", "Address": "Houston", "Salary": "10000.00"}, ]) for row in mongoCollection.find(): print("-" * 50) # 输出50个-,作为分界线 print("%-10s %s" % ("_id", int(row['_id']))) # 字段名固定10位宽度,并且左对齐 print("%-10s %s" % ("name", row['Name'])) print("%-10s %s" % ("age", row['Age'])) print("%-10s %s" % ("address", row['Address'])) print("%-10s %s" % ("salary", row['Salary'])) except pymongo.errors.PyMongoError as e: print("mongo Error:", e) traceback.print_exc()
5.连接Redis
5.1使用redis
import redis r = redis.Redis(host='localhost', port=6379, db=0, password="12345") print("connect", r.ping()) # 看信息 info = r.info() # or 查看部分信息 # info = r.info("Server") # 输出信息 items = info.items() for i, (key, value) in enumerate(items): print("item %s----%s:%s" % (i, key, value)) # 删除键和对应的值 r.delete("company") # 可以一次性push一条或多条数据 r.rpush("company", {"id": 1, "Name": "Paul", "Age": "32", "Address": "California", "Salary": "20000.00"}, {"id": 2, "Name": "Allen", "Age": "25", "Address": "Texas", "Salary": "15000.00"}, {"id": 3, "Name": "Teddy", "Age": "23", "Address": "Norway", "Salary": "20000.00"}) r.rpush("company", {"id": 4, "Name": "Mark", "Age": "25", "Address": "Rich-Mond", "Salary": "65000.00"}) r.rpush("company", {"id": 5, "Name": "David", "Age": "27", "Address": "Texas", "Salary": "85000.00"}) r.rpush("company", {"id": 6, "Name": "Kim", "Age": "22", "Address": "South-Hall", "Salary": "45000.00"}) r.rpush("company", {"id": 7, "Name": "James", "Age": "24", "Address": "Houston", "Salary": "10000.00"}) # eval用来将dict格式的字符串转换成dict for row in map(lambda x: eval(x), r.lrange("company", 0, r.llen("company"))): print("-" * 50) # 输出50个-,作为分界线 print("%-10s %s" % ("_id", row['id'])) # 字段名固定10位宽度,并且左对齐 print("%-10s %s" % ("name", row['Name'])) print("%-10s %s" % ("age", row['Age'])) print("%-10s %s" % ("address", row['Address'])) print("%-10s %s" % ("salary", row['Salary'])) # 关闭当前连接 # r.shutdown() #这个是关闭redis服务端
5.2使用pyredis
import pyredis r = pyredis.Client(host='localhost', port=6379, database=0, password="12345") print("connect", r.ping().decode("utf-8")) # 看信息 # info = r.execute("info").decode() # or 查看部分信息 info = r.execute("info", "Server").decode() # 输出信息 print(info) # 删除键和对应的值 r.delete("company") # 可以一次性push一条或多条数据 r.rpush("company", '''{"id": 1, "Name": "Paul", "Age": "32", "Address": "California", "Salary": "20000.00"}''', '''{"id": 2, "Name": "Allen", "Age": "25", "Address": "Texas", "Salary": "15000.00"}''', '''{"id": 3, "Name": "Teddy", "Age": "23", "Address": "Norway", "Salary": "20000.00"}''') r.rpush("company", '''{"id": 4, "Name": "Mark", "Age": "25", "Address": "Rich-Mond", "Salary": "65000.00"}''') r.rpush("company", '''{"id": 5, "Name": "David", "Age": "27", "Address": "Texas", "Salary": "85000.00"}''') r.rpush("company", '''{"id": 6, "Name": "Kim", "Age": "22", "Address": "South-Hall", "Salary": "45000.00"}''') r.rpush("company", '''{"id": 7, "Name": "James", "Age": "24", "Address": "Houston", "Salary": "10000.00"}''') # eval用来将dict格式的字符串转换成dict for row in map(lambda x: eval(x), r.lrange("company", 0, r.llen("company"))): print("-" * 50) # 输出50个-,作为分界线 print("%-10s %s" % ("_id", row['id'])) # 字段名固定10位宽度,并且左对齐 print("%-10s %s" % ("name", row['Name'])) print("%-10s %s" % ("age", row['Age'])) print("%-10s %s" % ("address", row['Address'])) print("%-10s %s" % ("salary", row['Salary'])) # 关闭当前连接 r.close()
以上是python中常见数据库有哪些的详细内容。更多信息请关注PHP中文网其他相关文章!

Python列表切片的基本语法是list[start:stop:step]。1.start是包含的第一个元素索引,2.stop是排除的第一个元素索引,3.step决定元素之间的步长。切片不仅用于提取数据,还可以修改和反转列表。

ListSoutPerformarRaysin:1)DynamicsizicsizingandFrequentInsertions/删除,2)储存的二聚体和3)MemoryFeliceFiceForceforseforsparsedata,butmayhaveslightperformancecostsinclentoperations。

toConvertapythonarraytoalist,usEthelist()constructororageneratorexpression.1)intimpthearraymoduleandcreateanArray.2)USELIST(ARR)或[XFORXINARR] to ConconverTittoalist,请考虑performorefformanceandmemoryfformanceandmemoryfformienceforlargedAtasetset。

choosearraysoverlistsinpythonforbetterperformanceandmemoryfliceSpecificScenarios.1)largenumericaldatasets:arraysreducememoryusage.2)绩效 - 临界杂货:arraysoffersoffersOffersOffersOffersPoostSfoostSforsssfortasssfortaskslikeappensearch orearch.3)testessenforcety:arraysenforce:arraysenforc

在Python中,可以使用for循环、enumerate和列表推导式遍历列表;在Java中,可以使用传统for循环和增强for循环遍历数组。1.Python列表遍历方法包括:for循环、enumerate和列表推导式。2.Java数组遍历方法包括:传统for循环和增强for循环。

本文讨论了Python版本3.10中介绍的新“匹配”语句,该语句与其他语言相同。它增强了代码的可读性,并为传统的if-elif-el提供了性能优势

Python中的功能注释将元数据添加到函数中,以进行类型检查,文档和IDE支持。它们增强了代码的可读性,维护,并且在API开发,数据科学和图书馆创建中至关重要。


热AI工具

Undresser.AI Undress
人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool
免费脱衣服图片

Clothoff.io
AI脱衣机

Video Face Swap
使用我们完全免费的人工智能换脸工具轻松在任何视频中换脸!

热门文章

热工具

DVWA
Damn Vulnerable Web App (DVWA) 是一个PHP/MySQL的Web应用程序,非常容易受到攻击。它的主要目标是成为安全专业人员在合法环境中测试自己的技能和工具的辅助工具,帮助Web开发人员更好地理解保护Web应用程序的过程,并帮助教师/学生在课堂环境中教授/学习Web应用程序安全。DVWA的目标是通过简单直接的界面练习一些最常见的Web漏洞,难度各不相同。请注意,该软件中

螳螂BT
Mantis是一个易于部署的基于Web的缺陷跟踪工具,用于帮助产品缺陷跟踪。它需要PHP、MySQL和一个Web服务器。请查看我们的演示和托管服务。

SublimeText3汉化版
中文版,非常好用

SecLists
SecLists是最终安全测试人员的伙伴。它是一个包含各种类型列表的集合,这些列表在安全评估过程中经常使用,都在一个地方。SecLists通过方便地提供安全测试人员可能需要的所有列表,帮助提高安全测试的效率和生产力。列表类型包括用户名、密码、URL、模糊测试有效载荷、敏感数据模式、Web shell等等。测试人员只需将此存储库拉到新的测试机上,他就可以访问到所需的每种类型的列表。

SublimeText3 Mac版
神级代码编辑软件(SublimeText3)