bdarnell / torndb

A lightweight wrapper around MySQLdb. Originally part of the Tornado framework.

Torndb

Unmaintained

This package is not actively maintained, and since it exists primarily for backwards compatibility with the module provided in Tornado prior to version 3.0, I do not intend to make major changes or merge pull requests that do so. New projects are encouraged to choose a different database package. Anyone interested in producing an updated version of torndb is welcome to create a fork (under a new name)

Description

Torndb is a simple wrapper around MySQLdb that originally appeared in Tornado (http://www.tornadoweb.org). It is being moved into a separate package for Tornado 3.0.

Limitations

Torndb does not support Python 3, or any database drivers other than MySQLdb or pymysql.

Installation

pip install torndb

Documentation

http://torndb.readthedocs.org

torndb's Issues

get wrong answer using torndb.get api

my table's description below
+---------------+-------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| hash_info | varchar(45) | NO | UNI | NULL | |
| found_torrent | int(11) | YES | | 0 | |
| found_time | timestamp | NO | | CURRENT_TIMESTAMP | |
+---------------+-------------+------+-----+-------------------+----------------+

My sql command is
select count(*) as t from t_hash_info where date(found_time) = "2013-11-19";

I execute the same sql command both in mysql and with torndb get api, get different result.
At the same time, I do again using MySQLdb original api, get right answer. I dont know why.

when torn db will support python3 ?

Hello !
When torn db will support python3 ?

Thanks!

Update method not found in torndb.Connection

How do I use update method mentioned here https://torndb.readthedocs.org/en/latest/#torndb.Connection.update

Looking at the source Connection class doesn't have a update() method

about paging

Dose this package has no paging func?

DictCursor can be used in query

[Row(itertools.izip(column_names, row)) for row in cursor]

The DictCursor returns rows as dictionaries and stores the result set in the client.

http://mysql-python.sourceforge.net/MySQLdb.html

Does this package support Python 3?

Thanks.

The docstring of `get()` is ambiguous

The source of get():

def get(self, query, *parameters, **kwparameters):
    """Returns the first row returned for the given query."""
    rows = self.query(query, *parameters, **kwparameters)
    if not rows:
        return None
    elif len(rows) > 1:
        raise Exception("Multiple rows returned for Database.get() query")
    else:
        return rows[0]

Returns the first row returned for the given query.

I read this and think it use query() and only return the first row.

But the real implemention is that the query should only have zero or one result, if more than one will raise an error, I think the docstring is ambiguous.

Why not just only return the first row no matter how many rows are returned?

Same code, it`s fine in local, but fail in server. Why?

local is ok, but fail in server

local MySQL version:

mysql  Ver 14.14 Distrib 5.7.16, for osx10.12 (x86_64) using  EditLine wrapper

server MySQL version:

mysql  Ver 14.14 Distrib 5.6.30, for Linux (x86_64) using  EditLine wrapper

Traceback (most recent call last):
  File "/usr/lib64/python2.7/site-packages/tornado/web.py", line 1467, in _execute
    result = method(*self.path_args, **self.path_kwargs)
  File "/home/Server/controller/course_controller.py", line 67, in get
    cids = get_cids(self.db, org_ids)
  File "/home/Server/data_repository.py", line 123, in get_cids
    return cs_course_dao.get_org_by_cids(db, orgIds)
  File "/home/Server/dao/cs_course_dao.py", line 29, in get_org_by_cids
    records = db.query("SELECT id from course where org_id in %(orgId)s", orgId=orgIds)
  File "/usr/lib/python2.7/site-packages/torndb.py", line 136, in query
    self._execute(cursor, query, parameters, kwparameters)
  File "/usr/lib/python2.7/site-packages/torndb.py", line 234, in _execute
    return cursor.execute(query, kwparameters or parameters)
  File "/usr/lib64/python2.7/site-packages/MySQLdb/cursors.py", line 174, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib64/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1")

The torndb package in pypi is not the lastest stable version.

There are some changes, eg. add time_zone param to __init__.
But the new package has not submitted to pypi yet, so we can install the latest version from github only.

Thanks, Ben.

torndb Support mariadb ？

torndb Support mariadb ？

TypeError: can only concatenate list (not "function") to list

266. CONVERSIONS[field_type] = [(FLAG.BINARY, str)] + CONVERSIONS[field_type]

why torndb will close connection when meeting OperationalError

from the docstring, we know torndb is a wrapper of mysqldb, and support some easy used DML sql method.
the most important method is _execute and _cursor,you know

    def _cursor(self):
        self._ensure_connected()
        return self._db.cursor()

    def _execute(self, cursor, query, parameters, kwparameters):
        try:
            return cursor.execute(query, kwparameters or parameters)
        except OperationalError:
            logging.error("Error connecting to MySQL on %s", self.host)
            self.close()
            raise

but why when catch OperationalError, will close the connection?

from the mysql doc we know OperationalError just some exception not related to sql execute, but from pep249 , this error is related to the database's operation, so maybe some sql execute can cause the error

mysql doc: https://dev.mysql.com/doc/connector-python/en/connector-python-api-errors-operationalerror.html

This exception is raised for errors which are related to MySQL's operations. For example: too many connections; a host name could not be resolved; bad handshake; server is shutting down, communication errors.

mysql doc: https://dev.mysql.com/doc/connector-python/en/connector-python-api-errors.html

he exception classes defined in this module mostly follow the Python Database API Specification v2.0 (PEP 249). For some MySQL client or server errors it is not always clear which exception to raise. It is good to discuss whether an error should be reclassified by opening a bug report.

pep249: https://www.python.org/dev/peps/pep-0249/

OperationalError
Exception raised for errors that are related to the database's operation and not necessarily under the control of the programmer, e.g. an unexpected disconnect occurs, the data source name is not found, a transaction could not be processed, a memory allocation error occurred during processing, etc. It must be a subclass of DatabaseError.

for example:
when I execute a sql ,which a field type is not match table field schema, it also raise OperationalError

OperationalError(1366, "Incorrect integer value: 'aaaaa' for column 'rssi' at row 1")

in my example, this field schema of table is integer,but I put a string in sql

so if I put some sql in a transaction, when catch OperationalError, the connection is closed, mysql auto rollback, the rollback by myself has no effect.

Fortunately, I have extended tornndb's support for transactions in my code, but I also don`t know the reason of close connection when catch OperationalError

Even more confusing is according to mysql doc, 1366 will be DatabaseError, but in fact it raise OperationalError

server-error-reference

Error number: MY-011366; Symbol: ER_KEYRING_FAILED_TO_REMOVE_FILE; SQLSTATE: HY000
Message: Could not remove file %s OS retuned this error: %s

you see HY000

it will be DatabaseError when you check the table here connector-python-api-errors

when i use torndb as global var like `blog` demo

help me, how to fix it, thanks
*** Error in `python': double free or corruption (!prev): 0x000000000177da10 ***
段错误

NameError: global name 'CONVERSIONS' is not defined

import markdown
import os.path
import re
import torndb
import tornado.auth
import tornado.httpserver
import tornado.ioloop
import tornado.options
import tornado.web
import unicodedata

from tornado.options import define, options

define("port", default=8888, help="run on the given port", type=int)
define("mysql_host", default="localhost:3306", help="blog database host")
define("mysql_database", default="blog", help="blog database name")
define("mysql_user", default="blog", help="blog database user")
define("mysql_password", default="blog", help="blog database password")


class Application(tornado.web.Application):
    def __init__(self):
        handlers = [
            (r"/", HomeHandler),
            (r"/archive", ArchiveHandler),
            (r"/feed", FeedHandler),
            (r"/entry/([^/]+)", EntryHandler),
            (r"/compose", ComposeHandler),
            (r"/auth/login", AuthLoginHandler),
            (r"/auth/logout", AuthLogoutHandler),
        ]
        settings = dict(
            blog_title=u"Tornado Blog",
            template_path=os.path.join(os.path.dirname(__file__), "templates"),
            static_path=os.path.join(os.path.dirname(__file__), "static"),
            ui_modules={"Entry": EntryModule},
            xsrf_cookies=True,
            cookie_secret="__TODO:_GENERATE_YOUR_OWN_RANDOM_VALUE_HERE__",
            login_url="/auth/login",
            autoescape=None,
        )
        tornado.web.Application.__init__(self, handlers, **settings)

        # Have one global connection to the blog DB across all handlers
        self.db = torndb.Connection(
            host=options.mysql_host, database=options.mysql_database,
            user=options.mysql_user, password=options.mysql_password)

Add timeout and raise exception in __init__()

Hello

Maybe will good idea add timeout parameter to Connection()? And remove please in init() last try/except block, because don't have a good way to find problem with connection in this step

Got a traceback when working with python3.5

I got the following error when working with python3.
File "/usr/local/lib/python3.5/site-packages/torndb.py", line 138, in query#012 return [Row(itertools.izip(column_names, row)) for row in cursor]#12 File "/usr/local/lib/python3.5/site-packages/torndb.py", line 138, in #12 return [Row(itertools.izip(column_names, row)) for row in cursor]#012AttributeError: module 'itertools' has no attribute 'izip'

For python3, it should use "zip" instead of "itertools.izip"

MS SQL Support in future?

Hi, bdarnell.

Will torndb support MS SQL in future?
I've just create a new repo libdb based on torndb. If you think it is OK then I will fork a new copy of torndb and pull requests for MS SQL support.

Thanks.

Problem with datetime formatting

The problem might not be specific to datetime formatting, but that's where I'm stuck.

Consider the following query that executes fine directly on mysql:

mysql> SELECT STR_TO_DATE('01:35 PM', '%h:%i %p') as t;
+----------+
| t |
+----------+
| 13:35:00 |
+----------+

However, when I run the exact same query in torndb:

row = conn.get("SELECT STR_TO_DATE('01:35 PM', '%%h:%%i %%p') as t")
print ">>", row.t
I get the following warning (conn is correctly constructed using torndb.Connection):

/Library/Python/2.7/site-packages/torndb.py:226: Warning: Incorrect datetime value: '01:35 PM' for function str_to_date
return cursor.execute(query, kwparameters or parameters)

None

I looked at the mysqld logs, and it seems that both queries are identical in the server, only using torndb I see the following:
290 Query SELECT STR_TO_DATE('01:35 PM', '%h:%i %p') as t
290 Query SHOW WARNINGS

whereas when I run it from mysql, I only see the first line:
46 Query SELECT STR_TO_DATE('01:35 PM', '%h:%i %p') as t

What is going on here?

Parameters

torndb (and tornado.database before it) accept parameters to queries only on the basis of their order -so, for example, using torndb:

query( "SELECT * FROM table WHERE id IN (%s, %s, %s)", 101, 102, 103 )

will execute fine, and run the query:

SELECT * FROM table WHERE id IN (101, 102, 103)

However, MySQLdb also provides support for named parameters - as follows:

cursor.execute( 
"SELECT * FROM table WHERE name = %(name)s AND location = %(location)s", 
{"name": "george", "location":"jungle"} 
)

torndb currently has no way of using this functionality, and it'd be awesome if we could bring it in - so we could have something like:

query( "SELECT * FROM table WHERE name = %(name)s AND location = %(location)s", name="george", location="jungle" )

There's a couple of ways we could do this - one option would be change the query (and related methods) to be of the form query( query, *parameters, **named_parameters ), and fall back to named_parameters if parameters is empty. This is nice in that we don't introduce any additional methods, and it'll work with existing code, but I don't really like the fall back approach.

Alternatively, we could add some additional methods called (for example) query_named looking like query_named( query, **named_parameters ).

Query results in incorrect data

You'll think this is strange but check this out.

import torndb
database = torndb.Connection(host='localhost',user='root',password='', database='testclient')
print database.query("""select  DATE_FORMAT(from_unixtime(timestamp) - interval 5 hour, '%%Y-%%c-%%d') date, count(*)
from orders where from_unixtime(timestamp) between date '2013-01-01 00:00:00' and date '2013-02-01 00:00:00' group by date""")

Results in:

[{'date': u'2012-12-31', 'count(*)': 16L}, {'date': u'2013-1-02', 'count(*)': 52L}, {'date': u'2013-1-03', 'count(*)': 82L}, {'date': u'2013-1-04', 'count(*)': 100L}, {'date': u'2013-1-05', 'count(*)': 126L}, {'date': u'2013-1-06', 'count(*)': 98L}, {'date': u'2013-1-07', 'count(*)': 77L}, {'date': u'2013-1-08', 'count(*)': 97L}, {'date': u'2013-1-09', 'count(*)': 97L}, {'date': u'2013-1-10', 'count(*)': 50L}]

Same query as above... different results

Why are they different data?

query function did not work under certain circumstances

as described in title.
code:

names = ['aa', 'bb', 'cc']
db.query("select * from tab_name where name in %s", names)

name column is varchar(127)

I check mysql query log, the query that torndb using is:

select * from tab_name where name in ("'aa'", "'bb'", "'cc'")

while the right query should be

select * from tab_name where name in ("aa", "bb", "cc")

I think it is caused by environment, because the situation happened on two of my computers but not happen on another.

How can I solved this problem?

when operating mysql fails, the error log is always "error connecting mysql". we need a more detailed message。

torndb v0.2 on PyPI differs from the same version on readthedocs and GitHub

This will raise an TypeError: TypeError: __init__() got an unexpected keyword argument 'charset'

from torndb import Connection
Connection('localhost', 'user', charset="utf8mb4")

latest(version = "0.2" in the code) On ReadTheDocs and GitHub:

def __init__(self, host, database, user=None, password=None,
                 max_idle_time=7 * 3600, connect_timeout=0, 
                 time_zone="+0:00", charset = "utf8", sql_mode="TRADITIONAL"):

torndb v0.2 on PyPI

def __init__(self, host, database, user=None, password=None,
                 max_idle_time=7 * 3600, connect_timeout=0,
                 time_zone="+0:00"):

An important cue very upset

Why don't you update torndb's reference Pack: MySQLdb

Below the python3.4.3 no have MySQLdb, it was renamed to pymysql

I just refer to the torndb No in my project: "module named'MySQLdb'"

Why, I am very, very, very upset, why you don't update, now have a lot of people use python3.4.3

please.............please...............