Python MySQLdb-类中的连接 [英] Python MySQLdb - Connection in a class

问题描述

我正在制作一个Python项目，必须从数据库中查找和检索数据.
我尝试制作一个类，在其中声明连接并进行查询，这是到目前为止我所学的.

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.

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';"))

因此，主要问题是变量db和cursor不能从同一类的其他def/函数中调用.我想要得到的是一个精细的查询，在这里我可以进行查询并检索其内容.这将总结我的代码，因此我应该这样做.

So, the main problem is that the variable db and cursor are 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.

推荐答案

我通常使用psycopg2/postgres，但这是我经常使用的基本数据库类，以Python的SQLite为例:

I usually use psycopg2 / postgres, but this is the basic DB class that I often use, with Python's SQLite as an example:

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()

这将使您可以正常使用db = Database('db_file.sqlite)或在with语句中使用Database类:

This will let you use the Database class either normally like db = Database('db_file.sqlite) or in a with statement:

with Database('db_file.sqlite') as db:
    # do stuff

，并且with语句退出时，连接将自动提交并关闭.

and the connection will automatically commit and close when the with statement exits.

然后，您可以封装在方法中经常执行的特定查询，并使它们易于访问.例如，如果您要处理交易记录，则可以使用一种按日期获取记录的方法:

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)

我希望这会有所帮助！

这篇关于Python MySQLdb-类中的连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！