DZone Snippets: db code

how to create a new user in MySQL

jm3 (john manoogian III) — Mon, 24 Mar 2008 04:06:23 GMT

// first, mysql -uroot mysql
// then:





  mysql% GRANT ALL PRIVILEGES ON *.* TO 'jm3_spoon'@'localhost' IDENTIFIED BY 'stirthatshit' WITH GRANT OPTION;

  mysql% create database jm3_agitator;

Select DataBase Schema

dubby (Dave) — Wed, 17 Oct 2007 11:30:21 GMT

// Select database schema.
//This could be used to recreate or test for existence of columns/tables
//or could also be used to create a database template system to enable the writing //of database schema into txt template file to be recreated again by reading the //txt file via an application
//
//You can also use SELECT * instead of defining each schema property (column)



SELECT TABLE_CATALOG

, TABLE_SCHEMA

, TABLE_NAME

, ORDINAL_POSITION

, COLUMN_DEFAULT

, IS_NULLABLE

, DATA_TYPE

, CHARACTER_MAXIMUM_LENGTH

, COLLATION_NAME 

FROM 

INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = (N'Persons')

Storm ORM by Canonical in WSGI enabled applications

sevkin (Vsevolod Balashov) — Fri, 10 Aug 2007 11:39:51 GMT



#!/usr/bin/env python

# vim:ts=4:sw=4:et

# (c) 2007 Vsevolod Balashov  under terms of LGPL 2.1

# use Storm ORM  in WSGI enabled applications



"""WSGI middleware for Storm.



This is the database access inteface for WSGI enabled (PEP 333) web applications.



Pylons framework example:



in wsgiapp.py



from storm.database import create_database

from middlestorm import MiddleStorm

...



# CUSTOM MIDDLEWARE HERE

app = MiddleStorm(app, create_database(config['app_conf']['sqlalchemy.default.uri']))



in controller:



class DemoController(BaseController):

    def index(self):

        store = request.environ['storm.store']

        ...

"""



from storm.database import Database

from storm.store import Store

from threading import local



__all__ = ["MiddleStorm"]

__author__ = "Vsevolod Balashov "

__version__ = "0.1"



class SingleConn(Database):

    """Database proxy class.

    Share single database connection between all Store object instances in threads.

    If you have readonly database or not use transactions why not?

    """

    def __init__(self, database):

        self._database = database

        self._connection = database.connect()

    def connect(self):

        return self._connection



class MiddleStorm(object):

    """WSGI middleware.

    Add Store object instance in environ['storm.store']. Each thread contains own instance.

    """



    def __init__(self, app, database, single = False):

        """Create WSGI middleware.

        @param app: up level application or middleware.

        @param database: instance of Database returned create_database.

        @param: single: use single database connection in all threads. 

        """

        assert isinstance(database, Database), \

            'database must be subclass of storm.database.Database'

        if single:

            self._database = SingleConn(database)

        else:

            self._database = database

        self._app = app

        self._local = local()



    def __call__(self, environ, start_response):

        try:

            environ['storm.store']  = self._local.store

        except AttributeError:

            environ['storm.store']  = \

                self._local.__dict__.setdefault('store', Store(self._database))

        return self._app(environ, start_response)

Simple mySQL backup script for cron

fak3r (fak3r) — Wed, 20 Jun 2007 15:01:39 GMT

Simple mySQL backup script for cron - backs up all databases, saves the last 4 copies.



#!/bin/bash



# modify the following to suit your environment

export DB_BACKUP="/backup/mysql_backup"

export DB_USER="root"

export DB_PASSWD="********"



# title and version

echo ""

echo "mySQL_backup"

echo "----------------------"

echo "* Rotating backups..."

rm -rf $DB_BACKUP/04

mv $DB_BACKUP/03 $DB_BACKUP/04

mv $DB_BACKUP/02 $DB_BACKUP/03

mv $DB_BACKUP/01 $DB_BACKUP/02

mkdir $DB_BACKUP/01 



echo "* Creating new backup..."

mysqldump --user=$DB_USER --password=$DB_PASSWD --all-databases | bzip2 > $DB_BACKUP/01/mysql-`date +%Y-%m-%d`.bz2

echo "----------------------"

echo "Done"

exit 0

php image from database using PEAR::DB

mikewilsonuk (Mike Wilson) — Mon, 26 Mar 2007 08:16:22 GMT

This fetches an image from a MySQL database (I know, a database may not be the best place to keep images). The image is sent to the web client as an image/jpeg.



require_once 'DB.php';



define('DB_SERVER', 'localhost');

define('DB_USER', 'php');

define('DB_PASS', '');

define('DB_DATABASE', 'images');

define('DSN', 'mysql://'.DB_USER.':'.DB_PASS.'@'.DB_SERVER.'/'.DB_DATABASE);



global $db;

$db = DB::connect(DSN);



if (PEAR::isError($db)) {

    die($db->getMessage());

}



if (isset($_GET['id'])) {

  header('Content-Type: image/jpeg');

  $id = $_GET['id'];

  $sql = "select image_data from images where id = ?";

  $result =& $db->query($sql, $id);



  if (PEAR::isError($result)) {

    die($result->getMessage());

  } else {

    if ($result->fetchInto($row)) {

      echo $row[0];

    }

  }



} else {

  echo file_get_contents('broken.png');

}

?>

Result Object Methods

morlandi (Mario Orlandi) — Fri, 17 Nov 2006 17:11:13 GMT

// Assuming that we have set result to being a result object we can use the following methods:

len(result)



     this will show the number rows returned (which would be 3 in the example above).

result.names()



    a list of all the column headings, returning a list containing emp_id, first, last and salary

result.tuples()



    returns a list of tuples in our example:



    [(43, 'Bob', 'Roberts', 50000),

     (101, 'Cheeta', 'leCat', 100000),

     (99, 'Jane', 'Junglewoman', 100001)]

result.dictionaries()



    will return a list of dictionaries, with one dictionary for each row:



    [{'emp_id': 42, 'first': 'Bob','last': 'Roberts', 'salary': 50000},

     {'emp_id': 101, 'first: 'Cheeta', 'last': 'leCat', 'salary': 100000},

     {'emp_id': 99, 'first': 'Jane', 'last': 'Junglewoman', 'salary': 100001}]

result.data_dictionary()



    returns a dictionary describing the structure of the results table. 



The dictionary has the key name, type, null and width. 

Name and type are self explanatory, null is true if that field may contain a null value and width is the width in characters of the field. 

Note that null and width may not be set by some Database Adapters.

result.asRDB()



    displays the result in a similar way to a relational database. 



The DTML below displays the result below:

    
      

    



    ... displays ...



    emp_id first last salary

    42 Bob Roberts 50000

    101 Cheeta leCat 100000

    99 Jane Junglewoman 100001

result[0][1]



    return row 0, column 1 of the result, bob in this example. 



Be careful using this method as changes in the schema will cause unexpected results.

Db Connection

morlandi (Mario Orlandi) — Mon, 16 Oct 2006 18:23:14 GMT

// eGenix mxODBC Database Connection: connection string sample



DSN=LocalServer;

DATABASE=GammaMedidata;

UID=sa;

PWD=******;

// eGenix mxODBC Database Connection: connection string sample



DRIVER=SQL Server;

SERVER=(local);

UID=sa;

PWD=****;

DATABASE=wam

// MySQL connection string sample



dbcobraf@www2.cobraf.com cobraf 'password'

python and sqllite : simplest example

manatlan (manatlan) — Thu, 08 Sep 2005 01:41:59 GMT



import sqlite



con = sqlite.connect('mydatabase.db')

cur = con.cursor()

#~ cur.execute('CREATE TABLE foo (o_id INTEGER PRIMARY KEY, fruit VARCHAR(20), veges VARCHAR(30))')

#~ con.commit()

cur.execute('INSERT INTO foo (o_id, fruit, veges) VALUES(NULL, "apple", "broccoli")')

con.commit()

print cur.lastrowid



cur.execute('SELECT * FROM foo')

print cur.fetchall()