Postgre Sql and Python Welcome to Postgre Sql

Postgre. Sql and Python Welcome to

Postgre. Sql and Python Brent Friedman Senior Solutions Consultant U. S. Professional Services Group Cincom Systems, Inc. Cincinnati, OH EMAIL: bfriedman. development@gmail. c om

Postgre. Sql and Python 1. 2. 3. 4. 5. Objectives Discuss databases What is Postgre. Sql? What is a db driver? Advanced topics Questions

Postgre. Sql and Python Database History

Postgre. Sql and Python 1950 s – 1960 s “Flat files” 1960 s hierarchical databases 1970 s – 1990 s (and 2000 s, too!) relational databases (E. F. Codd) 1990 s – on object databases

Postgre. Sql and Python • Sidebar “What is NORMAL FORM? ”

Postgre. Sql and Python First Normal Form “The truth …”

Postgre. Sql and Python Second Normal Form “… the whole truth, …”

Postgre. Sql and Python Third Normal Form “… and nothing but the truth. ”

Postgre. Sql and Python Entity Relationship Diagram (ERD) Entity – a table Relationship – Linkage between two tables

Postgre. Sql and Python So, all that is great. Why do I want to use a database? A database allows you to store and retrieve large amounts of data quickly, in an organized fashion.

Postgre. Sql and Python Postgre. SQL “The world’s most advanced open source database. ”

Postgre. Sql and Python So, that’s a nice slogan. What does it mean to me? 1. Postgre. Sql is an ENTERPRISE CLASS database. It’s got the features, if you need them. 2. Postgre. Sql is released under the BSD public license.

Postgre. Sql and Python 3. Postgre. Sql is a scalable, standards-compliant database. 4. Postgre. Sql isn’t controlled by a single company. (My. SQL was recently acquired by Sun Microsystems. ) 5. Postgre. Sql is on par, feature wise, with an industry giant (rhymes with boracle).

Postgre. Sql and Python Ok, so Postgre. Sql is open source, and has enterprise features. Does it integrate with the normal slew of languages? pl/pgsql python perl java ada bash c c++ php ruby smalltalk tcl. net etc.

Postgre. Sql and Python What is a database driver? A driver is a module that allows your application to use a database specific connection protocol and the SQL language against a database.

Postgre. Sql and Python Postgre. Sql Connectivity Options 1. Py. Pg. Sql – has been around a while, tutorials are hard to find 2. Py. Gre. Sql – much easier to find examples/current mailing list activity 3. Psycopg 2 – db driver with its own connection pool, and a few other things

Postgre. Sql and Python Where to begin… Download Python www. python. org Download Postgre. Sql www. postgresql. org Download Py. Gre. Sql www. pygresql. org Download Psycopg 2 www. initd. org/pub/software/psycopg /

Postgre. Sql and Python Create a simple table to work with: create sequence pythondemo_seq; create table pythondemo ( id int 4 DEFAULT nextval(‘pythondemo_seq’) NOT NULL, name varchar(20), password varchar(20), active boolean);

Postgre. Sql and Python Creating a sample table, continued… Insert into pythondemo(name, password) values(‘bob’, ’secretpassword’); Insert into pythondemo(name, password) values(‘betty’, ’nimda’); Insert into pythondemo(name, password) values(‘scott’, ’tiger’);

Postgre. Sql and Python A small psycopg 2 example #!/usr/bin/python 2. 4 import psycopg 2 try: conn = psycopg 2. connect("dbname=‘mydb' user='dbuser' host='localhost' password='dbpass'"); except: print "I am unable to connect to the database" cur = conn. cursor() cur. execute("""SELECT * from pythondemo""") rows = cur. fetchall() print "n. Show me the data!n" for row in rows: print " ", row[0]

Postgre. Sql and Python Sidebar – Connection Pools Or, one of those “Enterprise features…”

Postgre. Sql and Python Connection Pools Contain No Water (in most cases) 1. If SQL is a human-readable scheme; and, 2. Socket connections are expensive transactions for a processor; THEN: 3. Database connections can REALLY hurt your application’s scalability [A syllogism, for all you liberal studies majors…]

Postgre. Sql and Python CONN CONN

Postgre. Sql and Python Some “rules of thumb” for connection pools 1. 2. For a standard web application with 500 concurrent users, use a connection pool size of 50 Using a connection pool will decrease database processing time by 50% or more

Postgre. Sql and Python More on Enterprise Stuff… 1. ACID compliant 2. Foreign keys really work! 3. All joins (even right/left outer) 4. Views act the way views should act 5. Stored procedures are EASY!

Postgre. Sql and Python And more enterprise stuff… 6. MVCC (concurrency) 7. Tablespaces 8. Asynchronous replication 9. Multi-master replication 10. Hot backups/real time 11. Built-in unicode support 12. Etc etc

Postgre. Sql and Python QUESTIONS?

Postgre. Sql and Python The End bfriedman. development@gmail. com