Postgres/Mysql/Mongo is my work in the most commonly used databases. Now lists the basic operation of python operation of these three databases, as long as you master the basic operation, and then more training, in fact, you can apply the handy.
1. Connecting to the PG library
## Import psycopg2 package
import psycopg2
## Connect to a given database
conn = (database="zabbix", user="zabbix",password="zabbix", host="127.0.0.1", port="5432")
## Create cursors to perform database operations
cursor = ()
## Execute SQL commands
#("CREATE TABLE test_conn(id int, name text)")
#("INSERT INTO test_conn values(1,'haha')")
## Submitting SQL commands
#()
## Execute SQL SELECT command
("select * from drules;")
## Get the tuple returned by SELECT
rows = ()
print('druleid|proxy_hostid|name|iprange| delay|nextcheck|status')
for row in rows:
#print(row)
print(row[0],row[1],row[2],row[3],row[4],row[5])
## Close the cursor
()
## Close the database connection
()
2. Connecting to MySQL
2.1 Connecting to databases
Before connecting to the database, please check the following items:
- You have created the database TESTDB.
- You have created the table EMPLOYEE in the TESTDB database.
- The EMPLOYEE table fields are FIRST_NAME, LAST_NAME, AGE, SEX and INCOME.
- Connect to the database TESTDB using the user name "testuser", the password is "test123", you can set your own or directly use the root user name and its password, Mysql database user authorization please use the Grant command.
- The Python MySQLdb module is already installed on your machine. If you are not familiar with sql statements, you can visit our SQL Basics Tutorials
#!/usr/bin/python
# -*- coding: UTF-8 -*-
import MySQLdb
# Open a database connection
db = ("localhost", "testuser", "test123", "TESTDB", charset='utf8' )
# Use the cursor() method to get the operation cursor
cursor = ()
# Execute the SQL statement using the execute method! [Insert image description here](/cover1/?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,image_MjAyMDA3MTUxNjIxMDEzOC5wbmc=,size_16, color_FFFFFF,t_70,image/resize,m_lfit,w_962#pic_center)
("SELECT VERSION()")
# Get a piece of data using the fetchone() method
data = ()
print "Database version : %s " % data
# Close the database connection
()
2.2 Creating databases and tables
#!/usr/bin/python
# -*- coding: UTF-8 -*-
import MySQLdb
# Open a database connection
db = ("localhost", "testuser", "test123", "TESTDB", charset='utf8' )
# Use the cursor() method to get the operation cursor
cursor = ()
# If the table already exists use the execute() method to delete the table.
("DROP TABLE IF EXISTS EMPLOYEE")
# Create Data Table SQL Statement
sql = """CREATE TABLE EMPLOYEE (
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
INCOME FLOAT )"""
(sql)
# Close the database connection
()
2.3 Insertion of data
#!/usr/bin/python
# -*- coding: UTF-8 -*-
import MySQLdb
# Open a database connection
db = ("localhost", "testuser", "test123", "TESTDB", charset='utf8' )
# Use the cursor() method to get the operation cursor
cursor = ()
# SQL insert statement
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
LAST_NAME, AGE, SEX, INCOME)
VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
# Execute sql statements
(sql)
# Submitted to database for execution
()
except:
# Rollback in case there is any error
()
# Close the database connection
()
#!/usr/bin/python
# -*- coding: UTF-8 -*-
import MySQLdb
# Open a database connection
db = ("localhost", "testuser", "test123", "TESTDB", charset='utf8' )
# Use the cursor() method to get the operation cursor
cursor = ()
# SQL insert statement
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
LAST_NAME, AGE, SEX, INCOME) \
VALUES (%s, %s, %s, %s, %s )" % \
('Mac', 'Mohan', 20, 'M', 2000)
try:
# Execute sql statements
(sql)
# Submitted to database for execution
()
except:
# Rollback in case of error
()
# Close the database connection
()
2.4 Database query operations
Python querying Mysql uses the fetchone() method to fetch a single piece of data, and the fetchall() method to fetch multiple pieces of data.
- fetchone(): This method fetches the next query result set. The result set is an object
- fetchall(): receives all the returned lines.
- rowcount: This is a read-only property and returns the number of rows affected by the execution of the execute() method.
#!/usr/bin/python
# -*- coding: UTF-8 -*-
import MySQLdb
# Open a database connection
db = ("localhost", "testuser", "test123", "TESTDB", charset='utf8' )
# Use the cursor() method to get the operation cursor
cursor = ()
# SQL Query Statements
sql = "SELECT * FROM EMPLOYEE \
WHERE INCOME > %s" % (1000)
try:
# Execute SQL statements
(sql)
# Get a list of all records
results = ()
for row in results:
fname = row[0]
lname = row[1]
age = row[2]
sex = row[3]
income = row[4]
# Print results
print "fname=%s,lname=%s,age=%s,sex=%s,income=%s" % \
(fname, lname, age, sex, income )
except:
print "Error: unable to fecth data"
# Close the database connection
()
2.5 Database update operations
#!/usr/bin/python
# -*- coding: UTF-8 -*-
import MySQLdb
# Open a database connection
db = ("localhost", "testuser", "test123", "TESTDB", charset='utf8' )
# Use the cursor() method to get the operation cursor
cursor = ()
# SQL update statements
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')
try:
# Execute SQL statements
(sql)
# Submitted to database for execution
()
except:
# Rollback in case of error
()
# Close the database connection
()
2.6 Delete data operations
#!/usr/bin/python
# -*- coding: UTF-8 -*-
import MySQLdb
# Open a database connection
db = ("localhost", "testuser", "test123", "TESTDB", charset='utf8' )
# Use the cursor() method to get the operation cursor
cursor = ()
# SQL Delete Statement
sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)
try:
# Execute SQL statements
(sql)
# Submit changes
()
except:
# Rollback in case of error
()
# Close the connection
()
3. Connect to the Mongo library
3.1 Determining whether a library exists
#!/usr/bin/python3
import pymongo
myclient = ("mongodb://localhost:27017/")
dblist = myclient.list_database_names()
# dblist = myclient.database_names()
if "runoobdb" in dblist:
print("Database already exists!")
3.2 Creating collections (tables)
#!/usr/bin/python3
import pymongo
myclient = ("mongodb://localhost:27017/")
mydb = myclient["runoobdb"]
collist = mydb. list_collection_names()
# collist = mydb.collection_names()
if "sites" in collist: # Determine if the sites collection exists
print("The collection already exists!")
else:
mycol = mydb["sites"]
3.3 Insertion of collections
#!/usr/bin/python3
import pymongo
myclient = ("mongodb://localhost:27017/")
mydb = myclient["runoobdb"]
mycol = mydb["sites"]
mydict = { "name": "RUNOOB", "alexa": "10000", "url": "" }
x = mycol.insert_one(mydict)
print(x)
print(x)
3.4 Returning the _id field
The insert_one() method returns the InsertOneResult object, which contains the inserted_id property, which is the id value of the inserted document.
#!/usr/bin/python3
import pymongo
myclient = ('mongodb://localhost:27017/')
mydb = myclient['runoobdb']
mycol = mydb["sites"]
mydict = { "name": "Google", "alexa": "1", "url": "" }
x = mycol.insert_one(mydict)
print(x.inserted_id)
3.5 Inserting Multiple Documents
Multiple documents are inserted into a collection using the insert_many() method, which takes a dictionary list as its first argument.
#!/usr/bin/python3
import pymongo
myclient = ("mongodb://localhost:27017/")
mydb = myclient["runoobdb"]
mycol = mydb["sites"]
mylist = [
{ "name": "Taobao", "alexa": "100", "url": "" },
{ "name": "QQ", "alexa": "101", "url": "" },
{ "name": "Facebook", "alexa": "10", "url": "" },
{ "name": "Knowing.", "alexa": "103", "url": "" },
{ "name": "Github", "alexa": "109", "url": "" }
]
x = mycol.insert_many(mylist)
# Output the _id values of all inserted documents.
print(x.inserted_ids)
3.6 Inserting Multiple Documents with a Specified _id
#!/usr/bin/python3
import pymongo
myclient = ("mongodb://localhost:27017/")
mydb = myclient["runoobdb"]
mycol = mydb["site2"]
mylist = [
{ "_id": 1, "name": "RUNOOB", "cn_name": ""},
{ "_id": 2, "name": "Google", "address": "Google Search"},
{ "_id": 3, "name": "Facebook", "address": "Facebook"},
{ "_id": 4, "name": "Taobao", "address": "Taobao"},
{ "_id": 5, "name": "Zhihu", "address": "Knowing."}
]
x = mycol.insert_many(mylist)
# Output the _id values of all inserted documents.
print(x.inserted_ids)
3.7 Querying a piece of data
Use the find_one() method to query for a piece of data in a collection.
Queries the first piece of data in the sites document:
#!/usr/bin/python3
import pymongo
myclient = ("mongodb://localhost:27017/")
mydb = myclient["runoobdb"]
mycol = mydb["sites"]
x = mycol.find_one()
print(x)
3.8 Querying all data in a collection
The find() method queries all the data in a collection, similar to the SELECT * operation in SQL.
#!/usr/bin/python3
import pymongo
myclient = ("mongodb://localhost:27017/")
mydb = myclient["runoobdb"]
mycol = mydb["sites"]
for x in ():
print(x)
3.9 Querying Data in Specified Fields
You can use the find() method to query for data in a specified field, setting the corresponding value of the field to be returned to 1.
#!/usr/bin/python3
import pymongo
myclient = ("mongodb://localhost:27017/")
mydb = myclient["runoobdb"]
mycol = mydb["sites"]
for x in ({},{ "_id": 0, "name": 1, "alexa": 1 }):
print(x)
Except for _id you can't specify both 0 and 1 in an object; if you set one field to 0, everything else is 1, and vice versa.
#!/usr/bin/python3
import pymongo
myclient = ("mongodb://localhost:27017/")
mydb = myclient["runoobdb"]
mycol = mydb["sites"]
for x in ({},{ "alexa": 0 }):
print(x)
3.10 Queries based on specified conditions
#!/usr/bin/python3
import pymongo
myclient = ("mongodb://localhost:27017/")
mydb = myclient["runoobdb"]
mycol = mydb["sites"]
myquery = { "name": "RUNOOB" }
mydoc = (myquery)
for x in mydoc:
print(x)
3.11 Advanced queries
The following example is used to read the first letter of the name field with an ASCII value greater than "H", with the greater-than modifier condition {"$gt": "H"} :
#!/usr/bin/python3
import pymongo
myclient = ("mongodb://localhost:27017/")
mydb = myclient["runoobdb"]
mycol = mydb["sites"]
myquery = { "name": { "$gt": "H" } }
mydoc = (myquery)
for x in mydoc:
print(x)
3.12 Querying with Regular Expressions
The regular expression modifier is only used to search for fields in a string.
The following example is used to read data where the first letter in the name field is "R", with the regular expression modifier condition {"$regex":"^R"} :
#!/usr/bin/python3
import pymongo
myclient = ("mongodb://localhost:27017/")
mydb = myclient["runoobdb"]
mycol = mydb["sites"]
myquery = { "name": { "$regex": "^R" } }
mydoc = (myquery)
for x in mydoc:
print(x)
3.13 Returning a specified number of records
If we want to set a specified number of rows on the query result we can use the limit() method, which accepts only one numeric parameter.
import pymongo
myclient = ("mongodb://localhost:27017/")
mydb = myclient["runoobdb"]
mycol = mydb["sites"]
myresult = ().limit(3)
# Output results
for x in myresult:
print(x)
3.14 Modification of data
to modify a record in a document in MongoDB using the update_one() method. The first parameter of the method is the query condition and the second parameter is the field to be modified.
If more than one match is found, only the first one will be modified.
import pymongo
myclient = ("mongodb://localhost:27017/")
mydb = myclient["runoobdb"]
mycol = mydb["sites"]
myquery = { "alexa": "10000" }
newvalues = { "$set": { "alexa": "12345" } }
mycol.update_one(myquery, newvalues)
# Output the modified "sites" collection
for x in ():
print(x)
The update_one() method can only fix the first row that matches, if you want to modify all the matched rows, you can use update_many().
The following example will look for all name fields beginning with F and change the alexa field to 123 for all records it matches:
import pymongo
myclient = ("mongodb://localhost:27017/")
mydb = myclient["runoobdb"]
mycol = mydb["sites"]
myquery = { "name": { "$regex": "^F" } }
newvalues = { "$set": { "alexa": "123" } }
x = mycol.update_many(myquery, newvalues)
print(x.modified_count, "The document has been modified.")
3.15 Sorting
The sort() method allows you to specify an ascending or descending sort.
The first argument to the sort() method is the field to be sorted, and the second specifies the sorting rule, 1 for ascending, -1 for descending, and the default is ascending.
#!/usr/bin/python3
import pymongo
myclient = ("mongodb://localhost:27017/")
mydb = myclient["runoobdb"]
mycol = mydb["sites"]
mydoc = ().sort("alexa")
for x in mydoc:
print(x)
3.16 Deletion of data
Use the delete_one() method to delete a document. The first argument to the method is a query object that specifies what data to delete.
#!/usr/bin/python3
import pymongo
myclient = ("mongodb://localhost:27017/")
mydb = myclient["runoobdb"]
mycol = mydb["sites"]
myquery = { "name": {"$regex": "^F"} }
x = mycol.delete_many(myquery)
print(x.deleted_count, "This file has been deleted.")
Delete multiple documents
The delete_many() method deletes multiple documents. The first argument to the method is a query object that specifies which data to delete.
Delete all documents that begin with F in the name field.
#!/usr/bin/python3
import pymongo
myclient = ("mongodb://localhost:27017/")
mydb = myclient["runoobdb"]
mycol = mydb["sites"]
x = mycol.delete_many({})
print(x.deleted_count, "This file has been deleted.")
3.17 Deleting all documents in a collection
The delete_many() method deletes all documents in the collection if passed an empty query object:
#!/usr/bin/python3
import pymongo
myclient = ("mongodb://localhost:27017/")
mydb = myclient["runoobdb"]
mycol = mydb["sites"]
x = mycol.delete_many({})
print(x.deleted_count, "This file has been deleted.")
3.17 Deleting Collections
Use the drop() method to delete a collection.
The following example removes the customers collection:
#!/usr/bin/python3
import pymongo
myclient = ("mongodb://localhost:27017/")
mydb = myclient["runoobdb"]
mycol = mydb["sites"]
()
This article on the basic operation of Python connection Postgres/Mysql/Mongo database is introduced to this article, more related Python connection to the database content, please search for my previous articles or continue to browse the following related articles I hope that you will support me in the future!