Python MySQLdb - 类中的连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38076220/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Python MySQLdb - Connection in a class
提问by Mar?al Torà
I am making a Python project where I have to seek and retreive data from a database.
I tried making a class, in which I declare the connection and do my queries, here is moreless what I have so far.
我正在制作一个 Python 项目，我必须在其中从数据库中查找和检索数据。
我尝试创建一个类，在其中声明连接并进行查询，这是迄今为止我所拥有的。
import MySQLdb
dbc =("localhost","root","1234","users")
class sql:
db = MySQLdb.connect(dbc[0],dbc[1],dbc[2],dbc[3])
cursor = db.cursor()
def query(self,sql):
sql.cursor.execute(sql)
return sql.cursor.fetchone()
def rows(self):
return sql.cursor.rowcount
sqlI = sql()
print(sqlI.query("SELECT `current_points` FROM `users` WHERE `nick` = 'username';"))
So, the main problem is that the variable dband cursorare not callable from other def's/functions from the same Class. What I'd like to get, is a polished query, where I can make queries and retreive it's content. This would summarize my code, therefore I should do.
因此，主要问题是变量db和cursor不能从同一类的其他定义/函数调用。我想得到的是一个完善的查询，我可以在其中进行查询并检索其内容。这将总结我的代码，因此我应该这样做。
回答by carusot42
I usually use psycopg2 / postgres, but this is the basic DB class that I often use, with Python's SQLite as an example:
我通常使用psycopg2/postgres，但这是我经常使用的基本DB类，以Python的SQLite为例：
import sqlite3
class Database:
def __init__(self, name):
self._conn = sqlite3.connect(name)
self._cursor = self._conn.cursor()
def __enter__(self):
return self
def __exit__(self, exc_type, exc_val, exc_tb):
self.close()
@property
def connection(self):
return self._conn
@property
def cursor(self):
return self._cursor
def commit(self):
self.connection.commit()
def close(self, commit=True):
if commit:
self.commit()
self.connection.close()
def execute(self, sql, params=None):
self.cursor.execute(sql, params or ())
def fetchall(self):
return self.cursor.fetchall()
def fetchone(self):
return self.cursor.fetchone()
def query(self, sql, params=None):
self.cursor.execute(sql, params or ())
return self.fetchall()
This will let you use the Databaseclass either normally like db = Database('db_file.sqlite)or in a withstatement:
这将使您Database可以正常使用该类db = Database('db_file.sqlite)或在with语句中使用该类：
with Database('db_file.sqlite') as db:
# do stuff
and the connection will automatically commit and close when the withstatement exits.
并且当with语句退出时连接将自动提交和关闭。
Then, you can encapsulate specific queries that you do often in methods and make them easy to access. For example, if you're dealing with transaction records, you could have a method to get them by date:
然后，您可以将您经常使用的特定查询封装在方法中并使其易于访问。例如，如果您正在处理交易记录，您可以使用一种方法来按日期获取它们：
def transactions_by_date(self, date):
sql = "SELECT * FROM transactions WHERE transaction_date = ?"
return self.query(sql, (date,))
Here's some sample code where we create a table, add some data, and then read it back out:
下面是一些示例代码，我们在其中创建一个表，添加一些数据，然后将其读回：
with Database('my_db.sqlite') as db:
db.execute('CREATE TABLE comments(pkey INTEGER PRIMARY KEY AUTOINCREMENT, username VARCHAR, comment_body VARCHAR, date_posted TIMESTAMP)')
db.execute('INSERT INTO comments (username, comment_body, date_posted) VALUES (?, ?, current_date)', ('tom', 'this is a comment'))
comments = db.query('SELECT * FROM comments')
print(comments)
I hope this helps!
我希望这有帮助！
回答by Daniel Roseman
That's not how you write classes in Python. You need to define your connection and cursor inside the __init__method, and refer to them via self.
这不是你在 Python 中编写类的方式。您需要在__init__方法内定义连接和光标，并通过self.
class sql:
dbc = ("localhost","root","1234","users")
def __init__(self):
db = MySQLdb.connect(*self.dbc)
self.cursor = db.cursor()
def query(self,sql):
self.cursor.execute(sql)
return self.cursor.fetchone()
def rows(self):
return self.cursor.rowcount
回答by Ashish Mishra
You can use constructor for the connection. When the object of class will created the constructor will invoke automatically.
您可以使用构造函数进行连接。当类的对象将被创建时，构造函数将自动调用。
import MySQLdb
class Connection:
def __init__(self):
self.con=MySQLdb.connect("127.0.0.1","root","","db_name",3306)
self.cmd=self.con.cursor()
obj=Connection()
