EzDevInfo.com

PyMysql UPDATE query

I've been trying using PyMysql and so far everything i did worked (Select/insert) but when i try to update it just doesn't work, no errors no nothing, just doesn't do anything.

import pymysql
connection = pymysql.connect(...)
cursor = connection.cursor()
cursor.execute("UPDATE Users SET IsConnected='1' WHERE Username='test'")
cursor.close()
connection.close()

and yes I've double checked that Users, IsConnected and Username are all correct and test does exist (SELECT works on it)

what's my problem here?

Pymysql Insert Into not working

I'm running this from PyDev in Eclipse...

import pymysql
conn = pymysql.connect(host='localhost', port=3306, user='bbos', passwd='bbos', db='fan')
cur = conn.cursor()
print "writing to db"
cur.execute("INSERT INTO cbs_transactions(leagueID) VALUES ('test val')")
print "wrote to db"

The result is, at the top of the Console it says C:...test.py, and in the Console:

writing to db wrote to db

So it's not terminating until after the execute command. But when I look in the table in MySQL it's empty. A record did not get inserted.

First off, why isn't it writing the record. Second, how can I see a log or error to see what happened. Usually there should be some kind of error in red if the code fails.

Intermittently can't connect to mysql on AWS RDS (Error 2003)

We are having an intermittent issue with connections to our mysql server timing out. The error we are receiving is as following.

(2003, 'Can\'t connect to MySQL server on \'<connection>\' ((2013, "Lost connection to MySQL server during query (error(104, \'Connection reset by peer\'))"))') Callstack: File "/usr/lib64/python2.7/site-packages/pymysql/connections.py", line 818, in _connect 2003, "Can't connect to MySQL server on %r (%s)" % (self.host, e)) File "/usr/lib64/python2.7/site-packages/pymysql/connections.py", line 626, in __init__ self._connect()

Some more info:

• We have a flight of EC2 servers that are constantly running queries to a backend RDS.
• We average about 500 connections per second to the RDS
• We have around 0 - 4 hiccups per RDS per day
• The hiccups don't correspond with our maintenance window
• When we hit a hiccup it can affect quite a few connections ~50
• When a hiccup happens it will disrupt connections across all servers and ports

The error itself looks to be generated from the tcp connection being closed on the ec2. Our TCP keep alive time is set to 7200 seconds and that's when the error is fired off.

My question is what can be done to track down why these hiccups happen? It's great that they don't happen often, but it's not ideal that they happen at all.

Any advice would be appreciated thanks!

Update 10/29:

I've been running a service checking to see if I have any long processes running on the sql server and it looks like these errors aren't getting that far. A new process is never created for this connection! I have still been receiving the hiccups, just no signs of connections.

Why do JSON queries return object if there is one element, list if more than one?

I had to rewrite a python script from python2 to python 3 to solve the encoding problems I had the easiest way. I had to move from mysqldb to pymysql which seemed to use same syntax. I accessed the pymysql's github[1] site and following examples I noticed that when query result was one element it returned a JSON object but when it returns more than one, it returns a list.

Wouldn't it be more consistent to return always a list with 0, 1 or whichever number of elements? Why is it done this way?

Note: to avoid this behavior in pymysql just remove the cursorclass parameter from:

# Connect to the database
connection = pymysql.connect(host='localhost',user='user',
passwd='passwd', db='db', charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)

[1] https://github.com/PyMySQL/PyMySQL/

pymysql lost connection after a long period

using pymysql connect to mysql, leave the program running for a long time,for example, leave the office at night and come back next morning.during this period,no any operation on this application.now doing a database commit would give this error.

  File "/usr/local/lib/python3.3/site-packages/pymysql/cursors.py", line 117, in execute
    self.errorhandler(self, exc, value)
  File "/usr/local/lib/python3.3/site-packages/pymysql/connections.py", line 189, in defaulterrorhandler
    raise errorclass(errorvalue)

pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query')

restart the web server (tornado),it's ok.why if leave it long time would get this error?

django 1.5 + pymysql error: ImportError: cannot import name Thing2Literal

I try to use django1.5 and pymysql as MySQLdb as here How to make Django work with unsupported MySQL drivers such as gevent-mysql or Concurrence's MySQL driver?

In the top of my management command:

+try:
+    import pymysql
+    pymysql.install_as_MySQLdb()
+except ImportError:
+    pass 

but get error:

/local/lib/python2.7/site-packages/django/db/backends/mysql/base.py", line 30, in <module>
    from MySQLdb.converters import conversions, Thing2Literal
ImportError: cannot import name Thing2Literal

how to fix it?

Impossible to insert data with PyMySQL when I use parameter

I'm currently working on a basic query which insert data depending on the input parameters, and I'm unable to perfom it.

cur.execute("INSERT INTO foo (bar1, bar2) values (?, ?)", (foo1, foo2))

I have this error message:

Exception in Tkinter callback Traceback (most recent call last):
File "/usr/lib/python3.2/tkinter/init.py", line 1426, in call return self.func(*args) File "test.py", line 9, in register cur.execute("INSERT INTO foo (bar1, bar2) values (?,?)", (foo1, foo2)) File "/usr/local/lib/python3.2/dist-packages/pymysql/cursors.py", line 108, in execute query = query % escaped_args TypeError: unsupported operand type(s) for %: 'bytes' and 'tuple'

foo1 and foo2 are both string type. I tried with %s, same error.

Installing PyMySQL on a Windows 7 machine

I am not a python user and though I have used MySQL, I'm not an expert. Also, I'm mainly a Windows user and I don't know much about running command line scripts. So this might be a stupid question but I want to ask anyway...

(BTW my purpose is to install 'Baseball On A Stick')

I installed Python (version 3.3 on a Windows 7 machine).

Next it says to install pymysql. So I downloaded the .zip file from https://github.com/petehunt/PyMySQL/

The Readme says "Simply run the build-py3k.sh script from the local directory. It will build a working package in the ./py3k directory." I have absolutely no idea what this means. How do I run this? I tried typing it into the Python command line interface and it just gave me an error.

Python3 mysqlclient-1.3.6 (aka PyMySQL) usage?

Im still very much learning python and all the different ways to use 3rd party modules. I have installed https://pypi.python.org/pypi/mysqlclient which was recommended here Python 3 and MySQL

I believe i installed the package correctly

D:\install\python modules>python -m pip install mysqlclient-1.3.6-cp34-none-win_amd64.whl
Unpacking d:\install\python modules\mysqlclient-1.3.6-cp34-none-win_amd64.whl
Installing collected packages: mysqlclient
Successfully installed mysqlclient
Cleaning up...

weird thing is when i try and import the module mysqlclient I get the below

D:\install\python modules>python
Python 3.4.2 (v3.4.2:ab2c023a9432, Oct  6 2014, 22:16:31) [MSC v.1600 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import mysqlclient
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ImportError: No module named 'mysqlclient'

I checked the homepage https://github.com/PyMySQL/mysqlclient-python and I couldnt find any examples on how to use this module. I am quite confused, have I just majorly missed the boat here?

Python mysql (using pymysql) auto reconnect

I'm not sure if this is possible, but I'm looking for a way to reconnect to mysql database when the connection is lost. All the connections are held in a gevent queue but that shouldn't matter I think. I'm sure if I put some time in, I can come up with a way to reconnect to the database. However I was glancing pymysql code and I saw that there is a 'ping' method in Connection class, which I'm not sure exactly how to use.

The method looks like it will reconnect first time but after that it switched the reconnect flag to False again? Can I use this method, or is there a different way to establish connection if it is lost? Even if it is not pymysql how do people tackle, database servers going down and having to re-establish connection to mysql server?

def ping(self, reconnect=True):
    ''' Check if the server is alive '''
    if self.socket is None:
        if reconnect:
            self._connect()
            reconnect = False
        else:
            raise Error("Already closed")
    try:
        self._execute_command(COM_PING, "")
        return self._read_ok_packet()
    except Exception:
        if reconnect:
            self._connect()
            return self.ping(False)
        else:
            raise

TypeError: unsupported operand type(s) for %: 'bytes' and 'tuple' [closed]

TypeError: unsupported operand type(s) for %: 'bytes' and 'tuple'

The setup is as follows: Eloipool is a bitcoinmining pool software http://gitorious.org/bitcoin/eloipool PyMySQL is required for the MySQL database connection https://github.com/petehunt/PyMySQL .

The system is ubuntu 12.04.

root@j064:/opt/PyMySQL# locate Python.h
/usr/include/python2.7/Python.h
/usr/include/python3.2mu/Python.h
/usr/include/wx-2.8/wx/wxPython/wxPython.h
root@j064:/opt/PyMySQL#

Eloipool (Python3 and up) is located in /opt/eloipool/ PyMySQL is located in /opt/PyMySQL/pymysql/ PyMySQL comes with a Python3 compilant patch located in /opt/PyMySQL/py3k/pymysql/

I tried symlinks to both versions in the eloipool folder, the default version will ask for all subfolders and files from /opt/PyMySQL/pymysql/, I could not even try as pymysql and eloipool have a file called util.py.

When I symlink the /opt/PyMySQL/py3k/pymysql/ version I get the following error when running eloipool:

root@j064:/opt/eloipool# ./eloipool.py

>>> 2012-11-05 17:27:28,138     merkleMaker     INFO    New block: 000000000000026ea479ce7585db76c170190fce7bc46c0406767523d1846e96 (height: 206615; bits: 1a0513c5)
2012-11-05 17:27:28,246 restoreState    INFO    Restoring saved state from 'eloipool.worklog' (196 bytes)
2012-11-05 17:27:28,248 restoreState    INFO    State restored successfully
2012-11-05 17:27:28,248 restoreState    INFO    Total downtime: 96.0659 seconds
2012-11-05 17:27:28,295 JSONRPCServer   INFO    Waiting 13.1 seconds to longpoll
2012-11-05 17:27:29,214 JSONRPCServer   INFO    Ignoring longpoll attempt while another is waiting
2012-11-05 17:27:41,431 JSONRPCServer   INFO    Longpoll woke up 1 clients in 0.000 seconds
2012-11-05 17:28:28,353 WorkLogPruner   DEBUG   Pruned 0 jobs
2012-11-05 17:29:28,389 WorkLogPruner   DEBUG   Pruned 0 jobs
2012-11-05 17:29:33,805 JSONRPCHandler  ERROR   Error during JSON-RPC call: doJSON_getwork['00000002d1846e96067675237bc46c0470190fce85db76c1a479ce750000026e000000002b3b2cd67c438323e27e2d23671e4cf07e28c0c96756f5e0edef21d4d6c502795097f74d1a0513c5eef5f556000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000']
Traceback (most recent call last):
  File "/opt/eloipool/jsonrpcserver.py", line 196, in _doJSON_i
    rv = getattr(self, method)(*params)
  File "/opt/eloipool/jsonrpc_getwork.py", line 44, in doJSON_getwork
    return self.doJSON_submitwork(data)
  File "/opt/eloipool/jsonrpc_getwork.py", line 79, in doJSON_submitwork
    self.server.receiveShare(share)
  File "./eloipool.py", line 457, in receiveShare
    i(share)
  File "/opt/eloipool/sharelogging/sql.py", line 65, in logShare
    dbc.execute(stmt, params)
  File "/opt/eloipool/pymysql/cursors.py", line 108, in execute
    query = query % escaped_args
TypeError: unsupported operand type(s) for %: 'bytes' and 'tuple'


>>> exit()
2012-11-05 17:29:38,577 stopServers     INFO    Stopping servers...
2012-11-05 17:29:38,577 Waker for BitcoinNode   DEBUG   Read wakeup
2012-11-05 17:29:38,587 saveState       INFO    Saving work state to 'eloipool.worklog'...
2012-11-05 17:29:38,603 exit    INFO    Goodbye...
Terminated
root@j064:/opt/eloipool#

The MYSQL schema:

-- phpMyAdmin SQL Dump
-- version 3.4.10.2
-- http://www.phpmyadmin.net
--
-- Host: localhost:3306
-- Erstellungszeit: 05. Nov 2012 um 17:55
-- Server Version: 5.5.24
-- PHP-Version: 5.3.10

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Datenbank: `simplec`
--

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `accountBalance`
--

CREATE TABLE IF NOT EXISTS `accountBalance` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `userId` int(255) NOT NULL,
  `balance` varchar(40) DEFAULT NULL,
  `sendAddress` varchar(255) DEFAULT '',
  `paid` varchar(40) DEFAULT '0',
  `threshold` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `userId` (`userId`),
  KEY `b_userId` (`userId`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=706 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `locks`
--

CREATE TABLE IF NOT EXISTS `locks` (
  `name` varchar(11) NOT NULL,
  `locked` tinyint(4) NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `networkBlocks`
--

CREATE TABLE IF NOT EXISTS `networkBlocks` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `blockNumber` int(255) NOT NULL,
  `timestamp` int(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5876 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `news`
--

CREATE TABLE IF NOT EXISTS `news` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `message` varchar(255) NOT NULL,
  `timestap` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `title` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT ' ',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `pool_worker`
--

CREATE TABLE IF NOT EXISTS `pool_worker` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `associatedUserId` int(255) NOT NULL,
  `username` char(50) DEFAULT NULL,
  `password` char(255) DEFAULT NULL,
  `allowed_hosts` text,
  PRIMARY KEY (`id`),
  KEY `p_username` (`username`),
  KEY `pool_worker_associatedUserId` (`associatedUserId`),
  KEY `pool_worker_username` (`username`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1398 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `settings`
--

CREATE TABLE IF NOT EXISTS `settings` (
  `setting` varchar(255) NOT NULL,
  `value` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`setting`),
  KEY `set_setting` (`setting`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `shares`
--

CREATE TABLE IF NOT EXISTS `shares` (
  `id` bigint(30) NOT NULL AUTO_INCREMENT,
  `rem_host` varchar(255) NOT NULL,
  `username` varchar(120) NOT NULL,
  `our_result` enum('Y','N') NOT NULL,
  `upstream_result` enum('Y','N') DEFAULT NULL,
  `reason` varchar(50) DEFAULT NULL,
  `solution` varchar(257) NOT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `shares_username` (`username`),
  KEY `shares_time` (`time`),
  KEY `shares_our_result` (`our_result`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `shares_counted`
--

CREATE TABLE IF NOT EXISTS `shares_counted` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `blockNumber` int(11) NOT NULL,
  `userId` int(11) NOT NULL,
  `count` int(11) NOT NULL,
  `invalid` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=614 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `unconfirmed_rewards`
--

CREATE TABLE IF NOT EXISTS `unconfirmed_rewards` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `userId` int(255) NOT NULL,
  `blockNumber` int(255) NOT NULL,
  `amount` varchar(40) NOT NULL,
  `rewarded` enum('N','Y') NOT NULL DEFAULT 'N',
  `shares` int(255) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `unconfirmed_rewards_blockNumber` (`blockNumber`),
  KEY `unconfirmed_rewards_rewarded` (`rewarded`),
  KEY `unconfirmed_rewards_userId` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `userHashrates`
--

CREATE TABLE IF NOT EXISTS `userHashrates` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `userId` int(255) NOT NULL,
  `hashrate` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `timestamp` (`timestamp`),
  KEY `userHashrates_id1` (`userId`),
  KEY `userId_timestamp` (`userId`,`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `webUsers`
--

CREATE TABLE IF NOT EXISTS `webUsers` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `admin` int(1) NOT NULL,
  `username` varchar(40) NOT NULL,
  `pass` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL COMMENT 'Assocaited email: used for validating users, and re-setting passwords',
  `emailAuthPin` varchar(10) NOT NULL COMMENT 'The pin required to authorize that email address',
  `secret` varchar(10) NOT NULL,
  `loggedIp` varchar(255) NOT NULL,
  `sessionTimeoutStamp` int(255) NOT NULL,
  `accountLocked` int(255) NOT NULL COMMENT 'This is the timestamp when the account will be unlocked(usually used to lock accounts that are trying to be bruteforced)',
  `accountFailedAttempts` int(2) NOT NULL COMMENT 'This counts the number of failed attempts for web login',
  `pin` varchar(255) NOT NULL COMMENT 'four digit pin to allow account changes',
  `share_count` int(11) NOT NULL DEFAULT '0',
  `stale_share_count` int(11) NOT NULL DEFAULT '0',
  `shares_this_round` int(11) NOT NULL DEFAULT '0',
  `api_key` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `activeEmail` int(1) DEFAULT NULL,
  `donate_percent` varchar(11) DEFAULT '1',
  `btc_lock` enum('0','1') NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `u_username` (`username`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=710 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `winning_shares`
--

CREATE TABLE IF NOT EXISTS `winning_shares` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `blockNumber` int(11) NOT NULL,
  `username` varchar(11) NOT NULL,
  `share_id` int(255) NOT NULL DEFAULT '0',
  `rewarded` enum('N','Y') NOT NULL DEFAULT 'N',
  `amount` varchar(40) NOT NULL DEFAULT '0',
  `confirms` smallint(6) NOT NULL DEFAULT '0',
  `txid` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `scored` enum('N','Y') NOT NULL DEFAULT 'N',
  PRIMARY KEY (`id`),
  KEY `winning_shares_scored` (`scored`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=14 ;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

My conclusion is I either installed PyMySQL the wrong way, I useed the wrong python version or its broken as a buddy claimed.

However, this really is not a flake post, I urgently need to get this to work with my limited knowledge of the linux os, thanks for all constructive responses in advance.

At what point is MySQL primary key error thrown?

If I have a batch insert statement like:

INSERT INTO TABLE VALUES (x,y,z),(x2,y2,z2),(x3,y3,z3);

And x2 violates a primary key, is the error thrown before or after the processing of x3?

Specifically, I have a bunch of batch inserts in a try-catch block using Python and PyMySQL, like:

conn = myDB.cursor() 
try:
     conn.execute("INSERT INTO TABLE VALUES (x,y,z),(x2,y2,z2),(x3,y3,z3);")
except pymysql.Error as  msg:
     print("MYSQL ERROR!:{0}".format(msg)) #print error

I want to make sure that if one of the tuples in the batch insert fails, thus printing the error, the rest of the tuples in that same batch were still processed.

My motivation is that I am transferring LOTS of data across two servers. In server 1 the data is stored in log files, and it is being inserted into MySQL on server 2. Some of the data is already in MySQL on server 2, so there are many failures. However, if I do not use batch inserts, and I have a separate INSERT INTO for every of the (millions of) records things seem to run much more slowly. So I'm in trouble either way: with batch inserts, duplicate failures blow up the whole statement, and without batch inserts the process takes much much longer.

Python3: Does mysql db connection need to be explicitly closed in function?

Do you have to close mysql (pymysql) connection explicitly within a function in python3, or can you let python3 take care of it automatically as it gets out of scope.

Write query rows in csv python

I'm wrinting a script to write query results rows in a csv file. The data is email adresses, like this :

email1@mail.com

email2@mail.com

email3@mail.com

For now, my code writes my results in the csv file like this :

('email1@mail.com'), ('email2@mail.com'), ('email3@mail.com'),

How can I use next lines instead of rows ?

EDIT : It seems that my query gives me data between brackets like ('email1@mail.com'),

Here is the code :

import pymysql
import csv

db = pymysql.connect(host="localhost", port=3306, user="foo", passwd="bar", db="db")
cursor = db.cursor()

query3 = """SELECT email FROM tmp"""


cursor.execute(query)

data = cursor.fetchall()
list = []

for row in data :
  value = str(row)
  list.append(value)    
  file = open('file.csv', 'wb')
  data = csv.writer(file)
  data.writerow(list)

cursor.close()
db.close()
file.close()

Mock a MySQL database in Python

I use Python 3.4 from the Anaconda distribution. Within this distribution, I found the pymysql library to connect to an existing MySQL database, which is located on another computer.

import pymysql
config = {
      'user': 'my_user',
      'passwd': 'my_passwd',
      'host': 'my_host',
      'port': my_port
    }

    try:
        cnx = pymysql.connect(**config)
    except pymysql.err.OperationalError : 
        sys.exit("Invalid Input: Wrong username/database or password")

I now want to write test code for my application, in which I want to create a very small database at the setUp of every test case, preferably in memory. However, when I try this out of the blue with pymysql, it cannot make a connection.

def setUp(self):
    config = {
      'user': 'test_user',
      'passwd': 'test_passwd',
      'host': 'localhost'
    }
    cnx = pymysql.connect(**config)

pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on 'localhost' ([Errno 61] Connection refused)")

I have been googling around, and found some things about SQLite and MySQLdb. I have the following questions:

1. Is sqlite3 or MySQLdb suitable for creating quickly a database in memory?
2. How do I install MySQLdb within the Anaconda package?
3. Is there an example of a test database, created in the setUp? Is this even a good idea?

I do not have a MySQL server running locally on my computer.