在python中读取外部sql脚本 [英] reading external sql script in python
问题描述
我正在学习如何在 python 中执行 SQL(我知道 SQL,而不是 Python).
I am working on a learning how to execute SQL in python (I know SQL, not Python).
我有一个外部 sql 文件.它创建并插入数据到三个表'Zookeeper'、'Handles'、'Animal'.
I have an external sql file. It creates and inserts data into three tables 'Zookeeper', 'Handles', 'Animal'.
然后我有一系列的查询来运行表.以下查询位于我在 python 脚本顶部加载的 zookeeper.sql 文件中.前两个的例子是:
Then I have a series of queries to run off the tables. The below queries are in the zookeeper.sql file that I load in at the top of the python script. Example for the first two are:
--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 中执行它们.我已经获得并完成了读取文件的代码.然后执行循环中的所有查询.
These all execute fine in SQL. Now I need to execute them from within Python. I have been given and completed code to read in the file. Then execute all the queries in the loop.
1.1 和 1.2 是我感到困惑的地方.我相信在循环中,这是我应该放入一些东西来运行第一个和第二个查询的行.
The 1.1 and 1.2 is where I am getting confused. I believe in the loop this is the line where I should put in something to run the first and then second query.
result = c.execute("SELECT * FROM %s;" % table);
result = c.execute("SELECT * FROM %s;" % table);
但是什么?我想我错过了一些非常明显的东西.我认为让我失望的是 % 表.在查询 1.1 和 1.2 中,我不是在创建表,而是在查找查询结果.
but what? I think I am missing something very obvious. I think what is throwing me off is % table. In query 1.1 and 1.2, I am not creating a table, but rather looking for a query result.
我的整个 python 代码如下.
My entire python code is below.
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()
推荐答案
你的代码已经包含了一种从指定的 sql 文件中执行所有语句的漂亮方法
Your code already contains a beautiful way to execute all statements from a specified sql file
# 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
将其包装在一个函数中,您可以重用它.
Wrap this in a function and you can reuse it.
def executeScriptsFromFile(filename):
# Open and read the file as a single buffer
fd = open(filename, '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
使用它
executeScriptsFromFile('zookeeper.sql')
你说你被迷惑了
result = c.execute("SELECT * FROM %s;" % table);
在 Python 中,您可以使用称为字符串格式的东西向字符串添加内容.
In Python, you can add stuff to a string by using something called string formatting.
您有一个字符串 "Some string with %s"
和 %s,这是其他内容的占位符.要替换占位符,请在字符串后添加 %(要替换的内容")
You have a string "Some string with %s"
with %s, that's a placeholder for something else. To replace the placeholder, you add % ("what you want to replace it with") after your string
例如:
a = "Hi, my name is %s and I have a %s hat" % ("Azeirah", "cool")
print(a)
>>> Hi, my name is Azeirah and I have a Cool hat
有点幼稚的例子,但应该很清楚.
Bit of a childish example, but it should be clear.
现在,什么
result = c.execute("SELECT * FROM %s;" % table);
意思是,它是否将 %s 替换为表变量的值.
means, is it replaces %s with the value of the table variable.
(创建于)
for table in ['ZooKeeper', 'Animal', 'Handles']:
# for loop example
for fruit in ["apple", "pear", "orange"]:
print fruit
>>> apple
>>> pear
>>> orange
如果您还有其他问题,请戳我.
If you have any additional questions, poke me.
这篇关于在python中读取外部sql脚本的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!