通过创建模块来简化数据库(psycopg2)的使用 [英] Simplify database (psycopg2) usage by creating a module
问题描述
首先让我说我对Python还是很陌生，如果这不是解决这个问题的合适地点，我深表歉意.
Let me preface this by saying that I am fairly new to Python and I apologize if this is not the appropriate place for this question.
我正在使用 psycopg2 模块来处理PostgreSQL数据库.一般用法如下所示:
I am using the psycopg2 module to manipulate a PostgreSQL database. The general usage would look something like this:
# Example 1
import psycopg2
conn = psycopg2.connect(database="mydb", user="postgres")
cur = conn.cursor()
cur.execute ("SELECT * FROM mytable;")
rows = cur.fetchall()
for i, row in enumerate(rows):
print "Row", i, "value = ", row
cur.close()
conn.close()
这将打开与 mydb 数据库的连接，从表 mytable 中选择所有字段并打印它们，然后关闭连接.
This will open a connection to the mydb database, select all fields from the table mytable and print them, and then close the connection.
我想做的就是将其中一些功能分解为一个模块，因为我将需要在许多脚本中一遍又一遍地调用它们.对于此示例，理想情况下，我将有一个名为 core 的模块，其中包含三个功能:
What I would like to do is factor out some of these functions into a module because I will need to call them over and over throughout many scripts. For this example, ideally I would have a module named core which contains three functions:
- core.db_init()-打开连接，并由上面的前两行代码组成.
- core.db_query(query)-执行所需的SQL查询，并由上述代码中的第三行组成.
- core.db_close()-关闭连接并由最后两行组成.
我尝试如下创建模块:
# Module core.py
import psycopg2
def db_init():
conn = psycopg2.connect(database="mydb", user="postgres")
cur = conn.cursor()
def db_query(query):
cur.execute(query)
def db_close():
cur.close()
conn.close()
但是当我尝试使用此模块重新创建示例1时，出现命名空间错误:
But I get namespace errors when I try to recreate Example 1 using this module:
# Example 2
import core
core.db_init()
core.db_query("SELECT * FROM mytable;")
rows = cur.fetchall()
for i, row in enumerate(rows):
print "Row", i, "value = ", row
core.db_close()
我什至不确定模块实际上就是我想要的.我应该改用一个类吗?同样，我对所有这些都是新手.但是，如果有人可以帮助我找出一种更好的方法来完成此任务，我将非常感激.
I'm not even sure a module is actually what I want. Should I be using a class instead? Again, I'm very new to all of this. But if someone could help me figure out a better way to do this, I would be very grateful.
推荐答案
您的主要问题是，每个变量都限于您在其中编写的函数.
除非这样声明，否则:
Your main issue, is that each variable is limited to the function you wrote it in.
Unless otherwise declared like such:
def db_init():
global conn
conn = psycopg2....
更好的方法是将其转换为类，一个基本的示例是:
A better approach would be to convert this into a class, a basic example would be:
import psycopg2
class MyDatabase():
def __init__(self, db="mydb", user="postgres"):
self.conn = psycopg2.connect(database=db, user=user)
self.cur = self.conn.cursor()
def query(self, query):
self.cur.execute(query)
def close(self):
self.cur.close()
self.conn.close()
db = MyDatabase()
db.query("SELECT * FROM table;")
db.close()
现在，由于您正在使用cur.execute()，因此SELECT查询的作用不大.
但是我故意这样做，以使代码与您编写的代码相似，您将希望将其交换出来以返回值，但是，如果调用预期返回值的查询，等等.
Now, the SELECT query won't do much since you're using cur.execute().
But i kept this on purpose to keep the code similar to what you wrote, you'll want to swap that out to return the values however if calling a query that is expected to return a value and so on.
您专注于函数的方法将遇到命名空间"问题，其中变量位于该函数的局部范围内，而其他函数通常无法访问它们.
Your approach that is focused on functions will have "namespace" issues where variables live in a local scope of that function and there for other functions can't normally access them.
相反，类范围的变量可以访问其自己的变量，因此没有限制.
Instead, class scoped variables can access its own variables and is there for not as limited out of the box.
您可以创建全局变量并将其在函数中声明为全局变量，但我认为正如我在评论中提到的那样:
You could make global variables and declare them as global in the functions, but I think as I mentioned in a comment:
您想将其归为一类.数据库是基于会话的实体，就像类是会话实体一样.通过类抽象将每个连接作为活动实体处理，否则cur和conn将成为作用域变量，您需要将它们处理到全局作用域中.
You'd want to make this into a class. A database is a session based entity just as classes are session entities. Handle each connection as a living entity by class-abstracting it, otherwise cur and conn will become scoped variables and you need to work them into the global scope.
这篇关于通过创建模块来简化数据库(psycopg2)的使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
