python读取sql_在python中读取外部sql脚本

我正在学习如何在python中执行SQL（我知道SQL，而不是python）。

我有一个外部sql文件。它创建数据并将数据插入三个表“Zookeeper”、“Handles”、“Animal”。

然后我有一系列的查询要从表中运行。下面的查询位于zookeeper.sql文件中，我在python脚本的顶部加载了该文件。前两个例子是：--1.1

SELECT ANAME,zookeepid

FROM ANIMAL, HANDLES

WHERE AID=ANIMALID;

--1.2条SELECT ZNAME, SUM(TIMETOFEED)

FROM ZOOKEEPER, ANIMAL, HANDLES

WHERE AID=ANIMALID AND ZOOKEEPID=ZID

GROUP BY zookeeper.zname;

这些在SQL中都执行得很好。现在我需要在Python中执行它们。我已经得到并完成了在文件中阅读的代码。然后执行循环中的所有查询。

1.1和1.2是我困惑的地方。我相信在循环中，这是我应该放一些东西来运行第一个和第二个查询的行。

result=c.execute（“从%s；%表中选择*）

但是什么？我想我漏掉了一些很明显的东西。我想让我失望的是%桌位。在查询1.1和1.2中，我不是创建表，而是寻找查询结果。

下面是我的整个python代码。import sqlite3

from sqlite3 import OperationalError

conn = sqlite3.connect('csc455_HW3.db')

c = conn.cursor()

# Open and read the file as a single buffer

fd = open('ZooDatabase.sql', 'r')

sqlFile = fd.read()

fd.close()

# all SQL commands (split on ';')

sqlCommands = sqlFile.split(';')

# Execute every command from the input file

for command in sqlCommands:

# This will skip and report errors

# For example, if the tables do not yet exist, this will skip over

# the DROP TABLE commands

try:

c.execute(command)

except OperationalError, msg:

print "Command skipped: ", msg

# For each of the 3 tables, query the database and print the contents

for table in ['ZooKeeper', 'Animal', 'Handles']:

**# Plug in the name of the table into SELECT * query

result = c.execute("SELECT * FROM %s;" % table);**

# Get all rows.

rows = result.fetchall();

# \n represents an end-of-line

print "\n--- TABLE ", table, "\n"

# This will print the name of the columns, padding each name up

# to 22 characters. Note that comma at the end prevents new lines

for desc in result.description:

print desc[0].rjust(22, ' '),

# End the line with column names

print ""

for row in rows:

for value in row:

# Print each value, padding it up with ' ' to 22 characters on the right

print str(value).rjust(22, ' '),

# End the values from the row

print ""

c.close()

conn.close()