Top-rated recipes tagged "sql"

Printing list of ODBC data sources (Python)

2013-12-10T11:07:37-08:00

Python recipe 578782 by Michal Niklas (database, odbc, sql).

This simple code shows ODBC data sources. It uses odbc module.

Inner Join (Python)

2011-11-01T20:10:34-07:00

Python recipe 577937 by Raymond Hettinger (database, join, sql). Revision 2.

Implemented an SQL style INNER JOIN for two lists of tuples to be joined on a common field.

Populate SQL tables from CSV data files (Python)

2011-02-02T21:21:49-08:00

Python recipe 577559 by James Mills (conversion, csv, data, python, sql).

Just a quick recipe I developed a few years ago that I thought might be useful to others. Basically it takes as input a data file with comma separated values (CSV) and translates this into a series of SQL "INSERT" statements allowing you to then feed this into MySQL, SQLite, or any other database.

Example Usage:

$ cat cars.csv Year,Make,Model,Length 1997,Ford,E350,2.34 2000,Mercury,Cougar,2.38

$ sqlite3 cars.db "CREATE TABLE cars (Year, Make, Model, Length)"

$ ./csv2sql.py cars.csv | sqlite3 cars.db

$ sqlite3 cars.db "SELECT * FROM cars" 1997|Ford|E350|2.34 2000|Mercury|Cougar|2.38

Enjoy! Feedback welcome!

cheers James Mills / prologic

Minimalistic PostgreSQL console shell (Python)

2011-11-30T13:43:28-08:00

Python recipe 577468 by ccpizza (postgres, postgresql, sql). Revision 17.

Primitive shell for running PostgreSQL quieries from console. Make sure you set the right connection settings in the script.

All queries are executed in autocommit mode.

Example command line usage:

pg.py select * from mytable where oidh=1

The script recognizes a number of magic keywords:

pg.py show databases
pg.py show schemas
pg.py describe <table_name>

Create PDF control break reports with itertools.groupby and xtopdf (Python)

2016-07-23T22:17:29-07:00

Python recipe 580692 by Vasudev Ram (control, database, grouping, iterators, pdf, pdfwriter, pdf_generation, python, reportgeneration, reporting, sql, xtopdf).

This recipe shows how to create the classic control break style of report (a staple of data processing) using Python along with the groupby function from the itertools module, and xtopdf, a Python toolkit for PDF creation.

Publish Firebird SQL data to PDF with xtopdf (Python)

2015-12-01T19:45:18-08:00

Python recipe 579131 by Vasudev Ram (database, firebird, pdfwriter, python, sql, xtopdf).

This recipe shows how to publish data from the Firebird RDBMS to PDF, using the xtopdf toolkit and the fbd Python driver for Firebird. Firebird is a cross-platform, open source RDBMS based on the former Interbase RDBMS from Borland, which they used to bundle with some of their developmemt tools, such as Borland C++ and Borland Delphi.

The recipe reads data from a Firebird table, using the fbd Python driver for Firebird, and writes it to PDF, using the xtopdf toolkit. See:

http://jugad2.blogspot.in/p/xtopdf-pdf-creation-library.html

for information on xtopdf.

It assumes that a Firebird database called test.fdb exists under /temp/firebird (C:\temp\firebird, really - the test was done on Windows), and that it has a contacts table with the structure shown in the code of the recipe.

More details and sample output are here:

http://jugad2.blogspot.in/2014/01/by-vasudev-ram-pdf-firebird-is-cross.html

Publish SQLite data to PDF using named tuples (Python)

2015-02-24T22:08:11-08:00

Python recipe 579027 by Vasudev Ram (conversion, pdf, pdfwriter, sql, sqlite, sqlite3, xtopdf).

This recipe shows how to publish SQLite data to PDF, using named tuples from the collections module of Python, the sqlite3 library, and the xtopdf library for PDF generation.

Python SQLSMO - Threading Example (Python)

2015-02-10T02:01:42-08:00

Python recipe 579023 by Jorge Besada (database, python, smo, sql, sqlsmo, threading).

This is an example of the use of the SQLSMO module. Using a csv file DBLIST_ACTIONS.csv with list of databases where you can launch multiple different database operations in parallel

Some lines of the configuration file DBLIST_ACTIONS.csv used shown below: SERVERNAME,DBNAME1,SOURCESERVER,DATAFOLDER,LOGFOLDER,DBNAME2,ACTIONS,ENABLED (local)\sql2014,AdventureWorks2012,C:\SQL2014\BACKUPS,C:\SQL2014\DATA,C:\SQL2014\LOG,AdventureWorks_COPY1,RESTOREDBS1.CFG,Y (local)\sql2014,AdventureWorks2012,C:\SQL2014\BACKUPS,C:\SQL2014\DATA,C:\SQL2014\LOG,AdventureWorks_COPY2,RESTOREDBS1.CFG,Y (local)\sql2014,AdventureWorks2012,C:\SQL2014\BACKUPS,C:\SQL2014\DATA,C:\SQL2014\LOG,AdventureWorks_COPY3,RESTOREDBS1.CFG,Y (local)\sql2014,AdventureWorks2012,C:\SQL2014\BACKUPS,C:\SQL2014\DATA,C:\SQL2014\LOG,AdventureWorks_COPY4,RESTOREDBS1.CFG,

Where: SERVERNAME: server where the database to act upon resides

DBNAME1: source database

DBNAME2: destination database (may be different from source when we restore a copy with a different name)

SOURCESERVER: this is the network (or local) folder where backups are placed

DATAFOLDER: folder for data files

LOGFOLDER: folder for log files

ACTIONS: this is the name of the configuration file (.CFG) with the list of actions

ENABLED: a Y value here will mean we want to process the line

For each line (database) you specify a configuration file (in this case RESTOREDBS1.CFG), see sample below: (one line for each, no blank lines)

RESTORE DATABASE

SET DBOWNER: sa

SYNC LOGINS

SET SIMPLE MODE

SHRINK LOG

The program will process each line in the source CSV file and for each one it will perform the set of operations described in the configuration file. This system is being used in my workplace with different configuration files for different databases (there are configuration files for restores, specific restores with more actions, backups, etc, not included here for brevity).

Every time you do a database task you just add a line in the DBLIST_ACTIONS.CSV and create (if needed) a configuration file). If you are going to actually use it include a "Y" in the ENABLED column Note: every line action in the configuration file must have been implemented in the function ActionParsing as one the entries in the big if statement.

Special features: You can specify at the start of the program if you want to really execute or not. You may want to do first a trial run setting NOEXECUTE_OPTION = 1 (instead of the default of 0). In this case the program will run and create the SQL script of the operations, not executing them. Note: it has been implemented in the restores so far, will add it to the other options later.

Threading: by default it will run as many threads as lines in the DBLIST_ACTIONS.CSV file. But you can change this option by setting a value to THREAD_POOL different than 0.

Publish databases to PDF with PyDAL (a Python DB library) and xtopdf (Python)

2015-01-15T16:09:27-08:00

Python recipe 579004 by Vasudev Ram (database, pdf, pydal, python, python_developer_tools, sql, web2py, xtopdf).

This recipe shows how to use the PyDAL database library for Python, together with xtopdf, a PDF creation library for Python, to publish database data to PDF. PyDAL was earlier a part of the web2py Python web framework, and then was split out into a separate library that does not need to be used with web2py. It supports access to many popular relational databases, both open source and proprietary.

Python SQLSMO (Python)

2015-03-28T16:58:11-07:00

Python recipe 578977 by Jorge Besada (database, python, smo, sql, sqlsmo). Revision 15.

I needed a Python library equivalent to the SQL Server Management Objects (SMO) and did not find it, so I created my own version. It does not follow the standard SMO objects names. So far it has a set of basic functionality: to make backups, restores with move, sync logins for restored databases, check disk space. I included a good sized testing harness to get you going. This version uses sqlcmd for connectivity. I use this SQLSMO library as an imported module in several of my Python applications. It has been tested with SQL 2012 and SQL 2014, it should function with versions down to 2005.

Printing list of ODBC data sources with pyodbc module (Python)

2014-01-28T10:40:56-08:00

Python recipe 578815 by Michal Niklas (database, odbc, sql).

I have similar recipe but with odbc module: http://code.activestate.com/recipes/578782-printing-list-of-odbc-data-sources/

sql+ the SQL*Plus killer (Python)

2013-03-14T09:13:01-07:00

Python recipe 578490 by jo (database, db, db_client, sql, sql_client). Revision 3.

This recipe is an emulator of the Oracle SQL*Plus, but it does things in a more friendly way ;).

If you need a client to access your Oracle but you don't like SQL*Plus, try this one.

This recipe was inspired by James Thiele's Console built with Cmd object recipe.

It provides a 'help' facility and supplies command completion when you hit the 'tab' key.

In addition you can use command line editing and history keys.

Here are the commands that you can use:

========================================
Documented commands (type help <topic>):
========================================
constraints  edit      help     input   sequences  shell
db           exit      history  output  set        tables
desc         foreigns  index    quit    settings   triggers

You can edit the queries using 'vim' or any other editor.

The command 'output' can redirect output to a file and command 'input' can input commands from a file.

There's paginated output.

The command 'shell' or '!' allow you to perform operating system commands.

You can add commands by defining methods with names of the form 'do_xxx()' where 'xxx' is the name of the command you wish to add.

There is a configuration file (.sql+) where you need to enter the dburi, editor name and other settings.

Conjunction select using foreign keys (Text)

2011-06-11T02:43:22-07:00

Text recipe 577747 by Kaushik Ghose (conjunction, foreign, key, select, sql).

Say we have a table (notes) containing rows we want to select. Each note has one or more keywords (stored in a table of the same name). We want to select notes that have a conjunction of keywords (AND). notes and keywords are linked through a foreign key table notes_keywords. The following SQL statement allows us to do this

A MSSQL XML importer for MySQL (Python)

2011-03-09T03:46:49-08:00

Python recipe 577601 by Albert Perrien II (mysql, server, sql, xml).

This is a simple function that takes records exported to XML from SQL Server or Access and imports them into MySQL.

ExecSql.cgi returning JSON for SQL (Python)

2009-11-28T02:00:30-08:00

Python recipe 576971 by Martchenko (cgi, json, sql).

CGI script getting JSON for SQL request

Transparently execute SQL queries as prepared statements with Postgresql (Python)

2009-03-23T07:30:07-07:00

Python recipe 576698 by Michael Palmer (postgresql, prepared_statements, sql). Revision 4.

This recipe defines a mixin class for DBAPI cursors that gives them an 'executeps' method. This method transparently converts any SQL query into a prepared statement, which gets cached and executed instead of the original query.