gabrielstein.org

Fonte: PythonBrasil

O Python não possui acesso nativo a bancos de dados SQL em sua biblioteca padrão, somente ao Berkley database engine(BDB), mas define uma API padrão que os drivers de acesso a esses bancos de dados devem seguir, assim qualquer que seja o banco de dados os procedimentos são parecidos.

Os quatro principais bancos de dados livres que podem ser acessados do Python são o SQLite, o MySQL, o PostgreSQL e o Interbase/Firebird.

Para cada um inicie importando os drivers:

   1 import MySQLdb # para o MySQL
   2 import sqlite  # para o SQLite
   3 from pyPgSQL import PgSQL # para o PostgreSQL
   4 import kinterbasdb # para Interbase / Firebird
   5 import pymssql  #para o MS-SQL. (existem outros módulos - ADOdb for Python/PHP)
   6 import cx_Oracle #para o Oracle

Nota: Não faça from driver import * porque isso fará com que o Python importe muito lixo para o namespace padrão, além disso, isso impossibilita o uso de mais de um driver ao mesmo tempo.

Continue lendo(nova janela) >>

Just another link:  DEVSHED

If you are a python developer and will need some querys in MySQL, please read this post. To begin, we will need an API to work with MySQL databases, I will use MySQLdb.

We will need MySQLdb API installed on machine, I won´t talk about it now, please wait another post(very soon).

First: We need to import the MySQL API

import MySQLdb

Second: We will connect, using the method connect()

db=MySQLdc.connect(passwd=”my_password”,”db=gabriel”)

And now, go to the queries!

- We need a cursor,

gabriel=db.cursor()

- and after we will execute queries on it:

max_price=3000
gabriel.execute(“”"SELECT bike, computer, car FROM shop
WHERE price < %s”"”, (max_price,))

We use %s in the string to MySQLdb convert it to a literal value. The query will actually say, “…WHERE price < 5″

And now, the results:

>>> gabriel.fetchone()
(Spark23S, Macbook1XXs,PorscheCarrera )

Quite unlike the _mysql example, this returns a single tuple, which is the row, and the values are properly converted by default… except… What’s with the L’s?

As mentioned earlier, while MySQL’s INTEGER column translates perfectly into a Python integer, UNSIGNED INTEGER could overflow, so these values are converted to Python long integers instead.

If you wanted more rows, you could use c.fetchmany(n) or c.fetchall(). These do exactly what you think they do. On c.fetchmany(n), the n is optional and defaults to c.arraysize, which is normally 1. Both of these methods return a sequence of rows, or an empty sequence if there are no more rows. If you use a weird cursor class, the rows themselves might not be tuples.

Note that in contrast to the above, c.fetchone() returns None when there are no more rows to fetch.

Post changed and copied  from MySQLdb

Some links about Functions and MySQL. I know, I have a lot of stuff to translate on next weekend, but I will translate to portuguese too.

From MySQL website

From Database Journal

Well.. I find a very good stuff about Stored Procedures and MySQL.  Enjoy!

From MySQL website

From Database Journal, great reference for me

As soon as possible, I will translate these docs to PT_BR.

Since version 5.0.2 MySQL has support to triggers, which we can use with INSERT,UPDATE and DELETE SQL statements.

But what is a trigger? A trigger, basically is a procedural command associated to tables which needs a particular event to run. For example, if I need  insert my new employees in a health care program(a table), I will create a trigger on Employees table which every INSERT statement automatically runs a trigger tr_new_employee_health_care.

– Some important things to remember about triggers:

- A trigger can be set to activate either before or after the triggering statement. For example, you can have a trigger activate before each row that is inserted into a table or after each row that is updated.

- Triggers cannot be activated by APIs. Just using SQL commands.

– An example of MySQL triggers and Syntax:

http://dev.mysql.com/doc/refman/5.0/en/trigger-syntax.html

Well…

Hurry up! Time to begin a tutorial about databases using linux and mysql. I will try to put some python code together, maybe some tools to do basic operations like create database, give some permissions, create tables and the most important: create a sql script to migrate databases to another server. lets keep in touch, I will post that script very soon.

– First basic operation to do with mysql – access the ‘mysql’ command prompt, to work with mysql.

At linux shell:

mysql -u user -p

Where:

user- Its a user to connect on mysql server
-p – Its a parameter to prompt for password

E.g. mysql -u gabriel -p

Please pay attention: The user and password  maybe its not the same of /etc/passwd. Actually, in first startup of mysql service, mysql ask to you a new password of root, normally blank. NEVER LEAVE BLANK PASSWORD FOR THE ROOT USER.

– WORK WITH DATABASES

use name_of_database

– Creating databases

CREATE DATABASE name_of_database;

E.g. CREATE DATABASE gabriel;

– Granting basic permissions

GRANT <PRIVILEGES> ON OBJECT.object TO user@<HOST> IDENTIFIED BY ‘password’;

Where:

– TYPE OF PRIVILEGES

Privilege    Description
ALL/ALL PRIVILEGES    All the basic permissions
ALTER    Permission to run ALTER statements
CREATE    Permission to CREATE tables or databases
CREATE TEMPORARY TABLES    Permission to run CREATE TEMPORARY TABLE statements
DELETE    Permission to run DELETE statements
DROP    Permission to DROP tables or databases
EXECUTE    Permission to run stored procedures (in MySQL 5)
FILE    Permission to read and write files (e.g. LOAD DATA INFILE statements)
GRANT    Permission to GRANT available permissions to other users
INDEX    Permission to create, change or drop indexes
INSERT    Permission to run INSERT statements
LOCK TABLES    Permission to LOCK tables which the user has SELECT access to
PROCESS    Permission to view or kill MySQL processes
REFERENCES    Currently unused
RELOAD    Permission to reload the database (e.g. FLUSH statements)
REPLICATION CLIENT    Permission to ask about replication
REPLICATION SLAVE    Permission to replicate from the server
SHOW DATABASES    Permission to see all databases
SELECT    Permission to run SELECT statements
SHUTDOWN    Permission to SHUTDOWN the MySQL server
SUPER    Permission to connect, even if the number of connections is exceeded, and perform maintenance commands
UPDATE    Permission to run UPDATE statements
USAGE    Permission to connect and and perform basic commands only

– OBJECT

*.*    All tables in a database
*    All tables in the current database
dbname.*    All tables in the named database
dbname.tbname    The named table in the named database

E.g. GRANT ALL ON gabriel.* TO gabriel@localhost IDENTIFIED BY ‘mypassword’;

– HOST

Its the host with permissions to connect to database(can be an IP, and hostname, and normally to webservers with mysql is localhost)

– Import sql script file

mysql -u user -p < script.sql

Where:

user- Its a user to connect on mysql server
-p – Its a parameter to prompt for password
< - Put on(for example, execute the sql script inside mysql)
script.sql- A file with Transact SQL, like INSERT, CREATE …

Hint: a lot of  sql scripts file don´t have basic instructions in the begining, like  USE database. If you aren’t using a database, its not possible to create tables, for example.

E.g.*:

NORMAL FILE

CREATE TABLE xxxx(X,Y,Z)

MODIFY THE FILE TO:

USE X;

CREATE TABLE xxxx(X,Y,Z)

——– Its enough for now. Later I will publish more about MySQL. If you have a post about PostgreSQL, please let me know using the comments.  ——–